Cat On A Spreadsheet

Cat On A Spreadsheet

Parsing Complex Text and Log Files with VBA

Parsing plain text files—especially complex ones like server logs, CSVs with inconsistent formats, or exported data from legacy systems—is one of the most practical (and often overlooked) use cases for Excel VBA. In this post, we'll walk through a realistic scenario: extracting useful data from a messy application log file using VBA. You'll learn:

 

  • How to open and read text files line by line

  • How to use Split, Instr, and Mid for parsing

  • How to clean and structure the extracted data

  • How to handle edge cases and missing values

 

🧪 Case Study: Parsing an Application Log

 

Let’s say we have an AppLog.txt file with lines like these:

[2025-06-17 10:15:32] INFO: User JohnDoe logged in
[2025-06-17 10:15:35] ERROR: Unable to open config file
[2025-06-17 10:15:40] INFO: User JohnDoe started export
[2025-06-17 10:15:43] WARN: Low disk space

 

We want to extract and organize this into a table in Excel with:

 

  • Timestamp

  • Log level (INFO, ERROR, etc.)

  • Message

 

🧰 Step 1: Open and Read the File

Sub ParseLogFile()
Dim FilePath As String
Dim FileNum As Integer
Dim LineText As String
Dim RowNum As Long

FilePath = "C:\Logs\AppLog.txt"
FileNum = FreeFile
Open FilePath For Input As #FileNum
RowNum = 2 ' Start on row 2 to allow headers
Do While Not EOF(FileNum)
     Line Input #FileNum, LineText
     Call ParseLine(LineText, RowNum)
     RowNum = RowNum + 1
Loop
Close #FileNum
End Sub

 

🪓 Step 2: Parse Each Line into Pieces

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

23 June 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet