VB File Help Please

D

Dillirium

Limp Gawd
Joined
Sep 16, 2004
Messages
439
Ok here's what's going on. I'm reading in some log files to input into an access database. Yes i know access :( but still it's a temporary storage area so i can print reports and then delete all this information. These log files come from imail and looks like this

client, -, -, N, 05/04/2005, 00:00:43, 1, -, -, 203.135.3.142, 203.135.3.142, 80, 101, 240, 265, http, -, POST, http://203.135.3.142:80/scripts/open.dll?, -, Unknown, 200

Now what i'm doing is reading in like 5 records from this monster of a line :p I crab t he client skip the next 4 commas, i grab the date, time , scip 4 commas and i grab the Destination name (ip address or name) and then I grab the destination IP. you might notice both are ip addresses. THis is because it couldn't resolve the destination name so just uses the ip.

Everytime I encounter a comma i increment my intCommas. So i know which field i'm at etc...

Ready for the problem? For some stupid reason I can't open up the bloody file. I know the string is receiving data. The string I try to open the file w/ has
c:\WINDOWS\Desktop\Projects\Log Importer2005-05-01-WinProxy_Access.log
this path is correct.

This is the open statement:
Open filename For Random As #1 Len = 1

I try to read in 1 byte at a time and do w/ the character as I need. This is where i'm stuck. I'll post the rest of the code in my next post. Anyone have any ideas or do you need additional info?
 
NOTE: Please keep in the mind that this code isn't finished: (obviously) If you have any suggestions please state your opinion thank you!

_______________________________________________________________________
Public Function ParseLogFile()
'declare db
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


'filename recieved from the list box on frmLogList
Dim filename As String
'counters below
Dim x As Double
Dim y As Double 'this variable is important. counts/tracks which byte to read
Dim intCommas As Integer
'string to parse data from
Dim strChar As String * 1
Dim charVal As Integer
Dim strLine As String
'Below are the variables to input data into the database
Dim strClient As String
Dim strDate As String
Dim strTime As String
Dim strName As String
Dim strDIP As String

'sets the connection
Set cn = New ADODB.Connection
Set rs = New Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINDOWS\Desktop\Projects\Log Importer\weblog.mdb;Mode=ReadWrite;Persist Security Info=False"
cn.Open

With rs 'what the recordset should contain and other misc properties
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open "weblog", cn, , , adCmdTable
End With


x = 0 'initialize counters - x is counter for list of files
y = 0 'byte counter
charVal = 0 'looks for carriage return or end of line

Do While x <= frmLogList.lstFiles.ListCount - 1 'loop while there are more files to read in

filename = Trim(frmLogList.lstFiles.List(x)) 'rid of extra spaces

Open filename For Random As #1 Len = 1

Do While Not EOF(1) 'loop through 1 byte at a time
Get #1, y + 1, strChar
charVal = Asc(strChar)

If strChar <> "," And intCommas = 0 And charVal <> 13 And charVal <> 10 Then
strClient = strClient & strChar
ElseIf strChar <> "," And intCommas = 4 And charVal <> 13 And charVal <> 10 Then
strDate = strDate & strChar
ElseIf strChar <> "," And intCommas = 5 And charVal <> 13 And charVal <> 10 Then
strTime = strTime & strChar
ElseIf strChar <> "," And intCommas = 9 And charVal <> 13 And charVal <> 10 Then
strName = strName & strChar
ElseIf strChar <> "," And intCommas = 10 And charVal <> 13 And charVal <> 10 Then
strDIP = strDIP & strChar
ElseIf strChar = "," Then
intCommas = intCommas + 1
ElseIf charVal = 13 Or charVal = 10 Then
'upload all variables and reset variables
'upload
rs.AddNew
rs.Fields("client") = Trim(strClient)
rs.Fields("date") = Trim(strDate)
rs.Fields("time") = Trim(strTime)
rs.Fields("d_name") = Trim(strName)
rs.Fields("d_ip") = Trim(strDIP)
rs.Update

'reset
strClient = ""
strDate = ""
strTime = ""
strName = ""
strDIP = ""
intCommas = 0
End If
y = y + 1 'increment y to read the next byte

