Get complicated .txt files into one excel table

G

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
1645647550563.png

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.
1645649426375.png

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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
can you add a link with a real textfile instead of a screenshot ?

20220224_01.xlsm
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1WPIW SM3/D/B HIFTDOWNWPIW SM3/D/B RB-6H_S5WPIW SM3/D/B _SHIFTUPWPIW SM3/D/B INJ-2DWPIW SM3/D/B -1H_REV2WPIG SM3/D/B MRB-1HCWPIG SM3/D/B MRB-2HCWPIG SM3/D/B MRB-3HCWPIG SM3/D/B MRB-3HCWPIG SM3/D/B HIFTDOWNWPIG SM3/D/B RB-6H_S5WPIG SM3/D/B _SHIFTUPWPIG SM3/D/B INJ-2DWPIG SM3/D/B -1H_REV2WPIL SM3/D/B MRB-1HCWPIL SM3/D/B MRB-2HCWPIL SM3/D/B MRB-3HCWPIL SM3/D/B HIFTDOWNWPIL SM3/D/B RB-6H_S5WPIL SM3/D/B _SHIFTUPWPIL SM3/D/B INJ-2DWPIL SM3/D/B -1H_REV2WPIO SM3/D/B MRB-1HCWPIO SM3/D/B MRB-2HCWPIO SM3/D/B MRB-3HCWPIO SM3/D/B HIFTDOWNWPIO SM3/D/B RB-6H_S5
2SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/24000000000000000000000000000
3SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000549,5508320,557227,4032500000549,5508320,557227,4032500
4SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000550,3854321,924827,4434500000550,3854321,924827,4434500
5SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000551,0506323,017127,4769300000551,0506323,017127,4769300
6SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000551,7615324,192227,5147400000551,7615324,192227,5147400
7SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000552,438325,312427,552200000552,438325,312427,552200
8SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED2/jan/2400000000000000553,1949326,548727,5947900000553,1949326,548727,5947900
9SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED2/jan/2400000000000000554,0783327,933327,6435600000554,0783327,933327,6435600
10SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,1747329,523627,7009300000555,1747329,523627,7009300
11SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,3403329,760327,7094900000555,3403329,760327,7094900
12SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,5401330,040627,7196800000555,5401330,040627,7196800
GeeWiz

VBA Code: 
Option Compare Text
Sub GeeWiz()
     Dim ptr_r, ptr_k
     With Sheets("GeeWiz")
          a = .UsedRange.Columns(1).Value
          Dim result(1 To 100, 1 To 100)
          For i = 1 To UBound(a)
               If Left(a(i, 1), 7) = "summary" Then
                    my_summary = a(i, 1)
                    ptr_r = 1
                    For i1 = 1 To 3
                         sp = Split(a(i + i1, 1))
                         If i1 = 1 Then
                              Header = sp
                         Else
                              For k = 0 To Application.Min(UBound(sp), UBound(Header))
                                   Header(k + 1) = Header(k + 1) & vbLf & sp(k)
                              Next
                         End If
                    Next
                    ptr_k = Application.Max(3, ptr_k + 1)
                    k_start = ptr_k
                    For k1 = 1 To UBound(Header)
                         result(1, ptr_k) = Header(k1)
                         ptr_k = ptr_k + 1

                    Next
               Else
                    s = Left(a(i, 1), 11)
                    If s Like "##-???-####" Then
                         ptr_r = ptr_r + 1
                         result(ptr_r, 1) = my_summary
                         result(ptr_r, 2) = s
                         sp = Split(a(i, 1))
                         For k = 1 To UBound(sp)
                              result(ptr_r, k_start - 1 + k) = sp(k)
                         Next
                    End If
               End If
          Next
          .Range("G1").Resize(ptr_r, ptr_k).Value = result

     End With

End Sub
 
Upvote 0
BSALV said:
can you add a link with a real textfile instead of a screenshot ?

