excelvba

Look into specific information in txt file and paste it into Excel


I am working in Excel, to look into a txt file for certain information and then paste it into the worksheet.

I want to copy information from different lines that contain different defined strings.

Example:
txt file is a like a logbook. This logbook has entrances like |UPDATED|, |CREATED| and |UPDATED|. After this is is logged, then a variable number of lines will follow, until the "topic" is closed with the symbol "->".
I want the script to, look for the string |UPDATED|, copy the whole line and paste into Excel. Then keep scrolling through the txt file lines, until "->" is found, copy that line and put it right under the correspondent "title" (UPDATED, CREATED, UPDATED).

If there was a way to also record the line it found the entrance into a variable, then I could do, for example const= const - 2, to go up in txt file for two lines and also copy that content!

Sub PS_automation()

Dim ReadData As String
Dim x As Integer, y As Integer, z As Integer, p As Integer
x = 1
y = 1
z = 1
p = 1

Open "C:\Users\racastr1\Desktop\PS_AUTOMATE.txt" For Input As #1 
    
Do Until EOF(1)
    
    Line Input #1, ReadData 
    
    If InStr(Left(ReadData, 9), "|CREATED|") Then
        Cells(x, 1) = ReadData
        x = x + 1
    Else
        If InStr(Left(ReadData, 8), "|CLOSED|") Then
            Cells(y, 2) = ReadData
            y = y + 1
        Else
            If InStr(Left(ReadData, 9), "|UPDATED|") Then
                Cells(z, 3) = ReadData
                z = z + 1
            End If
        End If
    End If
Loop
Close #1
End Sub

This already copies the mentioned entrances, but if I had the same condition to find "->" it does not paste it into the worksheet in order (so it does not get into the associated "title").

File Example:
File Example


Solution

  • You could try something like this (changed my approach from the initial answer)

    Sub PS_automation()
        
        Const TXT_PATH As String = "C:\Users\racastr1\Desktop\PS_AUTOMATE.txt"
        
        Dim data As String, arr, arrCols, i As Long, s As String, col As Long
        Dim ws As Worksheet, n As Long, v, sPrev As String
        
        data = GetContent(TXT_PATH)        'read the whole file
        data = Replace(data, vbcrlf, vbLf) 'normalize line separators
        arr = Split(data, vbLf)            'split to array
        
        'array of label+colNumber pairs
        arrCols = Array("|CREATED|", 1, "|CLOSED|", 2, "|UPDATED|", 3)
        Set ws = ActiveSheet 'or some specific sheet
        
        For i = 0 To UBound(arr) 'loop over the lines from the file
            s = arr(i)   
            'Loop over `arrCols` and check for pre-defined label....
            For n = LBound(arrCols) To UBound(arrCols) Step 2
                v = arrCols(n)            'label
                If Left(s, Len(v)) = v Then  'match?
                    col = arrCols(n + 1)  'corresponding column number
                    If i > 0 Then 'go back up and check for date?
                        sPrev = arr(i - 1) 'the line above the match
                        If sPrev Like "####-##-## *" Then 'starts with date?
                            'write line with date to sheet if found
                            ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = sPrev
                        End If
                    End If
                    ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = s
                    GoTo skip 'done with this line
                End If
            Next n
            
            If Left(s, 2) = "->" Then
                If col > 0 Then
                    ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = s
                End If
            End If
    skip:
        Next i
    End Sub
    
    
    Function GetContent(f As String) As String
        GetContent = CreateObject("scripting.filesystemobject"). _
                      OpenTextFile(f, 1).ReadAll()
    End Function