Return multiple Text File lines between 2 specific locations with VBA

user_10176484 0
Viewed 61

I have a backup text file I need to sort out into usable information in excel. I know what the text I'm looking for is, but where it is in the text file will vary. I know how to get the file open and how to copy the whole thing, but since the data copied will be different lengths, I need it sorted into columns for other formulas to look at.

  1. "/Job" and all lines until "/Pulse" to B1-B8
  2. "//INST" to "NOP" to B9 (although I can make this its own column if need be)
  3. all lines starting with C0 to column L
  4. all lines starting with EC0 to column S
  5. all lines following "*_Start" until EOF to column W

I can figure out how to search for specific single lines, but including lines under it UNTIL a certain point is proving over my head.

This is the code I've been using to get whole files from user-selected folders:

Dim myFile As String, textline As String
    Application.DisplayAlerts = False
    myFile = ThisWorkbook.Sheets("Data").Range("c1").Value
    Dim i As Long
        Open myFile For Input As #1
        Do Until EOF(1)
            i = i + 1
            Line Input #1, textline
            ThisWorkbook.Sheets("Temp IO").Range("F" & i).Value = textline
        Loop
        Close #1

The backup file will look like this (I've changed values so I'm not uploading anything identifyable)

/JOB
//NAME 1210
//POS
///NPOS 74,0,74,0,0,0
///TOOL 0
///POSTYPE PULSE
///PULSE
C00000=65298,-31463,-18169,-24773,-119176,56781
C00001=65298,-31463,-18169,-24773,-119176,56781
C00002=65298,-45193,-17067,0,-119176,0
C00003=17144,11040,29421,-1614,-103932,438
C00004=18413,40212,49835,-5253,-77713,5101
C00005=20415,59082,68960,-2441,-91039,4411
C00006=20826,60074,63714,-1626,-91500,4377
EC00000=0,0
EC00001=0,0
EC00002=0,0
EC00003=0,0
EC00004=0,0
EC00005=0,0
EC00006=0,0
//INST
///DATE 2022/03/14 12:06
///COMM Name
///ATTR SC,RW,VJ
///GROUP1 R1
///GROUP2 S1
///LVARS 0,3,5,0,0,0,0,0
NOP
*_START
JOINT   C00000          0            0              100     +JOINT          EC00000                                     
JOINT   C00001          9            0                  102 +JOINT          EC00001                                     
JOINT   C00002          9            0                      +JOINT          EC00002                                     
JOINT   C00003          9            0                      +JOINT          EC00003                                     
JOINT   C00004          9            0              133     +JOINT          EC00004                                     
JOINT   C00005          9            0                      +JOINT          EC00005                                     
JOINT   C00006          9            0  ARCON   99          162 +JOINT          EC00006                                     
SLENEAR C00007                       0  ARCOF   97          162 +JOINT          EC00007                                     
JOINT   C00008          9            0                      +JOINT          EC00008                                     
JOINT   C00009          9            0                      +JOINT          EC00009                                     
JOINT   C00010          9            0                      +JOINT          EC00010                                     
JOINT   C00011          9            0                      +JOINT          EC00011                                     
JOINT   C00012          9            0                      +JOINT          EC00012                                     
JOINT   C00013          9            0  ARCON   99          162 +JOINT          EC00013                                     
SLENEAR C00014                       0  ARCOF   97  
END
vba excel
1 Answers

Regex will definitely give you what you want. If you haven't got the time to learn the dark arts of regex, then with a small compromise (and files consistent with the one you've provided as an example) you could probably get what you want with the following code:

Dim myFile As String, textline As String

Application.DisplayAlerts = False
myFile = ThisWorkbook.Sheets("Data").Range("c1").Value

Dim LastRow As Long
Dim parser As String, parser_segment As Variant, lookfor As String, outputcolumn As String, col As String

parser = "/Job¦B|C0¦L|//INST¦B|EC0¦S|*_START¦W"

Open myFile For Input As #1
outputcolumn = ""
Do Until EOF(1)

    Line Input #1, textline
    
    For Each parser_segment In Split(parser, "|")
        lookfor = Split(parser_segment, "¦")(0)
        col = Split(parser_segment, "¦")(1)
        If UCase(Left(textline, Len(lookfor))) = UCase(lookfor) Then outputcolumn = col
    Next
    If outputcolumn <> "" Then
        With ThisWorkbook.Sheets("Temp IO")
            LastRow = .Cells(.Rows.Count, outputcolumn).End(xlUp).Row + 1
            .Range(outputcolumn & LastRow).Value = textline
        End With
    End If
    
Loop
Close #1

The 'secret sauce' is the parser code. This could've been done with a bunch of If..Then.. statements, but to keep things tidier I went with this. Each segment is made of a search string (which the incoming line must start with) and a column to write to. As each line is ready by your text file reader, it runs it past those segments to see if the outputcolumn needs to change. Once it's run through those options, it then finds the next usable cell in the outputcolumn and deposits the output there. This continues until it reaches the end of the file.

Hope this gets you some way to your result.

user_74467600
Related