Help in Importing a batch txt file which inconsistent file format

That was a fun challenge! Here is my take on it but its not dynamic so if the structure of the file (and any assumptions made on filtering etc) change it will break:

First import it in as a CSV with no delimiter, starting on row 13 and extend the Field Length so nothing gets missed:

You then want to filter out any rows that are not required, based on identified patterns:

Then you generate a row number, if it starts with a pipe then its 1, else its 2. Filter on row 1 and join these two streams back, but joining on record position:

Using Formula tool combine Field_1 and Right_Field_1, then using Text to Columns tool split on | into 66 columns (this is where it will break if the number of columns differ with each file).

Take the header from the first row of data using Dynamic Rename tool.

Remove any columns not required, undertake data cleansing to remove all the duplicate whitespace etc, and finally filter out any rows that start with SGrp to remove the extra headers.

I'd double check the output to make sure it aligns!

Text File Import.yxmd

Hello davidskaife,

Thank you for the solution. Though it is not dynamic but solves 90% of the problem. I can work on making it dynamic, but your solutions helped with main problem.

https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Batch-Macros-1583461640393

You're very welcome. If you need help making it dynamic please do share the details and the Community can rise to the challenge!

As my workflow helped you would you mind accepting it as the solution so others may find and use it?

All comments

SGolnik

I would start with one file and read the whole file in as a csv with \0 as the delimiter. This should put the whole file into a single cell. Parse to rows using text to columns on a new line delimiter \n. Filter out the garbage rows. Then parse to columns using the pipe delimiter. Once you have that file parsed out, then it can be turned into a batch macro.

https://help.alteryx.com/current/en/designer/apps-and-macros/macros/batch-macro.html

That was a fun challenge! Here is my take on it but its not dynamic so if the structure of the file (and any assumptions made on filtering etc) change it will break:

First import it in as a CSV with no delimiter, starting on row 13 and extend the Field Length so nothing gets missed:

You then want to filter out any rows that are not required, based on identified patterns:

Then you generate a row number, if it starts with a pipe then its 1, else its 2. Filter on row 1 and join these two streams back, but joining on record position:

Using Formula tool combine Field_1 and Right_Field_1, then using Text to Columns tool split on | into 66 columns (this is where it will break if the number of columns differ with each file).

Take the header from the first row of data using Dynamic Rename tool.

Remove any columns not required, undertake data cleansing to remove all the duplicate whitespace etc, and finally filter out any rows that start with SGrp to remove the extra headers.

I'd double check the output to make sure it aligns!

Text File Import.yxmd

Hello davidskaife,

Thank you for the solution. Though it is not dynamic but solves 90% of the problem. I can work on making it dynamic, but your solutions helped with main problem.

This is an accepted answer. Great help. I have a folder with muliple files as such , every week the folder is added new file. Here while importing I want to make the dynamic so that i do not need to mention the "Start data import on Line" as it might not be same for every file.

Hello ,

The second macro is not working . Also the issue is as such ..
the columns are :but when while importing due to inconsistent file format they are splitting into two rows .. the starting string of second row is not standard:
sometime secodn ;line is starting with "|Customer daily usage" some time "non premium" so this below condition is not working properlly :
IF StartsWith([Field_1], '|')
THEN 1
ELSE 2
ENDIF