Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

CSV import is splitting JSON data into their own columns

Hey everyone, I'm running into an issue...I have a .csv file that is a table dump from a provider. Hence, I have no real control over the formatting of the data...I have to play the hand I'm dealt. S...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Let me see if I can simplify the example just a bit. 

 

  • Source file is a .csv
  • The "details" header or column contains a JSON string. Here is an example of text from the raw .csv
    •  {"shipstation-synced":true,"shipstation-orderId":5287458}
  • This is what PowerBI is doing to the JSON String. It's being "pushed" into the shopify_order_id column. 
 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

  • alena2k's avatar
    alena2k
    Resolver IV
    6 years ago

    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"

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

      • alena2k's avatar
        alena2k
        Resolver IV
        6 years ago

        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"