Get complicated .txt files into one excel table

[GeeWhiz7]

Hi gurus,

I'd like to think I'm getting better at vba little by little, but then I get a problem like this...
If this is too difficult, I completely get it as I don't even know where to begin.

I have a folder with tab delimited text files inside. Each of those text files has the same format as below and unfortunately cannot be exported from the original software as single tables.
Historically, I have loaded into excel using the import wizard and manually extracted each individual table to compile it into a single table, example farther below. I've used other means in to help in the past like Python or PowerQuery, but its still a multi-step process.

Is there any way to do this with a macro considering the following:

• The text files are in a folder and need to be read using Input, LOF? Can they be done one after the other or even just file by file would work as I only get a new file every month.
• The whole text file is led by a tab (red line) and then all the rest of the delimitations are tab.
• The name of the "Run" is repeated before each table though I only need it once (next to red line)
• There are spaces above/below the header row and before the start of the next table (blue line)
• The column headers change column to column and also table to table (green circle)
• The column headers are always non-numeric, but take up multiple rows (cyan line with arrows)
• The dates look like they repeat in a table, but the timestamp is unique so all repeats need to be kept.
• Despite the table showing zeros there is data farther down, I need to keep zeros and the other data.

Example text file shortened version:

Desired result.
You can see only 1 date column and the columns from each original table (2 of 3 shown) are now in same table. Names were CONCAT'd and TRIM'd of the 3 rows so are now in one string.

Thanks to anyone who might be able to help!



If you need the .txt data, I've pasted it below. I think if you paste it back into notepad and then save as txt you'd have what I'm working with.


SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
DATE WPIW WPIW WPIW WPIW WPIW WPIW WPIG WPIG WPIG
SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B
MRB-3HC HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC

01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 0 0 0 0
02-JAN-2024 0 0 0 0 0 0 0 0 0
02-JAN-2024 0 0 0 0 0 0 0 0 0
03-JAN-2024 0 0 0 0 0 0 0 0 0
03-JAN-2024 0 0 0 0 0 0 0 0 0
03-JAN-2024 0 0 0 0 0 0 0 0 0


SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
DATE WPIG WPIG WPIG WPIG WPIG WPIL WPIL WPIL WPIL
SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B
HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN

01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 0 549.5508 320.5572 27.40325 0
01-JAN-2024 0 0 0 0 0 550.3854 321.9248 27.44345 0
01-JAN-2024 0 0 0 0 0 551.0506 323.0171 27.47693 0
01-JAN-2024 0 0 0 0 0 551.7615 324.1922 27.51474 0
01-JAN-2024 0 0 0 0 0 552.4380 325.3124 27.55220 0
02-JAN-2024 0 0 0 0 0 553.1949 326.5487 27.59479 0
02-JAN-2024 0 0 0 0 0 554.0783 327.9333 27.64356 0
03-JAN-2024 0 0 0 0 0 555.1747 329.5236 27.70093 0
03-JAN-2024 0 0 0 0 0 555.3403 329.7603 27.70949 0
03-JAN-2024 0 0 0 0 0 555.5401 330.0406 27.71968 0


SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
DATE WPIL WPIL WPIL WPIL WPIO WPIO WPIO WPIO WPIO
SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B SM3/D/B
RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN RB-6H_S5

01-JAN-2024 0 0 0 0 0 0 0 0 0
01-JAN-2024 0 0 0 0 549.5508 320.5572 27.40325 0 0
01-JAN-2024 0 0 0 0 550.3854 321.9248 27.44345 0 0
01-JAN-2024 0 0 0 0 551.0506 323.0171 27.47693 0 0
01-JAN-2024 0 0 0 0 551.7615 324.1922 27.51474 0 0
01-JAN-2024 0 0 0 0 552.4380 325.3124 27.55220 0 0
02-JAN-2024 0 0 0 0 553.1949 326.5487 27.59479 0 0
02-JAN-2024 0 0 0 0 554.0783 327.9333 27.64356 0 0
03-JAN-2024 0 0 0 0 555.1747 329.5236 27.70093 0 0
03-JAN-2024 0 0 0 0 555.3403 329.7603 27.70949 0 0
03-JAN-2024 0 0 0 0 555.5401 330.0406 27.71968 0 0

 

[BSALV]

you're welcome