Cat On A Spreadsheet

Cat On A Spreadsheet

Parsing Logs with Regex in VBA

In the previous post, we parsed logs using simple string functions like Mid and Instr. However, regular expressions (regex) provide much more flexibility for pattern matching, validation, and extraction.

 

📂 Example: Extracting Timestamp, Log Level, and Message

 

Suppose your log lines look like this:

[2025-06-17 10:15:32] INFO: User JohnDoe logged in

 

The following script will extract:

 

  • Timestamp

  • Log level

  • Message

Sub ParseLogFileRegex()
Dim FilePath As String
Dim FileNum As Integer
Dim LineText As String
Dim RowNum As Long
Dim regEx As Object
Dim matches As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\[(.*?)\]\s+(\w+):\s+(.*)"
regEx.Global = False
regEx.IgnoreCase = True
FilePath = "C:\Logs\AppLog.txt"
FileNum = FreeFile
Open FilePath For Input As #FileNum
RowNum = 2
Do While Not EOF(FileNum)
     Line Input #FileNum, LineText
     Set matches = regEx.Execute(LineText)

     If matches.Count > 0 Then
          With ThisWorkbook.Sheets(1)
               .Cells(RowNum, 1).Value = matches(0).SubMatches(0) ' Timestamp
               .Cells(RowNum, 2).Value = matches(0).SubMatches(1) ' Log level
               .Cells(RowNum, 3).Value = matches(0).SubMatches(2) ' Message
          End With
          RowNum = RowNum + 1
     End If
Loop
Close #FileNum

Set regEx = Nothing
End Sub

 

🔍 Pattern Explanation

 

Pattern                                                  Matches

\[(.*?)\]                                                       Anything inside square brackets (timestamp)

\s+                                                              One or more spaces

(\w+)                                                          Log level (INFO, ERROR, etc.)

:                                                                   Literal colon

\s+                                                              One or more spaces

(.*)                                                              The entire message

 

🛠️ Other Useful Patterns

 

Extract email addresses

[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}

Extract numbers

\d+

Extract words inside quotes

"([^"]*)"

 

🔚 Final Thoughts

 

Regex-based parsing transforms your log file handling from brittle string splits to robust, pattern-driven data extraction. Combine this with dictionaries or advanced UDF libraries to build efficient parsers for any data source.

Sub ParseLine(ByVal LineText As String, ByVal RowNum As Long)
Dim TimeStamp As String
Dim LogLevel As String
Dim Message As String
Dim StartPos As Long
Dim EndPos As Long

' Extract timestamp (between [ and ])
StartPos = InStr(1, LineText, "[") + 1
EndPos = InStr(1, LineText, "]") - 1
TimeStamp = Mid(LineText, StartPos, EndPos - StartPos + 1)

' Extract log level (after ] and before :)
StartPos = InStr(1, LineText, "]") + 2
EndPos = InStr(1, LineText, ":") - 1
LogLevel = Mid(LineText, StartPos, EndPos - StartPos + 1)

' Extract message (after the colon)
Message = Mid(LineText, InStr(1, LineText, ":") + 2)

' Output to Excel
With ThisWorkbook.Sheets(1)
     .Cells(RowNum, 1).Value = TimeStamp
     .Cells(RowNum, 2).Value = LogLevel
     .Cells(RowNum, 3).Value = Message
End With
End Sub

 

🧼 Step 3: Setup Headers (Optional)

 

You might want to add headers for clarity:

With ThisWorkbook.Sheets(1)
     .Range("A1").Value = "Timestamp"
     .Range("B1").Value = "Log Level"
     .Range("C1").Value = "Message"
End With
End Sub

 

You can call this sub before running ParseLogFile.

 

⚠️ Bonus: Handling Irregular Lines

 

Some logs may have missing or malformed entries. Use error handling:

On Error Resume Next
' ... parsing logic ...
If Err.Number <> 0 Then
     Message = "PARSE ERROR: " & LineText
     Err.Clear
End If
On Error GoTo 0

 

🧠 Why Use VBA for This?

 

While tools like Python are more common for text processing, VBA offers:

  • Native integration with Excel

  • Immediate visualization and manipulation of parsed data

  • A great option for non-developers working in Excel-driven environments

 

🔚 Final Thoughts

 

VBA is more than just forms and macros—it’s a lightweight data processing engine built right into Excel. With a bit of string manipulation and file handling, you can turn chaotic log files into structured, actionable data in minutes.

 

Want to go further? Try:

  • Parsing multi-line log entries

  • Highlighting rows with ERROR or WARN

  • Exporting results as a CSV or Excel table

30 June 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet