Entire JSON Object to Single Cell in CSV

[Anonymous]

Does anyone know how to insert an entire JSON object into a single cell of a CSV? 

The JSON data is stored in a multiline text field inside of a sharepoint list.

I am using the command Data Operation - Create CSV Table.

Then the command SharePoint - Create File, outputting as .csv

When I open the csv file, the JSON data is spanning multiple lines and I can't figure out why. I have tried placing double-quotes around the data but that does seem to work.

[Anonymous · Accepted solution]

Here is what I did to solve the problem. Not sure if there is a better way, but this worked. If someone know how to deal with JSON data with commas please let me know. 

I added pipe character "|" at the end of all the data columns accept for the last one. 



Then I used two replace commands to force everything to be pipe delimited into a text file. One replace adjusts the header, the other adjusts the body data.

replace(replace(body('CreateCSVTable-CustomConfig'),'|,','|'),'ID,Title,CDN,Modified,ModifiedBy,Created,CreatedBy,JSONData','ID|Title|CDN|Modified|ModifiedBy|Created|CreatedBy|JSONData')

This can then be loaded into Excel cleanly using Data -> Load Text/CSV. 
Make sure to pick the "Custom" delimiter option and the character pipe "|".

[howiekrauth]

I typed an apostrophe, then paste.  This worked for me.

[LRM84]

Hi!

I'm not able to execute this, could you please paste a picture of the entired flow?

Thanks in advance

[LRM84]

Hello,

Could you please share a detailed explanation on your solution? (with pics and result). I'm new in PA.

Thanks in advance