Parse JSON action is adding line breaks

[mboonie]

Hi. I am using the Parse JSON action to work with a data file of >13k rows (if that's important) which then updates the information in a SharePoint list.

Unfortunately, I am finding that the results that are returned seem to have random line breaks added between words for no apparent reason. For example, in the screen shot shown here, you can see that the output of the simple, short string "Phase 2" ends up with a newline instead of a space. (Of course, I checked the input file and there are no line-breaks found there.) To compound the issue, there are rules in the list itself that use this data in other calculations and JS link formatting, but which now don't recognize the data as "valid" (i.e. "Phase\n2" <> "Phase 2"!) I find that I need to go into the list and edit out the line-breaks (which appear as an HTML "<br>") manually, even from Single Line of Text fields.

Does anyone else experience this? And what can be done? I tried using string substitutions, but Flow acts like the break isn't there; yet it still returns it with a break in-between the two words! This workflow already takes well over two hours to run, so I'd rather not do too much text manipulation if I don't have to.

Help!

[v-bacao-msft]

Hi @mboonie,

Could you provide specific details of your Flow configuration?

I don't know how to rebuild your Flow like this.

I am currently thinking about some attributes in your schema that contain spaces and the like.

Please provide more details and we would provide suitable workaround for you.

Best Regards,

Barry

[mboonie · Accepted solution]

My apologies! I stand very much corrected: it is the HTML to Text action that is introducing the line breaks, not Parse JSON, which is using the results as input.

As an example, my test JSON file is 247,506 characters long (including asc(13) line-breaks where appropriate), and is an export from a proprietary database. Some of the data contains HTML-encoded formatting (because the database contains a number of rich-text fields, with bulleted lists, etc.) To have cleaner data, I chose to use the HTML to Text action.

And therein lies the rub.

That action appears to ignore the original line breaks, adding its own seemingly at random. While the first line break it adds is at position 256 (as one might expect when dealing with strings), the rest are added seemingly at random. For example, the next line is 46 characters long; the one after that is 122 characters long, etc.

The break is never in the middle of a word, however, but always replaces a space between words. There is definitely no pattern to WHICH words or phrases are fair game, though.

Using the "body" of the HTML to Text action, the Parse JSON action has no choice except to add the "\n" new-line character to make it legitimate JSON data. So, that action is off the hook.

At this point, I'm just going to change the SharePoint columns to rich-text Multi-Line columns (to accomodate the user-formatted input), and tell the stakeholders that "cleaning" their verbose, formatted input is not practical at this point. (Maybe I'll try to "clean" it further downstream when displaying it; InfoPath comes to mind.)

Nonetheless, it would be nice if that but could be addressed.

-Boonie

[mboonie]

My apologies! I stand very much corrected: it is the HTML to Text action that is introducing the line breaks, not Parse JSON, which is using the results as input.

As an example, my test JSON file is 247,506 characters long (including asc(13) line-breaks where appropriate), and is an export from a proprietary database. Some of the data contains HTML-encoded formatting (because the database contains a number of rich-text fields, with bulleted lists, etc.) To have cleaner data, I chose to use the HTML to Text action.

And therein lies the rub.

That action appears to ignore the original line breaks, adding its own seemingly at random. While the first line break it adds is at position 256 (as one might expect when dealing with strings), the rest are added seemingly at random. For example, the next line is 46 characters long; the one after that is 122 characters long, etc.

The break is never in the middle of a word, however, but always replaces a space between words. There is definitely no pattern to WHICH words or phrases are fair game, though.

Using the "body" of the HTML to Text action, the Parse JSON action has no choice except to add the "\n" new-line character to make it legitimate JSON data. So, that action is off the hook.

At this point, I'm just going to change the SharePoint columns to rich-text Multi-Line columns (to accomodate the user-formatted input), and tell the stakeholders that "cleaning" their verbose, formatted input is not practical at this point. (Maybe I'll try to "clean" it further downstream when displaying it; InfoPath comes to mind.)

Nonetheless, it would be nice if that but could be addressed.

-Boonie

[irisp]

are you passing correct JSON data, you can test it using https://jsonformatter.org

[mboonie]

The data and schema are definitely both properly formatted. There is nothing in the content to make anything think a line break was needed at any place; each new line-break replaced a standard asc(32) space. The JSON files are 250-300kb, with the longest line of data (<=800 characters). As I mentioned, the first break is inserted exactly at position 256. Coincidence? I think not.

As I mentioned above, if I don't use the HTML to Text converter, things come in as they should (albeit with HTML tags in the source's rich-text fields.)

I actually used JSON Formatter (https://jsonformatter.curiousconcept.com/) to check the data, and then used the Parse JSON "Use payload to generate schema" feature to get the initial schema, which I then verified and tidied up in Notepad++.

Yep, it's the HTML to Text that's the culprit. 

[Anonymous]

@mboonie : i am facing the same issue, how did you manage to solve the line breaks ? 

[mboonie]

Honestly, I couldn't find any way to clean it up, so I just accepted the HTML formatting as it was and made sure the destination columns were Rich Text, so the information could be pasted in without extranneous tags.

Most of my data was from an unformatted source, so I was fortunate enough that most of the data was fine (in fact, BETTER than "fine") without trying to use the HTML to Text conversion. I just run my JSON through a Word VBA function to convert other things (like changing underlined Greater-Than sybmols into proper Greater-Than-Or-Equal-To, i.e. replace <u>&gt;</u> with &ge;, or replace &#34; with a JSON quote \")

I could find no rhyme nor reason for the random line-breaks. It wasn't always after a set number of characters, nor did it only happen in a particular kind of JSON object. If it weren't for that, it would have been a sweet tool to use.

[Anonymous]

thank you for the replay. i am not as lucky as you are with my text, as am having substring function after the conversion that is throwing errors when the break lines are entered.  

[mboonie]

I haven't tried it myself, but have you tried the Replace function (Replace( String, OldString, NewString )? Use '\n' for the OldString, and empty quotes '' as NewString). Here I'm using it in a Compose action, to clean the TeamName string:

[Anonymous]

thanks for the idea! tried it but did not work in my context, the output of the html to text function does not recognize the /n as a break line.