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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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