Import TXT tables with multi-row records

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.

sample_output.xlsx

sample.txt

Parse

Accepted answers

T_Willins

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.

Import TXT tables workflow.jpg

Input Data.jpg

Import TXT tables.yxmd

All comments

TheOC

hi @CErickson

Are these txt files always in the same format?

T_Willins

Hi @CErickson,

Import TXT tables workflow.jpg

Input Data.jpg

Import TXT tables.yxmd

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 🙂

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703