Search Log File for Multiple Conditions

Question

Thanks for any help in advance.

I have a large Log File that I need to extract specific information for each block (record).

In the Log File, triple percent signs (%%%) delineate each record block. Each record block has several lines of data.

Some of the information I need to query is on a single line (string) in the log file while other data are embedded in a single line string. I will include a sample log file so maybe this will be clearer.

I have several conditions that I need to search for each record block. For example:

    If Cond_Id:  = X-MODE1-999999I 
    And
    If Mode_Name: = Mode1_xx_ALA
    Then
    From the “Info:” string within that record block 
       I need to Return   
          the “Sub_Task” and “Com_Task” values for “Com10”

along with the following:

    Cond_Id
    Mode_Name
    Exec_time(xxx)
    Com10- Sub_Task
    Com10- Com_Task
    Some reference to a line number in the record block for "Cond_Id:" would be helpful

I need these data written to an Excel Sheet with header of the six (6) items above. There will be many instances produced by each search and I need to write all of them in the excel sheet.

With my limited programming knowledge, I am not sure if it would be best to read the log file into Excel or Load it into an array and do the queries. These will be rather large Log Files so I am not sure about the overhead and which method would be best.

I really need help with the logic for this task in excel vba.

So for I can read the log file into an array and write it to a excel sheet. Please see below:

    Sub getTextFile()

     Dim strPathFile As String
     Dim strScriptPath As String
    'Dim textFileName As String

    'GET FILE & path of Text File
     strPathFile = strOpenTextFilePath 
     strScriptPath = Left(strPathFile, InStrRev(strPathFile, "\"))

     frmLogFileAnalysis.lblLogFileSRR = strPathFile

     Dim txt As String, Arr, d, r, c, rv(), u, j

    'read in the entire file
     With CreateObject("scripting.filesystemobject").opentextfile(strPathFile)
       txt = .readall()
      .Close
    End With

      Arr = Split(txt, vbCrLf) 'split lines to an array

        For j = 1 To UBound(Arr)
            Worksheets("LogFile").Cells(j, 1).Value = Arr(j - 1)
        Next

  End Sub

Log File Example:

%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12345 xxx
2019 01 01 01 01 01.12345 xxx
2019 01 01 01 01 01.12345
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12345 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_Txx;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999I;
Link_Id:  9( 9);
Operation: ;
Msg_Name: Stat_Table D_Name: Status_1 From_P: W To_Prev: I Value =>  111111111 Limits => Value => 111111111
Imp cond: Info Id: 999; Execution_Time(xxx): 2019 01 01 01 01 01.12345; L_Id: 9( 9); Command_String: 00 00 00 00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 ; ;


%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12347 xxx
2019 01 01 01 01 01.12347 xxx
2019 01 01 01 01 01.12347
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12347 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_Txx;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999P;
Link_Id:  9( 9);
Operation: ;
Msg_Name: Stat_Table D_Name: Status_1 From_P: I To_Prev: P Value =>  111111111 Limits => Value => 111111111
Imp cond: Info Id: 999; Execution_Time(xxx): 2019 01 01 01 01 01.12347; L_Id: 9( 9); Command_String: 00 00 00 00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 ; ;


%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12349 xxx
2019 01 01 01 01 01.12349 xxx
2019 01 01 01 01 01.12349
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12349 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_Txx;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999W;
Link_Id:  9( 9);
Operation: ;
Msg_Name: Stat_Table D_Name: Status_1 From_P: P To_Prev: W Value =>  111111111 Limits => Value => 111111111
Imp cond: Info Id: 999; Execution_Time(xxx): 2019 01 01 01 01 01.12349; L_Id: 9( 9); Command_String: 00 00 00 00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 ; ;


%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12346 xxx
2019 01 01 01 01 01.12346 xxx
2019 01 01 01 01 01.12346
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12346 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_ALA;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999I;
Link_Id:  9( 9);
Operation: ;

Info: Sub_Task_table: { Com1: Sub_Task: TTTT; Com_Task: TTTT; ; Com2: Sub_Task: TTTT; Com_Task: TTTT; ;Com3: Sub_Task: TTTT; Com_Task: TTTT; ;Com4: Sub_Task: TTTT; Com_Task: TTTT; ;Com5: Sub_Task: TTTT; Com_Task: TTTT; ;Com6: Sub_Task: TTTT; Com_Task: TTTT; ;Com7: Sub_Task: TTTT; Com_Task: TTTT; ;Com8: Sub_Task: TTTT; Com_Task: TTTT; ;Com9: Sub_Task: TTTT; Com_Task: TTTT; ;Com10: Sub_Task: TTTT; Com_Task: TTTT; ;Com11: Sub_Task: TTTT; Com_Task: TTTT; ;Com12: Sub_Task: TTTT; Com_Task: TTTT; ;Com13: Sub_Task: TTTT; Com_Task: TTTT; ;Com14: Sub_Task: TTTT; Com_Task: TTTT; ;Com15: Sub_Task: TTTT; Com_Task: TTTT; ;Com16: Sub_Task: TTTT; Com_Task: TTTT; ;Com17: Sub_Task: TTTT; Com_Task: TTTT; ;Com18: Sub_Task: TTTT; Com_Task: TTTT; ;Com19: Sub_Task: TTTT; Com_Task: TTTT; ;Com20: Sub_Task: TTTT; Com_Task: TTTT; ;  }; ;


%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12348 xxx
2019 01 01 01 01 01.12348 xxx
2019 01 01 01 01 01.12348
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12348 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_ALA;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999P;
Link_Id:  9( 9);
Operation: ;

Info: Sub_Task_table: { Com1: Sub_Task: TTTT; Com_Task: TTTT; ; Com2: Sub_Task: TTTT; Com_Task: TTTT; ;Com3: Sub_Task: TTTT; Com_Task: TTTT; ;Com4: Sub_Task: TTTT; Com_Task: TTTT; ;Com5: Sub_Task: TTTT; Com_Task: TTTT; ;Com6: Sub_Task: TTTT; Com_Task: TTTT; ;Com7: Sub_Task: TTTT; Com_Task: TTTT; ;Com8: Sub_Task: TTTT; Com_Task: TTTT; ;Com9: Sub_Task: TTTT; Com_Task: TTTT; ;Com10: Sub_Task: NNNN; Com_Task: NNNN; ;Com11: Sub_Task: TTTT; Com_Task: TTTT; ;Com12: Sub_Task: TTTT; Com_Task: TTTT; ;Com13: Sub_Task: TTTT; Com_Task: TTTT; ;Com14: Sub_Task: TTTT; Com_Task: TTTT; ;Com15: Sub_Task: TTTT; Com_Task: TTTT; ;Com16: Sub_Task: TTTT; Com_Task: TTTT; ;Com17: Sub_Task: TTTT; Com_Task: TTTT; ;Com18: Sub_Task: TTTT; Com_Task: TTTT; ;Com19: Sub_Task: TTTT; Com_Task: TTTT; ;Com20: Sub_Task: TTTT; Com_Task: TTTT; ;  }; ;


%%%
xxx_xxx.xxx_xxx.Send.xxx_xxx.xxx_xxx
2019 01 01 01 01 01.12350 xxx
2019 01 01 01 01 01.12350 xxx
2019 01 01 01 01 01.12350
xxx_xxx_xxx
 1234
Mode1_xx_xxx
xxx 1
 id: 0; Exec_time(xxx): { 2019 01 01 01 01 01.12350 };
Category: XXX_XXX.XXX_MSG.ERROR;
xxx_xxxxxx_State: TEST;
Mode_Name: Mode1_xx_ALA;
Source: XXX_XXX.MODEM.XXXX.XXXX;
Condition_Id: X-MODE1-999999W;
Link_Id:  9( 9);
Operation: ;

Info: Sub_Task_table: { Com1: Sub_Task: TTTT; Com_Task: TTTT; ; Com2: Sub_Task: TTTT; Com_Task: TTTT; ;Com3: Sub_Task: TTTT; Com_Task: TTTT; ;Com4: Sub_Task: TTTT; Com_Task: TTTT; ;Com5: Sub_Task: TTTT; Com_Task: TTTT; ;Com6: Sub_Task: TTTT; Com_Task: TTTT; ;Com7: Sub_Task: TTTT; Com_Task: TTTT; ;Com8: Sub_Task: TTTT; Com_Task: TTTT; ;Com9: Sub_Task: TTTT; Com_Task: TTTT; ;Com10: Sub_Task: WWWW; Com_Task: WWWW; ;Com11: Sub_Task: TTTT; Com_Task: TTTT; ;Com12: Sub_Task: TTTT; Com_Task: TTTT; ;Com13: Sub_Task: TTTT; Com_Task: TTTT; ;Com14: Sub_Task: TTTT; Com_Task: TTTT; ;Com15: Sub_Task: TTTT; Com_Task: TTTT; ;Com16: Sub_Task: TTTT; Com_Task: TTTT; ;Com17: Sub_Task: TTTT; Com_Task: TTTT; ;Com18: Sub_Task: TTTT; Com_Task: TTTT; ;Com19: Sub_Task: TTTT; Com_Task: TTTT; ;Com20: Sub_Task: TTTT; Com_Task: TTTT; ;  }; ;

I recommend to have a look at Regular Expressions (Regex) which is pretty mighty to extract specific data out of raw data: [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Pᴇʜ, Feb 06 '19 at 16:02

score 0 · Answer 1 · answered Feb 07 '19 at 07:58

What size Log file? Rows? and Kb/Mb/Gb?

VBA above does Readall, then Split, then Loop/Cells to get it all into Excel

This new procedure Reads each line, and searches for the 5 requested fields, saving them to local variables. When "Info:" is reached it gets the Com10 data, and then writes a new row to the output. Whenever the Mode_Name or Cond_Id is incorrect, it then bypasses all rows until the next "%%%".

Sub doLogFile()
'    Get filename of input
    Dim fPathOfInput As String
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show = 0 Then
            fPathOfInput = ""
        Else
            fPathOfInput = .SelectedItems(1)
        End If
    End With
    If Right(fPathOfInput, 4) <> ".txt" _
    Or fPathOfInput = "" Then
        MsgBox ("No input txt file selected. fn=" & fPathOfInput)
        Exit Sub
    End If

'    Create new XLS with modified FN, with column heads
    Dim wbOutput As Workbook, wsOutput As Worksheet, fPathOfOutput As String, nRow As Long
    Const wsMySheetName As String = "FoundRows"
    Set wbOutput = Workbooks.Add
    wbOutput.ActiveSheet.Name = wsMySheetName
    Set wsOutput = wbOutput.ActiveSheet
    fPathOfOutput = Replace(fPathOfInput, ".txt", ".xlsx")
    wbOutput.SaveAs Filename:=fPathOfOutput
    nRow = 1
    wsOutput.Cells(nRow, 1) = "Cond_Id"
    wsOutput.Cells(nRow, 2) = "Mode_Name"
    wsOutput.Cells(nRow, 3) = "Exec_time"
    wsOutput.Cells(nRow, 4) = "Com10 -Sub_Task"
    wsOutput.Cells(nRow, 5) = "Com10 -Com_Task"
    wsOutput.Cells(nRow, 6) = "LineNumber"


'    Open file, Read Each Row, Process, Close file
    Dim textLine As String, cntLines As Long
    cntLines = 0
    Open fPathOfInput For Input As #1
    Do Until EOF(1)
        Line Input #1, textLine
        cntLines = cntLines + 1

'        Process each Row
        Dim sLookFor As String
        Dim posExecTime As Integer, saveExecTime As String
        Dim saveModeName As String
        Dim saveCondId As String, rowCondId As Long
        Dim posCom10 As Integer, posCom11 As Integer, txtCom10 As String, saveSubTask As String, saveComTask As String
        If Left(textLine, 3) = "%%%" Then
            sLookFor = "A"
            rowCondId = 0
            saveExecTime = "": saveModeName = "": saveCondId = "": saveSubTask = "": saveComTask = ""
        End If
        If sLookFor <> "X" Then

            posExecTime = InStr(textLine, "Exec_time")
            If posExecTime Then
                saveExecTime = Mid(textLine, posExecTime)
                GoTo gotoLoop
            End If

            If Mid(textLine, 1, 10) = "Mode_Name:" Then
                saveModeName = Mid(textLine, 12)
                'If saveModeName = "Mode1_xx_ALA" Then
                If Left(saveModeName, 6) = "Mode1_" And Right(saveModeName, 5) = "_ALA;" Then
                Else
                    sLookFor = "X"
                End If
                GoTo gotoLoop
            End If

            If Mid(textLine, 1, 13) = "Condition_Id:" Then
                saveCondId = Mid(textLine, 15)
                If saveCondId = "X-MODE1-999999I;" Then
                'If Left(saveCondId, 6) = "zzzz" And Right(saveCondId, 4) = "zzz" Then
                    rowCondId = cntLines
                Else
                    sLookFor = "X"
                End If
                GoTo gotoLoop
            End If

            If Mid(textLine, 1, 5) = "Info:" Then
'                Find Com10
                posCom10 = InStr(textLine, "Com10:")
                posCom11 = InStr(textLine, "Com11:")
                Dim posSubTask As Integer, posComTask As Integer, posSemi As Integer
                If posCom10 And posCom11 Then
                    txtCom10 = Mid(textLine, posCom10 + 7, posCom11 - posCom10 - 7)

                    ' got --  Sub_Task: NNNN; Com_Task: NNNN;;
                    posSubTask = InStr(txtCom10, "Sub_Task:")
                    posSemi = InStr(txtCom10, ";")
                    saveSubTask = Mid(txtCom10, posSubTask + 10, posSemi - posSubTask - 10)

                    posComTask = InStr(posSemi, txtCom10, "Com_Task:")
                    posSemi = InStr(posSemi + 1, txtCom10, ";")
                    saveComTask = Mid(txtCom10, posComTask + 10, posSemi - posComTask - 10)

'                    Write Excel row
                    nRow = nRow + 1
                    wsOutput.Cells(nRow, 1) = saveCondId
                    wsOutput.Cells(nRow, 2) = saveModeName
                    wsOutput.Cells(nRow, 3) = saveExecTime
                    wsOutput.Cells(nRow, 4) = saveSubTask
                    wsOutput.Cells(nRow, 5) = saveComTask
                    wsOutput.Cells(nRow, 6) = rowCondId


                End If
'                Turn off scan
                sLookFor = "X"
                GoTo gotoLoop
            End If


        End If

gotoLoop:
    Loop
    Close #1
                    nRow = nRow + 1
                    wsOutput.Cells(nRow, 1) = "Total Rows in text file"
                    wsOutput.Cells(nRow, 6) = cntLines

End Sub

@Willie Does this solve your problem? If so, please click on the check mark. — donPablo, Feb 09 '19 at 16:52

Search Log File for Multiple Conditions

1 Answers1