cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcdf
Advocate II
Advocate II

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
rcdf
Advocate II
Advocate II

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

2 REPLIES 2
rcdf
Advocate II
Advocate II

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

howiekrauth
New Member

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

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (49,215)