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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (3,648)