I am trying to convert a list of objects from a JSON file to excel using this library https://www.codeproject.com/Articles/1193650/Cinchoo-ETL-Quick-Start-Converting-JSON-to-CSV-Fil in C#. The object is a JSON array so that means it contains also other objects but some of the objects contain just an empty array (see sensors).
Sensors example object :
{
"id": 4394,
"uuid": "50124928-f58b-4aa9-ab5b-14ce05058dd1",
"name": "smarty",
"description": null,
"state": "not_configured",
"system_tags": [
"indoor",
"offline"
],
"user_tags": [],
"last_reading_at": null,
"added_at": "2017-09-24T13:09:02Z",
"updated_at": "2017-09-24T13:09:02Z",
"mac_address": "[FILTERED]",
"owner": {
"id": 5424,
"uuid": "fea238d0-4c8f-4f0c-8ff5-412eca90bf45",
"username": "smarty",
"avatar": "https://smartcitizen.s3.amazonaws.com/avatars/default.svg",
"url": null,
"joined_at": "2016-07-04T22:23:04Z",
"location": {
"city": null,
"country": null,
"country_code": null
},
"device_ids": []
},
"data": {
"recorded_at": null,
"added_at": null,
"location": {
"ip": null,
"exposure": "indoor",
"elevation": null,
"latitude": 50.1234885,
"longitude": 8.6892953,
"geohash": "u0yjjsxq7r",
"city": "Frankfurt am Main",
"country_code": "DE",
"country": "Germany"
},
"sensors": []
},
"kit": null
Example of an object that contains array objects :
{
"id": 3669,
"uuid": "82d74e41-d37a-4312-ac0a-6fbb5292091e",
"name": "Hopper 1",
"description": "Measuring a conference room",
"state": "has_published",
"system_tags": [
"indoor",
"offline"
],
"user_tags": [
"Research"
],
"last_reading_at": "2016-11-16T08:52:45Z",
"added_at": "2016-08-22T10:35:03Z",
"updated_at": "2018-03-05T19:04:42Z",
"mac_address": "[FILTERED]",
"owner": {
"id": 1756,
"uuid": "bcf9527f-e159-44c6-a2dd-f646d06520fd",
"username": "Lassevestergaard",
"avatar": "https://smartcitizen.s3.amazonaws.com/avatars/default.svg",
"url": "alexandra.dk",
"joined_at": "2014-06-30T06:56:33Z",
"location": {
"city": "Aarhus N",
"country": "Denmark",
"country_code": "DK"
},
"device_ids": [
3669,
3670
]
},
"data": {
"recorded_at": "2016-11-16T08:52:45Z",
"added_at": "2016-11-16T08:52:45Z",
"location": {
"ip": null,
"exposure": "indoor",
"elevation": null,
"latitude": 56.1720539,
"longitude": 10.1892761,
"geohash": "u1zpxcg9gm",
"city": "Aarhus",
"country_code": "DK",
"country": "Denmark"
},
"sensors": [
{
"id": 14,
"ancestry": null,
"name": "BH1730FVC",
"description": "Digital Ambient Light Sensor",
"unit": "Lux",
"created_at": "2015-02-02T18:24:56Z",
"updated_at": "2015-07-05T19:57:36Z",
"measurement_id": 3,
"uuid": "ac4234cf-d2b7-4cfa-8765-9f4477e2de5f",
"value": 1277.6,
"raw_value": 1277.6,
"prev_value": 1277.6,
"prev_raw_value": 1277.6
},
{
"id": 17,
"ancestry": null,
"name": "Battery",
"description": "Custom Circuit",
"unit": "%",
"created_at": "2015-02-02T18:26:28Z",
"updated_at": "2015-07-05T19:55:34Z",
"measurement_id": 7,
"uuid": "5b0e390e-781d-4243-8e97-579eead09792",
"value": 100,
"raw_value": 100,
"prev_value": 100,
"prev_raw_value": 100
},
{
"id": 13,
"ancestry": "19",
"name": "HPP828E031",
"description": "Humidity",
"unit": "%",
"created_at": "2015-02-02T18:24:30Z",
"updated_at": "2015-07-05T19:54:54Z",
"measurement_id": 2,
"uuid": "1c19ae8f-b995-460f-87a3-a9d0c140abfb",
"value": 45.4674072265625,
"raw_value": 20168,
"prev_value": 45.4674072265625,
"prev_raw_value": 20168
},
{
"id": 12,
"ancestry": "19",
"name": "HPP828E031",
"description": "Temperature",
"unit": "ºC",
"created_at": "2015-02-02T18:24:02Z",
"updated_at": "2015-07-05T19:55:07Z",
"measurement_id": 1,
"uuid": "2922d20e-3b83-4d98-8791-cfcdfc12fa99",
"value": 15.8444018554687,
"raw_value": 25676,
"prev_value": 15.8444018554687,
"prev_raw_value": 25676
},
{
"id": 15,
"ancestry": "20",
"name": "MiCS-4514",
"description": "NO2",
"unit": "kOhm/ppm",
"created_at": "2015-02-02T18:25:51Z",
"updated_at": "2015-07-05T19:57:59Z",
"measurement_id": 6,
"uuid": "0c5b7e74-ef87-431d-89af-dd51de84b10e",
"value": 244.822,
"raw_value": 244.822,
"prev_value": 244.822,
"prev_raw_value": 244.822
},
{
"id": 16,
"ancestry": "20",
"name": "MiCS-4514",
"description": "CO",
"unit": "kOhm/ppm",
"created_at": "2015-02-02T18:26:11Z",
"updated_at": "2015-07-05T19:58:18Z",
"measurement_id": 5,
"uuid": "49a26be4-3ce1-4f2e-a09b-4296fefcfe17",
"value": 143.169,
"raw_value": 143.169,
"prev_value": 143.169,
"prev_raw_value": 143.169
},
{
"id": 21,
"ancestry": null,
"name": "Microchip RN-131",
"description": "802.11 b/g WiFi",
"unit": "# networks",
"created_at": "2015-05-04T11:17:18Z",
"updated_at": "2015-07-05T19:57:22Z",
"measurement_id": 9,
"uuid": "5b1f0e38-336a-4abf-9989-69b48f0026ef",
"value": 11,
"raw_value": 11,
"prev_value": 11,
"prev_raw_value": 11
}
As you can see one object contain an empty array of sensors and the other and empty one.
This is my code for the application:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ChoETL;
namespace ReadFromJson
{
class Program
{
static void Main(string[] args)
{
using (var csv = new ChoCSVWriter("dev.csv").WithFirstLineHeader())
{
using (var json = new ChoJSONReader("device.json"))
{
//var result = json.Select(a => a.data.sensors).ToArray();
csv.Write(json.Select(i => new
{
// Info about device
Id = i.id,
DeviceUuid = i.uuid,
DeviceName = i.name,
DeviceDescription = i.description,
DeviceState = i.state,
UserTags = i.user_tags,
LastReading = i.last_reading_at,
AddedAt = i.added_at,
Updated = i.updated_at,
MacAddress = i.mac_address,
//Info about owner
OwnerID = i.owner.id,
OwnerUuid = i.owner.uuid,
OwnerUserName = i.owner.username,
OwnerAvatar = i.owner.avatar,
OwnerUrl = i.owner.url,
OwnerJoinDate = i.owner.joined_at,
OwnerCity = i.owner.location.city,
OwnerCountry = i.owner.location.country,
OwnerCountryCode = i.owner.location.country_code,
DeviceIds = i.owner.device_ids,
//Info about data
DataRecorded_At = i.data.recorded_at,
DataAdded_At = i.data.added_at,
DataLocation = i.data.location.ip,
DataExposure = i.data.location.exposure,
DataElevation = i.data.location.elevation,
DataLatitude = i.data.location.latitude,
DataLongitude = i.data.location.longitude,
DataGeoLocation = i.data.location.geohash,
DataCity = i.data.location.city,
DataCountryCode = i.data.location.country_code,
DataCountry = i.data.location.country,
//SensorBattery
SensorsId = i.data.sensors[0].id,
SensortAncestry = i.data.sensors[0].ancestry,
SensorName = i.data.sensors[0].name,
SensorDescription = i.data.sensors[0].description,
SensorUnit = i.data.sensors[0].unit,
SensorCreatedAt = i.data.sensors[0].created_at,
SensorUpdated_at = i.data.sensors[0].updated_at,
SensorMeasurement_id = i.data.sensors[0].measurement_id,
SensorUuid = i.data.sensors[0].uuid,
SensorValue = i.data.sensors[0].value,
SensorRawValue = i.data.sensors[0].raw_value,
SensorPrevValue = i.data.sensors[0].prev_value,
SensorPrevRawValue = i.data.sensors[0].prev_raw_value,
//SensorHumidity
SensorsHumidityId = i.data.sensors[1].id,
SensortHumidityAncestry = i.data.sensors[1].ancestry,
SensorHumidityName = i.data.sensors[1].name,
SensorHumidityDescription = i.data.sensors[1].description,
SensorHumidityUnit = i.data.sensors[1].unit,
SensorHumidityCreatedAt = i.data.sensors[1].created_at,
SensorumidityUpdated_at = i.data.sensors[1].updated_at,
SensorHumidityMeasurement_id = i.data.sensors[1].measurement_id,
SensorHumidityUuid = i.data.sensors[1].uuid,
SensorHumidityValue = i.data.sensors[1].value,
SensorHumidityRawValue = i.data.sensors[1].raw_value,
SensorHumidityPrevValue = i.data.sensors[1].prev_value,
SensorHumidityPrevRawValue = i.data.sensors[1].prev_raw_value,
//Temperature
SensorsTemperatureId = i.data.sensors[2].id,
SensortTemperatureAncestry = i.data.sensors[2].ancestry,
SensorTemperatureName = i.data.sensors[2].name,
SensorTemperatureDescription = i.data.sensors[2].description,
SensorTemperatureUnit = i.data.sensors[2].unit,
SensorTemperatureCreatedAt = i.data.sensors[2].created_at,
SensorTemperatureUpdated_at = i.data.sensors[2].updated_at,
SensorTemperatureMeasurement_id = i.data.sensors[2].measurement_id,
SensorTemperatureyUuid = i.data.sensors[2].uuid,
SensorTemperatureValue = i.data.sensors[2].value,
SensorTemperatureRawValue = i.data.sensors[2].raw_value,
SensorTemperaturePrevValue = i.data.sensors[2].prev_value,
SensorTemperaturePrevRawValue = i.data.sensors[2].prev_raw_value,
//No2 gas sensor
SensorsNo2Id = i.data.sensors[3].id,
SensortNo2Ancestry = i.data.sensors[3].ancestry,
SensorNo2Name = i.data.sensors[3].name,
SensorNo2Description = i.data.sensors[3].description,
SensorNo2Unit = i.data.sensors[3].unit,
SensorNo2CreatedAt = i.data.sensors[3].created_at,
SensorMo2Updated_at = i.data.sensors[3].updated_at,
SensorNo2Measurement_id = i.data.sensors[3].measurement_id,
SensorNo2Uuid = i.data.sensors[3].uuid,
SensorNo2Value = i.data.sensors[3].value,
SensorNo2RawValue = i.data.sensors[3].raw_value,
SensorNo2PrevValue = i.data.sensors[3].prev_value,
SensorNo2PrevRawValue = i.data.sensors[3].prev_raw_value,
//CO2 gas sensor
SensorsCo2Id = i.data.sensors[4].id,
SensortCo2Ancestry = i.data.sensors[4].ancestry,
SensorCo2Name = i.data.sensors[4].name,
SensorCo2Description = i.data.sensors[4].description,
SensorCo2Unit = i.data.sensors[4].unit,
SensorCo2CreatedAt = i.data.sensors[4].created_at,
SensorCo2Updated_at = i.data.sensors[4].updated_at,
SensorCo2Measurement_id = i.data.sensors[4].measurement_id,
SensorCo2Uuid = i.data.sensors[4].uuid,
SensorCo2Value = i.data.sensors[4].value,
SensorCo2RawValue = i.data.sensors[4].raw_value,
SensorCo2PrevValue = i.data.sensors[4].prev_value,
SensorCo2PrevRawValue = i.data.sensors[4].prev_raw_value,
//Network sensor
SensorsNetworkId = i.data.sensors[5].id,
SensortNetworkAncestry = i.data.sensors[5].ancestry,
SensorNetworkName = i.data.sensors[5].name,
SensorNetworkDescription = i.data.sensors[5].description,
SensorNetworkUnit = i.data.sensors[5].unit,
SensorNetworkCreatedAt = i.data.sensors[5].created_at,
SensorNetworkUpdated_at = i.data.sensors[5].updated_at,
SensorNetworkMeasurement_id = i.data.sensors[5].measurement_id,
SensorNetworkUuid = i.data.sensors[5].uuid,
SensorNetworkValue = i.data.sensors[5].value,
SensorNetworkRawValue = i.data.sensors[5].raw_value,
SensorNetworkPrevValue = i.data.sensors[5].prev_value,
SensorNetworkPrevRawValue = i.data.sensors[5]?.prev_raw_value,
//decibel sensor db
SensorsDBId = i.data.sensors[6].id,
SensorDBAncestry = i.data.sensors[6].ancestry,
SensorDBName = i.data.sensors[6].name,
SensorDBDescription = i.data.sensors[6].description,
SensorDBUnit = i.data.sensors[6].unit,
SensorDBCreatedAt = i.data.sensors[6].created_at,
SensorDBUpdated_at = i.data.sensors[6].updated_at,
SensorDBMeasurement_id = i.data.sensors[6].measurement_id,
SensorDBUuid = i.data.sensors[6].uuid,
SensorDBValue = i.data.sensors[6].value,
SensorDBRawValue = i.data.sensors[6].raw_value,
SensorDBPrevValue = i.data.sensors[6].prev_value,
SensorDBPrevRawValue = i.data.sensors[6].prev_raw_value,
// LDR Analog Light Sensor
LightSensorsId = i.data.sensors[7].id,
LightSensortAncestry = i.data.sensors[7].ancestry,
LightSensorName = i.data.sensors[7].name,
LightSensorDescription = i.data.sensors[7].description,
LightSensorUnit = i.data.sensors[7].unit,
LightSensorCreatedAt = i.data.sensors[7].created_at,
LightSensorUpdated_at = i.data.sensors[7].updated_at,
LightSensorMeasurement_id = i.data.sensors[7].measurement_id,
LightSensorUuid = i.data.sensors[7].uuid,
LightSensorValue = i.data.sensors[7].value,
LightSensorRawValue = i.data.sensors[7].raw_value,
LightSensorPrevValue = i.data.sensors[7].prev_value,
LightSensorPrevRawValue = i.data.sensors[7].prev_raw_value,
//solar panel
SolarPaneltSensorsId = i.data.sensors[8].id,
SolarPanelSensortAncestry = i.data.sensors[8].ancestry,
SolarPanelName = i.data.sensors[8].name,
SolarPanelSensorDescription = i.data.sensors[8].description,
SolarPanelSensorUnit = i.data.sensors[8].unit,
SolarPanelSensorCreatedAt = i.data.sensors[8].created_at,
SolarPanelSensorUpdated_at = i.data.sensors[8].updated_at,
SolarPanelSensorMeasurement_id = i.data.sensors[8].measurement_id,
SolarPanelSensorUuid = i.data.sensors[8].uuid,
SolarPanelSensorValue = i.data.sensors[8].value,
SolarPanelSensorRawValue = i.data.sensors[8].raw_value,
SolarPanelSensorPrevValue = i.data.sensors[8].prev_value,
SolarPanelSensorPrevRawValue = i.data.sensors[8].prev_raw_value,
KitId = i.kit.id,
KitUuid = i.kit.uuid,
KitSlug = i.kit.slug,
KitName = i.kit.name,
KitDescription = i.kit.description,
KitCreatedAt = i.kit.created_at,
KitUpdatedAt = i.kit.updated_at
}));
}
}
}
}
After I tun the program I get this error : "System.IndexOutOfRangeException: 'Index was outside the bounds of the array.' "
Does someone know how to read the empty arrays from my list of objects ? or maybe other solution to read and convert to JSON to excel file using C#.