20220224_01.xlsm
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1WPIW SM3/D/B HIFTDOWNWPIW SM3/D/B RB-6H_S5WPIW SM3/D/B _SHIFTUPWPIW SM3/D/B INJ-2DWPIW SM3/D/B -1H_REV2WPIG SM3/D/B MRB-1HCWPIG SM3/D/B MRB-2HCWPIG SM3/D/B MRB-3HCWPIG SM3/D/B MRB-3HCWPIG SM3/D/B HIFTDOWNWPIG SM3/D/B RB-6H_S5WPIG SM3/D/B _SHIFTUPWPIG SM3/D/B INJ-2DWPIG SM3/D/B -1H_REV2WPIL SM3/D/B MRB-1HCWPIL SM3/D/B MRB-2HCWPIL SM3/D/B MRB-3HCWPIL SM3/D/B HIFTDOWNWPIL SM3/D/B RB-6H_S5WPIL SM3/D/B _SHIFTUPWPIL SM3/D/B INJ-2DWPIL SM3/D/B -1H_REV2WPIO SM3/D/B MRB-1HCWPIO SM3/D/B MRB-2HCWPIO SM3/D/B MRB-3HCWPIO SM3/D/B HIFTDOWNWPIO SM3/D/B RB-6H_S5
2SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/24000000000000000000000000000
3SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000549,5508320,557227,4032500000549,5508320,557227,4032500
4SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000550,3854321,924827,4434500000550,3854321,924827,4434500
5SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000551,0506323,017127,4769300000551,0506323,017127,4769300
6SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000551,7615324,192227,5147400000551,7615324,192227,5147400
7SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED1/jan/2400000000000000552,438325,312427,552200000552,438325,312427,552200
8SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED2/jan/2400000000000000553,1949326,548727,5947900000553,1949326,548727,5947900
9SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED2/jan/2400000000000000554,0783327,933327,6435600000554,0783327,933327,6435600
10SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,1747329,523627,7009300000555,1747329,523627,7009300
11SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,3403329,760327,7094900000555,3403329,760327,7094900
12SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED3/jan/2400000000000000555,5401330,040627,7196800000555,5401330,040627,7196800
GeeWiz

VBA Code: 
Option Compare Text
Sub GeeWiz()
     Dim ptr_r, ptr_k
     With Sheets("GeeWiz")
          a = .UsedRange.Columns(1).Value
          Dim result(1 To 100, 1 To 100)
          For i = 1 To UBound(a)
               If Left(a(i, 1), 7) = "summary" Then
                    my_summary = a(i, 1)
                    ptr_r = 1
                    For i1 = 1 To 3
                         sp = Split(a(i + i1, 1))
                         If i1 = 1 Then
                              Header = sp
                         Else
                              For k = 0 To Application.Min(UBound(sp), UBound(Header))
                                   Header(k + 1) = Header(k + 1) & vbLf & sp(k)
                              Next
                         End If
                    Next
                    ptr_k = Application.Max(3, ptr_k + 1)
                    k_start = ptr_k
                    For k1 = 1 To UBound(Header)
                         result(1, ptr_k) = Header(k1)
                         ptr_k = ptr_k + 1

                    Next
               Else
                    s = Left(a(i, 1), 11)
                    If s Like "##-???-####" Then
                         ptr_r = ptr_r + 1
                         result(ptr_r, 1) = my_summary
                         result(ptr_r, 2) = s
                         sp = Split(a(i, 1))
                         For k = 1 To UBound(sp)
                              result(ptr_r, k_start - 1 + k) = sp(k)
                         Next
                    End If
               End If
          Next
          .Range("G1").Resize(ptr_r, ptr_k).Value = result

     End With

End Sub
Click to expand...

Hi Bsalv, here is a link to a shortened version of a typical file which normally have a couple thousand rows and a couple dozen columns. I will also try the code you provided a little bit later this morning...after I try to figure out how the logic works. Thank you for helping with this.

Complicated txt file link
 
Upvote 0
GeeWhiz7 said:
Hi Bsalv, here is a link to a shortened version of a typical file which normally have a couple thousand rows and a couple dozen columns. I will also try the code you provided a little bit later this morning...after I try to figure out how the logic works. Thank you for helping with this.

Complicated txt file link
Click to expand...
Hi Bsalv, your code does work when I replicate what I think you did which was to copy and paste the text from my first post into a tab called GeeWiz. I should have put the link to a text file in (as you suggested) since it looks like the code does not work on that text if cut and paste into the same location on GeeWiz. I guess some formatting gets changed when going .txt-to-mrexcel-to-excel tab vs going txt-to-excel.
 
Upvote 0
GeeWhiz7 said:
Hi Bsalv, your code does work when I replicate what I think you did which was to copy and paste the text from my first post into a tab called GeeWiz. I should have put the link to a text file in (as you suggested) since it looks like the code does not work on that text if cut and paste into the same location on GeeWiz. I guess some formatting gets changed when going .txt-to-mrexcel-to-excel tab vs going txt-to-excel.
Click to expand...
I tried these steps to be able to use your code, but now I'm out of my depth again so if you had an idea how to use the text file from the link with your code, it would be greatly appreciated.
  • Just to see I used .texttocolumns to try to take my .txt file into a format more similar to what you were working with in column 1.
