Forum Discussion
Mooihoek
3 years agoHelper 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...
lbendlin
3 years agoSuper User
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
3 years agoHelper II
Yes the import is ok, but when you do close and apply the error message will be shown
- lbendlin3 years agoSuper User
no error message on my side. see attached.
- Mooihoek3 years agoHelper II
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
- lbendlin3 years agoSuper User
maybe something wonky with your JSON files. Can you post the sample files onto a file share?