Importing *.log records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to import IIS log records into Access 2003 and used them to
generate performance and activity reports and graphs for my customers. I have
already developed a series of reports, queries, and graphs, and now must
import these IIS log records into Access 2003.
These records are in a standard text format but have an extension of *.log.
If I change the extension on individual records from .log to .txt, I can
import them into Access and everything works fine. However, I need to import
an extremely large number of records and cannot do this using this method. I
found the following script that performs this function but do not know how
get it into an executable form. Any suggestions regarding how to do this will
be greatly appreciated. Also, if there is an alternative way of accomplishing
this,please let me know.
Thanks in advance,
Jim
--
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set FileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='c:\Scripts'} Where " _
& "ResultClass = CIM_DataFile")
For Each objFile In FileList
If objFile.Extension = "log" Then
strNewName = objFile.Drive & objFile.Path & _
objFile.FileName & "." & "txt"
errResult = objFile.Rename(strNewName)
Wscript.Echo errResult
End If
Next


Complete article including script can be found at:
http://www.microsoft.com/technet/scriptcenter/guide/sas_fil_ygwy.mspx
 
Hi, Jim.

It would be easier to treat .log files as if they were .txt files so that
the Text ISAM driver could be used. Add the .log extension to three Windows
Registry keys, and the file name change won't be necessary.

(Warning: Editing the Windows Registry can cause serious problems if not
done correctly, so make sure that you back up the Windows Registry and know
how to restore it _before_ attempting to alter it.)

Change the following Windows Registry keys to include log files (watch out
for word wrap):

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats\Text
Value Name: ImportFilter
Value Data: Text Files (*.txt;*.csv;*.tab;*.asc;*.log)

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
Value Name: DisabledExtensions
Value Data: !txt,csv,tab,asc,tmp,htm,html,log

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
Value Name: Extensions
Value Data: txt,csv,tab,asc,log

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Gunny,
Your solution worked perfectly with one exception. Each log file has a
header record generated by IIS. This header record includes information such
as the IIS release and version numbers plus the date and time stamp for the
logging period. Do you have any suggestions of how I can skip these header
records?
Again, the solution you provided to my original problem was outstanding, and
if necessary, I can manually delete the header records from each log file.
Thanks again.
Jim
 
Another option to using TransferText or the import wizard is to write your
own VBA code that opens each file, reads the data in line by line and
updates the table.

The basics of how you open a file and read it in line by line are:

Dim intFile As Integer
Dim strBuffer As String
Dim strFile As String
Dim strSQL As String

strFile = "C:\MyFile.log"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
' At this point, strBuffer contains a line from the file
' You can put in logic to ignore specific lines, or
' you can put in logic to parse the contents of strBuffer
' into the various fields for insertion into the table

If Left(strBuffer, 4) = "LOG " Then
' Ignore all lines that start with LOG
Else
strSQL = "INSERT INTO MyTable (Field1, Field2) " & _
"VALUES (" & Left(strBuffer, 5) & ", " & _
"'" & Mid(strBuffer, 10, 15) & "')"
CurrentDb.Execute strSQL, dbFailOnError
End If
Loop

Close intFile

In the example above, I'm ignoring all lines that start with the four
characters "LOG ". For all other lines, I'm assuming that the first 5
characters contain a numeric value that's supposed to be put in Field1 of
the table, and that positions 10 through 15 of the line contains a text
value that's supposed to be put into Field2 (Note that I've got single
quotes around the value I'm inserted into Field2, because it's text)

You have lots of options here: if it's a delimited file instead of fixed
position, you can use the Split function to break the input line into the
various component parts.
 
You might look at LogParser [1] which has a COM API and a command line interface (and can create charts or convert to tab seperated
values).

It might be a little heavier solution that hacking the registry to get .log files to look like .txt files (or rewriting the .log
file into a .txt files without the header), but the approach rocks and lends itself once mastered to many other log file analysis
tasks (depending on your reports and graphs, it might even generate them for you...).

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


[1] http://www.microsoft.com/downloads/...6b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
 
Back
Top