Pipe delimited file with new line characters in columns

[HeinL]

I have a pipe (|) delimited file that I want to read into Alteryx.  The problem is that the file contains new line characters in some of the columns.  This means that the particular record Alteryx gives the following warning: Input Data (5) Record #94916: Not enough fields in record.

I am trying to find a way to read the data properly.  I tried reading the data without a delimiter (\0) in the Input Data node.  I thought by doing so I could remove the new line character using RegEx or Formula nodes.  The problem is that it splits the record into two at the new line character which makes it very difficult to use any of the methods for dealing with delimiters that some of the users suggested before.

Any assistance is appreciated.

[AdamR_AYX · Accepted solution]

I think I have got this working. (see attachment)

The data is going to need to not have any pipes inside the fields and you will need to know how many fields the data is supposed to have up front.

Let me know how it works against your data.

[HeinL · Accepted solution]

Thanks a lot.  With a few changes to accommodate for my specific dataset this will work perfectly.

Hein Lochner

[TomKing]

This worked in my case as well, <CR><LF>s embedded in the fields - saved me hours of work.   Thanks Adam!