Importing W3C Web Server Log Files using Visual Basic

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