importing IIS Logs into Access 2003

[Jim Hank]

I host several websites using IIS 6 and would like to import IIS logs into
Access 2003. The format of these IIS W3C Extended Log Files are shown below.
These first four records (rows) in each file contains header info and the
remainder of each file is the actual log records. The following is a sample
of a W3C Extended log file:

#Software: Microsoft Internet Information Services 5.0
#Version: 1.0
#Date: 2006-02-22 15:25:57
#Fields: date time c-ip cs-username s-ip s-port cs-method cs-uri-stem
cs-uri-query sc-status sc-bytes cs-bytes cs(User-Agent)
2006-02-22 15:25:57 68.142.251.178 - 66.155.40.65 80 GET /robots.txt - 404
4184 193
Mozilla/5.0+(compatible;+Yahoo!+Slurp;+http://help.yahoo.com/help/us/ysearch/slurp)
2006-02-22 15:25:57 68.142.249.84 - 66.155.40.65 80 GET
/jsb-dtp/ProductServiceDescriptions/Catalog+Product+Description.pdf - 304 200
303
Mozilla/5.0+(compatible;+Yahoo!+Slurp;+http://help.yahoo.com/help/us/ysearch/slurp)
2006-02-22 15:26:16 68.142.250.121 - 66.155.40.65 80 GET
/jsb-dtp/InvitationExamples.htm - 304 200 263
Mozilla/5.0+(compatible;+Yahoo!+Slurp;+http://help.yahoo.com/help/us/ysearch/slurp)

As you can see, the first four records in each log file contain header
information beginning with the number sign ("#"). I would like to skip all of
the first three rows and the first field of the fourth row. The remainder of
the fourth row (after the # fields is actually the header record that
contains all of the Field Names for all the remaining records in the log
field.
I am open to all suggestions but given the number of log files that I will
be analyzing, I would like to come up with some automated process for
importing these log files into Access. After this information is imported
into Access 2003, I will create queries analyze this information and generate
monthly website performance reports that can be distributed to my customers.
I will also archive this data on an SQL server, and use it to create
quarterly and annual performance reports.
Any suggestion will be most appreciated.
Jim

 

[John Nurick]

Hi Jim,

There's a function here http://www.j.nurick.dial.pipex.com/Code/index.htm
that will strip header lines out of a text file. If you remove all the
header lines it looks as if you'll be left with a space-delimited file which
TransferText will be able to import to an existing table (once you've
created an import specification to suit).

It would be easy to modify it to remove the "#Fields: " from the fourth
line, lea

 

[Jim Hank]

John,
Based upon your description, this appears to be exactly what I need. I will
give it a try.
Thanks,
Jim