Import TXT tables with multi-row records

[CErickson]

I receive some txt files that appear to be fixed width but have very little experience parsing files like this.  I've attached a sample.

I'm hoping to achieve an output that has the following:

Columns - Community; Prefix; Street; Str Suffix; Dir; Low; High; Side; ESN

The issue is that the headers and the records are on 2 rows each and the Community values have to come from a special header for each table, each table being grouped by community and spread across multiple pages.  I've read over a lot of posts regarding the multi-row formula tool, etc., but have not been able to find a good fit and was hoping to get some advice on where to start breaking these tables out and grouping them.

[TheOC]

hi @CErickson 

Are these txt files always in the same format?

[T_Willins · Accepted solution]

Hi @CErickson,

This reminds me of the Weekly Challenge I wrote (#188).  Attached is a workflow that puts all the data in a clean format that you can further parse, concatenate, etc..  Be sure to check box to "Allow Long Lines" in the import data as shown below.  Let me know if you have questions.

[CErickson]

Hi @TheOC

Yeah this data is always the same - pretty sure this report was originally designed to output on a rear-fed impact printer (by the looks of it) in a bygone age haha

At any rate, don't expect it to ever change while I'm handling it on my end.

[CErickson]

Hi @T_Willins

Thanks so much for this - I'll try this out today with the larger data set and see how it shakes out.  

[CErickson]

@T_Willins - I ran your w/f through several different batches of this data and it looks great!  I really appreciate the help 🙂