Importing W3C Web Server Log Files using Microsoft Excel Visual Basic for Applications
Excel is probably the most important commonly-available analysis tool for IT professionals. Visual Basic for Applications can be used to extend the functionality of Excel to support common performance and capacity management activities. This article explains an add-in that is used to import W3C web server log files for analysis using Excel. The add-in allows the user to select a file, which is then opened and automatically edited to ensure that unnecessary and repeated headers are removed.Installing the Add-in
- Click on w3c.zip to download the add-in
- Unzip and save 'Import W3C Web Logs.xla' into the appropriate add-ins directory (for example, C:\Program Files\Microsoft Office\Office\Library)
- Start Excel
- Select 'Add-Ins...' from the 'Tools' menu
- Use the 'Browse' button to locate the add-in 'Import W3C Web Logs.xla'
- Check the 'Import W3C Web Server Logs' add-in as shown below:
Select a File for Opening
To import a W3C web server log file, select 'Open W3C Web Server Log File' from the 'Capacitas' menu, as shown below:
The following code is used to request the name of the W3C web server log to be opened:
WebLogFileName = Application.GetOpenFilename(FileFilter:="W3C Web Server Logs (*.log), *.log", Title:="Capacitas")
This allows the user to select a W3C web server log file for opening as shown below:
Open the File
The following code ensures that if the user clicks the Cancel button then an error message is displayed, otherwise the chosen file is opened:
If WebLogFileName = False Then
ErrorMessage = "No W3C Web Server Log file was selected for opening"
GoTo ErrorHandler
Else
Workbooks.OpenText Filename:=WebLogFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, TrailingMinusNumbers:=True
End If
Check the File Format
A check is then conducted to ensure the first cell contains the expected value, meaning it is in the correct format. If not, then an error message is displayed and the log file is closed:
If ActiveSheet.Cells(1, 1) <> "#Software:" Then
ErrorMessage = "The selected log file is not in the expected format"
ActiveWorkbook.Close
GoTo ErrorHandler
End If
Remove Headers
Custom-written sub functions are used to remove unnecessary and repeated headers from the log file data:
Run RemoveRows("#Software:", True)
Run RemoveRows("#Version:", True)
Run RemoveRows("#Date:", True)
Run RemoveCells("#Fields:")
Run RemoveRows("date", False)
For example, the RemoveCells function deletes all cells containing the specified text:
Function RemoveCells(StringToRemove As String) As Variant
With ActiveSheet.Range("A:A")
Do
Set CellsToDelete = .Find(StringToRemove)
If Not CellsToDelete Is Nothing Then
CellsToDelete.Delete Shift:=xlToLeft
End If
Loop While Not CellsToDelete Is Nothing
End With
End Function
Example
The following screenshots show an example W3C web server log file that has been opened firstly using 'Open' from the 'File' menu, and then using the add-in:
W3C web server log file opened using 'Open' from the 'File' menu
W3C web server log file opened using the add-in
The forthcoming course Capacity and Performance Management using MS-Excel VBA contains further information on how to:
- Import and manipulate other data files
- Automate analysis and presentation of the imported data
- Add menu items
- Create add-ins
Access to Capacitas articles is unrestricted although research is restricted to registered users of this website; registration is free and available to all. Click here to sign up now. Subscribers will be informed via email when new research is published.