cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.