Reading Text File

[dshaw]

I need some help reading in a text file with the following details:

Format:                           delimited text

Text Delimiter:                  none

Field Delimiter:                 ‘^|^’  (less the quotes)

Line Delimiter:                   cr/lf

Thanks,

Derreck

[RolandSchubert]

Hi @dshaw ,

you could load data without field separator (each line is one field then), replace "^|^" by "|" using a formula and split to columns using the Text to Columns tool with separator "|". What do you think?

Best

Roland

[echuong1]

I second @Ron 's approach. The only thing I would add is to bring the file into Alteryx without field names so they can be parsed simultaneously. You can then use a dynamic rename to add the headers. See attached for an example. Let me know if that works! 

[dshaw]

I think this is all going into the right direction but I am still having an issue which may have to do with the Line Delimiter.  That is something that I am not familiar with. Also, I am not sure replacing the '^|^' is working since that delimiter is a long delimiter. So, perhaps the key is the Line Delimiter, first, then the Line Delimiter.  Attached are what the before and after looks like using your example.

Below are the instructions I received:

Format:                           delimited text

Text Delimiter:                  none

Field Delimiter:                 ‘^|^’  (less the quotes)

Line Delimiter:                   cr/lf

[RolandSchubert · Accepted solution]

Hi @dshaw ,

I think, the problem is the number of columns you used in Text to Columns tool. You can adjust the number of columns in the tool:

This seems to be the easiest way to handle the problem, if the number of columns is identifcal for each record.

If the number of columns isn't identical for all records, you can split to rows a cross-tab then.

I've added a sample workflow (similar to the workflow created by @echuong1 ). Hope his solves your problem. If it still doesn't work, it would be helpful to provide a sample file.

Best

Roland

[bzielinski11]

Hi @RolandSchubert - i came across this solution you posted and it was very helpful for me to get started. I am using this to read a text file with transaction data that is delimited with a #|# - the issue i am running into relates to one of the fields having line breaks. One of the fields in this transaction data is a "memo" line where the user can input any length and format of their own comments that relate to a given transaction row. There are multiple instances where a memo line contains multiple line breaks and I can't figure out how to get around this. each row. I can't post actual data but see below for an example:

Any thought on how i could get past the line breaks? when i tried the workflow you uploaded the issue i ran into was each line break became column 1 again so there were a ton of rows that just had the data in the first column and the rest of the fields were null.