JSON Folder Import - OLE DB or ODBC error

[Mooihoek]

Using the Desktop Client,  Folder option to import multiple JSON files (Combine & Transform) I get the following error:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] We reached the end of the buffer.. '.

[lbendlin]

Can you provide a couple of sample JSON files?

[Mooihoek]

Sure each file has the following:

[
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T22:09:24.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.716",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.716,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.716",
"epoch": 1670191764984,
"hash": 1156952735
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:59:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.715",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.715,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.715",
"epoch": 1670191165071,
"hash": 4136067740
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:49:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.714",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.714,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.714",
"epoch": 1670190565019,
"hash": 2440566945
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:39:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.713",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.713,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.713",
"epoch": 1670189965007,
"hash": 1247800865
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:29:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.712",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.712,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.712",
"epoch": 1670189365378,
"hash": 1787557371
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:19:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.711",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.711,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.711",
"epoch": 1670188765041,
"hash": 249125501
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T21:09:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.71",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.71,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.71",
"epoch": 1670188165006,
"hash": 4230955781
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T20:59:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.709",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.709,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.709",
"epoch": 1670187565002,
"hash": 2984310911
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T20:49:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.708",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.708,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.708",
"epoch": 1670186965037,
"hash": 2664408132
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-04T20:39:25.000+00:00",
"text": "Energy consumption of Aeotec Switch is 30.707",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 30.707,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "30.707",
"epoch": 1670186365000,
"hash": 254841458
}
]

[lbendlin]

Not seeing an issue here

Query:

let
    Source = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".json")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "sample")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Content", "Name"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "parsejson", each parsejson([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoked Custom Function",{"Name", "parsejson"}),
    #"Expanded parsejson" = Table.ExpandTableColumn(#"Removed Other Columns1", "parsejson", {"deviceId", "deviceName", "locationId", "locationName", "time", "text", "component", "componentLabel", "capability", "attribute", "value", "unit", "translatedAttributeName", "translatedAttributeValue", "epoch", "hash"}, {"deviceId", "deviceName", "locationId", "locationName", "time", "text", "component", "componentLabel", "capability", "attribute", "value", "unit", "translatedAttributeName", "translatedAttributeValue", "epoch", "hash"})
in
    #"Expanded parsejson"

parsejson:

(file)=> let
    Source = Json.Document(file),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"deviceId", "deviceName", "locationId", "locationName", "time", "text", "component", "componentLabel", "capability", "attribute", "value", "unit", "data", "translatedAttributeName", "translatedAttributeValue", "epoch", "hash"}, {"deviceId", "deviceName", "locationId", "locationName", "time", "text", "component", "componentLabel", "capability", "attribute", "value", "unit", "data", "translatedAttributeName", "translatedAttributeValue", "epoch", "hash"}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"deviceId", type text}, {"deviceName", type text}, {"locationId", type text}, {"locationName", type text}, {"time", type datetime}, {"text", type text}, {"component", type text}, {"componentLabel", type text}, {"capability", type text}, {"attribute", type text}, {"value", type number}, {"unit", type text}, {"translatedAttributeName", type text}, {"translatedAttributeValue", type number}, {"epoch", Int64.Type}, {"hash", Int64.Type}})
in
    #"Changed Type"

imports fine:

[Mooihoek]

Yes the import is ok, but when you do close and apply the error message will be shown

[lbendlin]

no error message on my side. see attached.

[Mooihoek]

I see some additional steps in Queries in my pbx when I go through the Folder import compared to yours which still gives me the error.  When I update the source in the example you provided none of the data loads from the table

[lbendlin]

maybe something wonky with your JSON files.  Can you post the sample files onto a file share?

[Mooihoek]

You're right looks like the source files have an extra row at the end! This is causing issues