topic Re: Need help parsing a TXT file with non-standard delimiters in Alteryx Designer Desktop Discussions

Need help parsing a TXT file with non-standard delimiters

taxtechfl — Wed, 27 Jan 2021 18:33:10 GMT

I am looking for suggestions on the most efficient means of parsing a file that has non-standard delimiters in it. There are a few issues with how the file was built:

The field delimiter is a pipe
The text delimiter is a tilde
The first field only has one text delimiter on the end of the value

I have created a sample of the text:

Field01~|~Field02~|~Field03~|~Field04~|~Field05~|~Field06~|~Field07~|~Field08~|~Field09~|~Field10~|~Field11~|~Field12~|~Field13~|~Field14~|~Field15~|~Field16~|~Field17~|~Field18~|~Field19~|~Field20~|~Field21~|~Field22~|~Field23~|~Field24~
AP~|~AbC~|~AbC_1234567890_1234_2020_0001234567~|~12345~|~AbC_1234~|~GB~|~16-07-2020~|~16-07-2020~|~1234567~|~ABCDEFGH LTD~|~GB~|~GB123456789~|~0123456789~|~AbC_V1~|~~|~123.1~|~0.0~|~123.1~|~GBP~|~123.1~|~0.0~|~123.1~|~GBP~|~RE

Thank you in advance for your assistance and feedback.

Re: Need help parsing a TXT file with non-standard delimiters

jacob_kahn — Wed, 27 Jan 2021 19:03:46 GMT

What do you mean by 'text delimiter'?

Thanks,

Jacob

Re: Need help parsing a TXT file with non-standard delimiters

taxtechfl — Wed, 27 Jan 2021 19:10:54 GMT

I should have said "text qualifier." For example, in a typical CSV you may have the commas separating the fields while the values within each field will be inside of a set of double-quotes.

In the sample data you can see that the pipe separates the fields and the values within each field are inside of tildes (as opposed to quotes).

Thanks.

Re: Need help parsing a TXT file with non-standard delimiters

Maskell_Rascal — Wed, 27 Jan 2021 19:11:52 GMT

Hi @taxtechfl

It appears that you have "|" as a special character that you can use as a delimiter. If this is the case, the attached workflow should work for you.

You first use "|" as the delimiter on the input tool, but will then need to couple more steps to cleanse the data of the "~".

I'm using a RecordID, Crosstab, and Transpose tools to flip the data, and a Formula tool to trim the tilde from all fields and records. I can then use the Dynamic rename tool to select the first row of data as the field headers.

Attached is the workflow.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Re: Need help parsing a TXT file with non-standard delimiters

taxtechfl — Wed, 27 Jan 2021 20:00:50 GMT

Thank you @Maskell_Rascal for the solution. I appreciate your quick response to my post.