Forum Discussion
CSV import is splitting JSON data into their own columns
Hi Anonymous ,
Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, would you please kindly mark your sharing solution as an answer so that it can benefit more users?
Best regards,
Is it always splitting csv into the same set of undesired columns? If so, it should be possible to merge them back with ," as separator and parse the results normally
e.g.
#"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"details", "shopify_order_id"},Combiner.CombineTextByDelimiter(",""", QuoteStyle.None),"Merged"),
#"Parsed JSON" = Table.TransformColumns(#"Merged Columns",{},Json.Document)
in
#"Parsed JSON"
Unfortunately no it's not being consistent. That's because the JSON isn't consistent and in some instances the following columns have data. I wish I could just do a merge and roll...but that's not the case here.
I see. Can you open it as a text file instead of .csv?
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\sample.csv"), null, null, 1252)}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
#"Changed Type"I can give that a whirl but I'm confused, is this truely an issue that is seldom seen? I would suspect that anyone who dumps a DB table into a .csv could run into this. Hmmm....
import of .csv accepts different delimeters, but comma is a default. You can change delimeter to be somethign what's not in the file or nothing (as text). I would not expect .csv export to work in any other way.