Line breaking during CSV Parser

[Matt]

Hey Everyone,

Here’s an example of what I’m having issues with, since the contents that are inserted into the NOTES column have multiple line breaks. The data I’m receiving is via SFTP and wanted to see if this can be solved in Snap Logic.

The bolded text is the data that will be inserted into the NOTES column.

MODDATE|USERID|REC_NUM|CASE_NUM|DATE|TIME|DESCR|EXAMINER|TYPE|NOTES
20160203|DOMINGUEZMATTHEW|12345678|123456789|20081015|1130|Initial report|TESTING||TESTING NOTES

This text is still part of NOTES and is using line break to

that makes it go on multiple lines

What I need is to make all of this text appear in the NOTES column

-----------------------------------------------------------------------------

this is an example of what appears on our csv that we receive from our vendor

majority of it are copy and paste emails that the users have inserted

They go on for at least 50 lines until the next record is available

[tstack]

Matt:

Here’s an example of what I’m having issues with, since the contents that are inserted into the NOTES column have multiple line breaks.

What tool is generating these CSV files? It seems a little unusual that the multi-line columns are not quoted or escaped in some way. Adding some double-quotes around the multi-line column gets the CSV Parser snap as you would expect.

[Matt]

We are receiving these files from a vendor, I wanted to know if it would be possible in Snap Logic to remove the line breaks before requesting our vendor to remove it on their end.

[epearson]

I think it is possible, but a little tricky. You want to replace the extra line breaks, but not the ones that delimit the end of a record. I think you could do this with a regular expression … but in the end, having line breaks in the csv that way is not valid.

[epearson]

Actually, if the offending line breaks are consistently in the very last field, here is the logic I would start with to build out the regular expression:

Replace all newline \n characters with spaces except for a newline character that has some number of characters, except another newline character, and a pipe | character.

or …

Replace a newline character if it is eventually followed by another newline character before a pipe | is reached.

They amount to the same thing.