Loop

Close #1

x = x + 1 'increment x to keep track of what log file we're on
y = 0 'reset counter for next file
Loop

MsgBox "Operation Complete", vbOKOnly, "Successful"
rs.Close
cn.Close
Unload frmProcessing
Unload frmLogList
End Function
 
I actually fixed all my problems except 1. I don't know if anyone viewing this post uses imail or is interested in this but... It works.. however.. to process like 350megs of log files.. would take an estimated time of 4hrs. This is running on a 1ghz machine w/ 256 megs of ram on *drum roll please*. WIndows 98. Anyone have any advice on how to shorten the seek/write time of this file/databse transaction? here's the code below. I'll prolly refine it next monday so please post ideas/opinions. FYI: this is my first actual program I've wrote :p
_______________________________________________________________________
Public Function ParseLogFile()
'declare db
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'filename recieved from the list box on frmLogList
Dim filename As String

'counters below
Dim x As Double
Dim y As Double 'this variable is important. counts/tracks which byte to read
Dim intCommas As Integer

'string to parse data from
Dim strChar As String * 1
Dim charVal As Integer

'Below are the variables to input data into the database
Dim strClient As String
Dim strDate As String
Dim strTime As String
Dim strName As String
Dim strDIP As String

'sets the connection
Set cn = New ADODB.Connection
Set rs = New Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINDOWS\Desktop\Projects\Log Importer\weblog.mdb;Mode=ReadWrite;Persist Security Info=False"
cn.Open

With rs 'what the recordset should contain and other misc properties
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open "weblog", cn, , , adCmdTable
End With

'Initialize all variables
x = 0 'initialize counters - x is counter for list of files
y = 0 'byte counter
charVal = 0 'looks for carriage return or end of line
strClient = ""
strDate = ""
strTime = ""
strName = ""
strDIP = ""
filename = ""



Do While x <= frmLogList.lstFiles.ListCount - 1 'loop while there are more files to read in
Screen.ActiveForm.Refresh
filename = frmLogList.lstFiles.List(x) 'rid of extra spaces
filename = LTrim(filename)
filename = RTrim(filename)

Open filename For Random As #1 Len = 1

Do While Not EOF(1) 'loop through 1 byte at a time
Get #1, y + 1, strChar
charVal = Asc(strChar)

If strChar <> "," And intCommas = 0 And charVal <> 13 And charVal <> 10 Then
strClient = strClient & strChar
ElseIf strChar <> "," And intCommas = 4 And charVal <> 13 And charVal <> 10 Then
strDate = strDate & strChar
ElseIf strChar <> "," And intCommas = 5 And charVal <> 13 And charVal <> 10 Then
strTime = strTime & strChar
ElseIf strChar <> "," And intCommas = 9 And charVal <> 13 And charVal <> 10 Then
strName = strName & strChar
ElseIf strChar <> "," And intCommas = 10 And charVal <> 13 And charVal <> 10 Then
strDIP = strDIP & strChar
ElseIf strChar = "," Then
intCommas = intCommas + 1
If intCommas = 11 Then
'format/upload all variables and reset variables
strClient = Trim(strClient)
strDate = Trim(strDate)
strTime = Trim(strTime)
strName = Trim(strName)
strDIP = Trim(strDIP)

'upload
rs.AddNew
rs.Fields("client") = strClient
rs.Fields("date") = strDate
rs.Fields("time") = strTime
rs.Fields("d_name") = strName
rs.Fields("d_ip") = strDIP
rs.Update

'reset
strClient = ""
strDate = ""
strTime = ""
strName = ""
strDIP = ""
End If
ElseIf charVal = 13 Or charVal = 10 Then
intCommas = 0
End If
y = y + 1 'increment y to read the next byte
strChar = ""

Loop

Close #1

x = x + 1 'increment x to keep track of what log file we're on
y = 0 'reset counter for next file
Loop

MsgBox "Operation Complete", vbOKOnly, "Successful"
rs.Close
cn.Close
Unload frmProcessing
Unload frmLogList
End Function
 
Too much file I/O. Why not read in the log file all at once, or at least a line at a time?
 
You must log in or register to reply here.
Back
Top