Forum Discussion

Mooihoek's avatar
Mooihoek
Helper II
3 years ago

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

 

 

 

Need Help

8 Replies

    • Mooihoek's avatar
      Mooihoek
      Helper II
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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: