cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Entire JSON Object to Single Cell in CSV

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

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. 

Pic1.png

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.
Pic2.png

 

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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. 

Pic1.png

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.
Pic2.png

 

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
New Member

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

Hi!

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

Thanks in advance

Hello,

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

Thanks in advance

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,587)