Need help parsing a TXT file with non-standard delimiters

[taxtechfl]

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.

[jacob_kahn]

What do you mean by 'text delimiter'?

Thanks, 

Jacob

[taxtechfl]

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.

[Maskell_Rascal · Accepted solution]

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

[taxtechfl]

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