Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mooihoek
Helper II
Helper II

JSON Folder Import - OLE DB or ODBC error

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.. '.

 

Mooihoek_0-1670104764276.png

 

 

8 REPLIES 8
lbendlin
Super User
Super User

Can you provide a couple of sample JSON files?

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
}
]

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:

 

lbendlin_0-1670194513077.png

 

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

no error message on my side. see attached.

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.