Dynamic Fixed Width Text to Columns

[danrh]

Alright, I've got a doozy...

I receive fixed width text files that I currently parse out one by one.  I want to create a macro that auto-parses them.  The issue is that the number of columns and the width of individual columns changes from report to report.  The one I'm currently looking at comes like so:

The key to knowing the column widths is the second row with the dashes.  Two blank spaces separate each field.  Is there a way to take the format of a single row and parse the rest of the data accordingly?  I've poked at it with Regex, but no dice yet.

The only idea I've had thus far (and it ain't a good one) is to parse out every character onto a separate row, use a Multi-Row to figure out where the breaks are, and Concatenate them back together.  But, the size of the file is quickly going to become an issue.  200 characters across 200 records would give 40,000 rows --- this gets out of hand very quickly, not exactly a viable solution.

Assume no absolutes with the column naming conventions.  Could have letters, numbers, punctuation, etc.  I haven't built iterative marcros and while I want to learn, I'm not convinced that I want to learn today.  Looking to do this in a single pass if possible.

Thanks all!

[danrh]

Alright, y'all inspired me to take your ideas, spin them together, and make the macro my own :)

[MarqueeCrew]

@danrh,

Alteryx has such great flexibility in allowing users to configure tools (to the user's liking) to solve challenges in ways that make sense to each individual.  Great challenge for thinking through the problem and finding a way to simplify your life/work using Alteryx.

Cheers,

Mark

[mariaen]

Can you share the workflow before it was packaged into a macro?