VBA Code: 
Sub TxtFileToSingleColumn()
 With Application.FileDialog(msoFileDialogFilePicker) 'get the original text file
    If .Show Then
       c00 = .SelectedItems(1)
       c01 = Replace(Split(c00, "\")(UBound(Split(c00, "\"))), ".txt", "") & "new" & ".xlsx" 'make a new file for the result
       With Workbooks.Add
          ar = Split(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, vbLf) 'split by Return
          With .Sheets(1).Cells(1, 1) 'parse by the delimiters...don't understand why I'm left with multiple spaces in the result further below.
             .Resize(UBound(ar) + 1) = Application.Transpose(ar)
             .CurrentRegion.TextToColumns .Offset, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, Comma:=True, Space:=True, Other:=False
          End With
         .SaveAs c01, 51
         .Close
       End With
    End If
 End With
End Sub

  • The plan was then to take that format which I thought would be similar to what you were working on from post 1 and then call your code.
  • Unfortunately, even though things are now in a single column, this still leaves some tabs and spaces (instead of one single space) in the result so I couldn't then call your code successfully since I'm not savvy enough to get past the basic syntax of texttocolumns.
Not quite the same as post 1, close, but not good enough.
BasicInputTestnew.xlsx
ABCDEFGHIJKL
3 SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
4 DATE WPIW WPIW WPIW WPIW WPIW WPIW WPIG WPIG WPIG
5 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
6 MRB-3HC HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC
7
8 01-JAN-2024 0 0 0 0 0 0 0 0 0
9 01-JAN-2024 0 0 0 0 0 0 0 0 0
10 01-JAN-2024 0 0 0 0 0 0 0 0 0
11 01-JAN-2024 0 0 0 0 0 0 0 0 0
12 01-JAN-2024 0 0 0 0 0 0 0 0 0
13 01-JAN-2024 0 0 0 0 0 0 0 0 0
14 02-JAN-2024 0 0 0 0 0 0 0 0 0
15 02-JAN-2024 0 0 0 0 0 0 0 0 0
16 03-JAN-2024 0 0 0 0 0 0 0 0 0
17 03-JAN-2024 0 0 0 0 0 0 0 0 0
18 03-JAN-2024 0 0 0 0 0 0 0 0 0
19
20
21 SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
22 DATE WPIG WPIG WPIG WPIG WPIG WPIL WPIL WPIL WPIL
23 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
24 HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN
25
26 01-JAN-2024 0 0 0 0 0 0 0 0 0
27 01-JAN-2024 0 0 0 0 0 549.5508 320.5572 27.40325 0
28 01-JAN-2024 0 0 0 0 0 550.3854 321.9248 27.44345 0
29 01-JAN-2024 0 0 0 0 0 551.0506 323.0171 27.47693 0
30 01-JAN-2024 0 0 0 0 0 551.7615 324.1922 27.51474 0
31 01-JAN-2024 0 0 0 0 0 552.4380 325.3124 27.55220 0
32 02-JAN-2024 0 0 0 0 0 553.1949 326.5487 27.59479 0
33 02-JAN-2024 0 0 0 0 0 554.0783 327.9333 27.64356 0
34 03-JAN-2024 0 0 0 0 0 555.1747 329.5236 27.70093 0
35 03-JAN-2024 0 0 0 0 0 555.3403 329.7603 27.70949 0
36 03-JAN-2024 0 0 0 0 0 555.5401 330.0406 27.71968 0
37
38
39 SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
40 DATE WPIL WPIL WPIL WPIL WPIO WPIO WPIO WPIO WPIO
41 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
42 RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN RB-6H_S5
43
44 01-JAN-2024 0 0 0 0 0 0 0 0 0
45 01-JAN-2024 0 0 0 0 549.5508 320.5572 27.40325 0 0
46 01-JAN-2024 0 0 0 0 550.3854 321.9248 27.44345 0 0
47 01-JAN-2024 0 0 0 0 551.0506 323.0171 27.47693 0 0
48 01-JAN-2024 0 0 0 0 551.7615 324.1922 27.51474 0 0
49 01-JAN-2024 0 0 0 0 552.4380 325.3124 27.55220 0 0
50 02-JAN-2024 0 0 0 0 553.1949 326.5487 27.59479 0 0
51 02-JAN-2024 0 0 0 0 554.0783 327.9333 27.64356 0 0
52 03-JAN-2024 0 0 0 0 555.1747 329.5236 27.70093 0 0
53 03-JAN-2024 0 0 0 0 555.3403 329.7603 27.70949 0 0
54 03-JAN-2024 0 0 0 0 555.5401 330.0406 27.71968 0 0
Sheet1


What you were dealing with after cut and past from post 1 and what I was trying to achieve with texttocolumns
TestRSMLoad.xlsb
ABCD
1SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
2DATE WPIW WPIW WPIW WPIW WPIW WPIW WPIG WPIG WPIG
3SM3/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
4MRB-3HC HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC
5
601-JAN-2024 0 0 0 0 0 0 0 0 0
701-JAN-2024 0 0 0 0 0 0 0 0 0
801-JAN-2024 0 0 0 0 0 0 0 0 0
901-JAN-2024 0 0 0 0 0 0 0 0 0
1001-JAN-2024 0 0 0 0 0 0 0 0 0
1101-JAN-2024 0 0 0 0 0 0 0 0 0
1202-JAN-2024 0 0 0 0 0 0 0 0 0
1302-JAN-2024 0 0 0 0 0 0 0 0 0
1403-JAN-2024 0 0 0 0 0 0 0 0 0
1503-JAN-2024 0 0 0 0 0 0 0 0 0
1603-JAN-2024 0 0 0 0 0 0 0 0 0
17
18
19SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
20DATE WPIG WPIG WPIG WPIG WPIG WPIL WPIL WPIL WPIL
21SM3/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
22HIFTDOWN RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN
23
2401-JAN-2024 0 0 0 0 0 0 0 0 0
2501-JAN-2024 0 0 0 0 0 549.5508 320.5572 27.40325 0
2601-JAN-2024 0 0 0 0 0 550.3854 321.9248 27.44345 0
2701-JAN-2024 0 0 0 0 0 551.0506 323.0171 27.47693 0
2801-JAN-2024 0 0 0 0 0 551.7615 324.1922 27.51474 0
2901-JAN-2024 0 0 0 0 0 552.4380 325.3124 27.55220 0
3002-JAN-2024 0 0 0 0 0 553.1949 326.5487 27.59479 0
3102-JAN-2024 0 0 0 0 0 554.0783 327.9333 27.64356 0
3203-JAN-2024 0 0 0 0 0 555.1747 329.5236 27.70093 0
3303-JAN-2024 0 0 0 0 0 555.3403 329.7603 27.70949 0
3403-JAN-2024 0 0 0 0 0 555.5401 330.0406 27.71968 0
35
36
37SUMMARY OF RUN BTC_IAB_REVCLOSEDF_WD_P1P2I0_OPTIMIZED
38DATE WPIL WPIL WPIL WPIL WPIO WPIO WPIO WPIO WPIO
39SM3/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
40RB-6H_S5 _SHIFTUP INJ-2D -1H_REV2 MRB-1HC MRB-2HC MRB-3HC HIFTDOWN RB-6H_S5
41
4201-JAN-2024 0 0 0 0 0 0 0 0 0
4301-JAN-2024 0 0 0 0 549.5508 320.5572 27.40325 0 0
4401-JAN-2024 0 0 0 0 550.3854 321.9248 27.44345 0 0
4501-JAN-2024 0 0 0 0 551.0506 323.0171 27.47693 0 0
4601-JAN-2024 0 0 0 0 551.7615 324.1922 27.51474 0 0
4701-JAN-2024 0 0 0 0 552.4380 325.3124 27.55220 0 0
4802-JAN-2024 0 0 0 0 553.1949 326.5487 27.59479 0 0
4902-JAN-2024 0 0 0 0 554.0783 327.9333 27.64356 0 0
5003-JAN-2024 0 0 0 0 555.1747 329.5236 27.70093 0 0
5103-JAN-2024 0 0 0 0 555.3403 329.7603 27.70949 0 0
5203-JAN-2024 0 0 0 0 555.5401 330.0406 27.71968 0 0
GeeWiz
 
Upvote 0
can you add a link on another platform then Google, apparently i can't login with my password.
You you have onedrive or ... ?
 
Upvote 0
you are asked to point a textfile, that 'll be opened, read and closed again.
As temporary measure, that is written in a sheet "blad1" but is unnecessary for the execution of the program.
then the rest is like this morning, but vbtab (ascii-9) is now the separator.

VBA Code: 
Option Compare Text
Sub GeeWiz()
     Dim ptr_r, ptr_k


     '************************
     'choose, open and read textfile*
     '************************
     With Application.FileDialog(msoFileDialogFilePicker)       'open dialog for choosing the right textfile
          .AllowMultiSelect = False
          .Title = UCase("Choose your logfile")
          With .Filters
               .Clear
               .Add "Text Files (*.txt)", "*.txt"
          End With

          If Not .Show() Then
               Exit Sub                                         'nothing choosen
          Else
               sfileName = .SelectedItems(1)                    'the textfile you choose

               fileNo = FreeFile                                'Get first free file number
               Open sfileName For Input As #fileNo              'open textfile
               a = Split(Input$(LOF(fileNo), fileNo), vbLf)     'read content and split on vblf = your whole txt-file is in memory (array a)
               Close #fileNo                                    'close textfile
               Sheets("blad1").Range("A1").Resize(UBound(a) + 1).Value = Application.Transpose(a)     'write to a sheet somewhere
          End If
     End With

     '**************************
     'manipulate data, chr(9) = vbtab
     '**************************
     Dim result(1 To 1000, 1 To 1000)                           'make an oversized array

     With Sheets("GeeWiz")                                      '--------->THIS IS THE WORKSHEET WHERE EVERYTHING IS WRITTEN TO

          For i = 0 To UBound(a)

     '***********************************************************************
     'the name of the summary (starts with a vbtab, ends with lots of spaces and a vbtab)
     'the next 3 lines, also separated by vbtab, have to be correctly joined to make the right headers
     '***********************************************************************
               If Left(a(i), 8) = vbTab & "summary" Then
                    my_summary = Trim(Replace(a(i), vbTab, ""))     'name of your summary (remove the vbtabs and the spaces)

                    For i1 = 1 To 3                             'next 3 lines are the headers, join them !!!
                         sp = Split(a(i + i1), vbTab)           'split them on the vbtab
                         If i1 = 1 Then
                              Header = sp                       '1st time, just copy them
                         Else                                   'next lines = add to 1st line
                              For k = 0 To Application.Min(UBound(sp), UBound(Header))
                                   Header(k) = Trim(Header(k)) & vbLf & Trim(sp(k))
                              Next
                         End If
                    Next

                    ptr_r = 1                                   'write the 1st line of the array
                    ptr_k = Application.Max(4, ptr_k + 1)       'start writing this column of the array (min 4th column, leave an emptycolumn avery block
                    K_start = ptr_k                             'start of this block
                    For k1 = 2 To UBound(Header)                'only starting at the 3rd tab is relevant
                         result(1, ptr_k) = Header(k1)
                         ptr_k = ptr_k + 1
                    Next

     '************************************************************
     'it's not a summaryline, so check if it starts like a date (after a vbtab and a space)
     'replace all spaces
     'write to the correct column
     '****************************************************************
               Else
                    s = Mid(a(i), 3, 11)                        'starts with a vbtab and a space, so only 3rd character is the real start
                    If s Like "##-???-####" Then                'is it like a date
                         ptr_r = ptr_r + 1                      'next line
                         result(ptr_r, 1) = my_summary
                         result(ptr_r, 2) = "'" & s             'add a ', for remaining a string
                         sp = Split(Replace(a(i), " ", ""), vbTab)
                         For k = 2 To UBound(sp)                'starting at the 3rd part are the numbers
                              result(ptr_r, K_start - 2 + k) = sp(k)
                         Next
                    End If
               End If
          Next

     '************************************
     'write the array to the worksheet
     'number of rows and columns are the 2 pointers
     '************************************
          With .Range("A1")
               .CurrentRegion.ClearContents
               .Resize(ptr_r, ptr_k).Value = result
          End With
     End With

End Sub
 
Upvote 0
Solution
BSALV said:
you are asked to point a textfile, that 'll be opened, read and closed again.
As temporary measure, that is written in a sheet "blad1" but is unnecessary for the execution of the program.
then the rest is like this morning, but vbtab (ascii-9) is now the separator.

VBA Code: 
Option Compare Text
Sub GeeWiz()
     Dim ptr_r, ptr_k


     '************************
     'choose, open and read textfile*
     '************************
     With Application.FileDialog(msoFileDialogFilePicker)       'open dialog for choosing the right textfile
          .AllowMultiSelect = False
          .Title = UCase("Choose your logfile")
          With .Filters
               .Clear
               .Add "Text Files (*.txt)", "*.txt"
          End With

          If Not .Show() Then
               Exit Sub                                         'nothing choosen
          Else
               sfileName = .SelectedItems(1)                    'the textfile you choose

               fileNo = FreeFile                                'Get first free file number
               Open sfileName For Input As #fileNo              'open textfile
               a = Split(Input$(LOF(fileNo), fileNo), vbLf)     'read content and split on vblf = your whole txt-file is in memory (array a)
               Close #fileNo                                    'close textfile
               Sheets("blad1").Range("A1").Resize(UBound(a) + 1).Value = Application.Transpose(a)     'write to a sheet somewhere
          End If
     End With

     '**************************
     'manipulate data, chr(9) = vbtab
     '**************************
     Dim result(1 To 1000, 1 To 1000)                           'make an oversized array

     With Sheets("GeeWiz")                                      '--------->THIS IS THE WORKSHEET WHERE EVERYTHING IS WRITTEN TO

          For i = 0 To UBound(a)

     '***********************************************************************
     'the name of the summary (starts with a vbtab, ends with lots of spaces and a vbtab)
     'the next 3 lines, also separated by vbtab, have to be correctly joined to make the right headers
     '***********************************************************************
               If Left(a(i), 8) = vbTab & "summary" Then
                    my_summary = Trim(Replace(a(i), vbTab, ""))     'name of your summary (remove the vbtabs and the spaces)

                    For i1 = 1 To 3                             'next 3 lines are the headers, join them !!!
                         sp = Split(a(i + i1), vbTab)           'split them on the vbtab
                         If i1 = 1 Then
                              Header = sp                       '1st time, just copy them
                         Else                                   'next lines = add to 1st line
                              For k = 0 To Application.Min(UBound(sp), UBound(Header))
                                   Header(k) = Trim(Header(k)) & vbLf & Trim(sp(k))
                              Next
                         End If
                    Next

                    ptr_r = 1                                   'write the 1st line of the array
                    ptr_k = Application.Max(4, ptr_k + 1)       'start writing this column of the array (min 4th column, leave an emptycolumn avery block
                    K_start = ptr_k                             'start of this block
                    For k1 = 2 To UBound(Header)                'only starting at the 3rd tab is relevant
                         result(1, ptr_k) = Header(k1)
                         ptr_k = ptr_k + 1
                    Next

     '************************************************************
     'it's not a summaryline, so check if it starts like a date (after a vbtab and a space)
     'replace all spaces
     'write to the correct column
     '****************************************************************
               Else
                    s = Mid(a(i), 3, 11)                        'starts with a vbtab and a space, so only 3rd character is the real start
                    If s Like "##-???-####" Then                'is it like a date
                         ptr_r = ptr_r + 1                      'next line
                         result(ptr_r, 1) = my_summary
                         result(ptr_r, 2) = "'" & s             'add a ', for remaining a string
                         sp = Split(Replace(a(i), " ", ""), vbTab)
                         For k = 2 To UBound(sp)                'starting at the 3rd part are the numbers
                              result(ptr_r, K_start - 2 + k) = sp(k)
                         Next
                    End If
               End If
          Next

     '************************************
     'write the array to the worksheet
     'number of rows and columns are the 2 pointers
     '************************************
          With .Range("A1")
               .CurrentRegion.ClearContents
               .Resize(ptr_r, ptr_k).Value = result
          End With
     End With

End Sub
Click to expand...
Let me put it into excel and then reply. Thank you for the time you’ve spent.
 
Upvote 0
GeeWhiz7 said:
Let me put it into excel and then reply. Thank you for the time you’ve spent.
Click to expand...
Bsalv, the code works great. Thank you for taking time away from your important things to help me with mine!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
MAP / LAMBDA Functions to get multiple array elements
Replies
3
Views
558
Domenic
D
G
  • Solved
Hidden Spaces/Tabs in Cell
Replies
2
Views
662
GeeWhiz7
G
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
511
vampsthevampyre
V
R
  • Question Question
Help in converting a delimited data file (Tab) or as PDF file
Replies
0
Views
517
ronmill66
R
C
  • Question Question
How to Reduce Filtered Results in Pivot Table??
Replies
1
Views
990
clayjm
C

Forum statistics

Threads
1,223,716
Messages
6,174,069
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top