cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imcoDev
Regular Visitor

How to insert line break from JSON into Excel

Hello,

 

I had some records with line breaks and had to add them into a JSON.  To address the line breaks, I had to replace the instances with text:

\r\n

Afterwards, I mapped the JSON values to an Excel table using add rows.  However, the line breaks are not recognize in Excel.

What replacement text should I use in the JSON in order for the line breaks to show up in Excel?

 

 excel.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User III
Super User III

You should keep the line breaks as they are in the JSON. But when you reach the add rows section of the flow, use an expression to replace \r\n with a CRLF.

 

Excel has no problem understanding a CRLF, so that would work fine. An example expression to do this:

 

replace(variables('test')'\r\n', variables('CRLF'))
 
Change out variables('test') with your dynamic value and use this at the point when you insert the data into the Excel Row.

View solution in original post

2 REPLIES 2
Paulie78
Super User III
Super User III

You should keep the line breaks as they are in the JSON. But when you reach the add rows section of the flow, use an expression to replace \r\n with a CRLF.

 

Excel has no problem understanding a CRLF, so that would work fine. An example expression to do this:

 

replace(variables('test')'\r\n', variables('CRLF'))
 
Change out variables('test') with your dynamic value and use this at the point when you insert the data into the Excel Row.

View solution in original post

@Paulie78  Thank you, this ended up working.  

Usually, I do use an apply to each action to map each Excel row column with the output from JSON parse.  But for some reason when building the replace expressions the JSON parse output don't show up as dynamic variables.  So I had to set them to string variables first before I can use them as dynamic variables.  Thanks you again!

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (47,139)