Textfile-->Parse-->Table

[Thomas Yurick]

Hi all,

I have been away from Access for a while and need some quick help. I have a
textfile that I am opening and using string functions to parse. After I have
assigned the parsed strings to variables, how can I insert them into tables?
Here is an example of code, with the results being printed to the Immediate
Window, instead of being inserted into a table.

Thanks,
Tom

Private Sub cmdImportSysLog_Click()
'Dimension variables
Dim SyslogTextLine, AuthenSuccess, AuthenFail, AuthenScrchString,
FailedScrchString As String
Dim AuthenDate, AuthenGroup, AuthenUser, FailDate, FailIP, FailUser As
String
Dim GroupStart, GroupEnd, UserStart, UserEnd As Variant
Dim RPTStart, IPStart, IPEnd, FailUserStart, FailUserEnd As Variant
AuthenScrchString = "authenticated."
FailedScrchString = "Authentication failed:"
Open "D:\Work\SyslogCatchAll.txt" For Input As #1 ' Open file.
StartParseSuccess:
Do While Not EOF(1) ' Loop until end of file.

Line Input #1, SyslogTextLine ' Read line into variable.
AuthenSuccess = InStr(1, SyslogTextLine, AuthenScrchString, 1) 'Find Key
string.
If AuthenSuccess = 0 Then GoTo StartParseSuccess Else:
'PARSE
AuthenDate = Mid$(SyslogTextLine, 52, 23)
GroupStart = InStr(75, SyslogTextLine, "[", 1)
GroupEnd = InStr(75, SyslogTextLine, "]", 1)
AuthenGroup = Mid$(SyslogTextLine, GroupStart, (GroupEnd - GroupStart + 1))
UserStart = InStr((GroupEnd - GroupStart + 2), SyslogTextLine, "(", 1)
UserEnd = InStr((GroupEnd - GroupStart + 2), SyslogTextLine, ")", 1)
AuthenUser = Mid$(SyslogTextLine, (UserStart + 1), (UserEnd - UserStart -
1))
Debug.Print AuthenDate & " " & AuthenGroup & " " & AuthenUser 'Check line
and print if valid.
Loop
Close #1 ' Close file.

 

[John Nurick]

Hi Thomas,

One way is with something like this:

Dim rsR as DAO.Recordset

Set rsR = DBEngine(0)(0).Openrecordset("MyTable")

...

'inside loop
With rsR
.AddNew
.Fields("Authendate").Value = CDate(Authendate)
...
.Update
End With

...
rsR.Close

Hi all,

I have been away from Access for a while and need some quick help. I have a
textfile that I am opening and using string functions to parse. After I have
assigned the parsed strings to variables, how can I insert them into tables?
Here is an example of code, with the results being printed to the Immediate
Window, instead of being inserted into a table.

Thanks,
Tom

Private Sub cmdImportSysLog_Click()
'Dimension variables
Dim SyslogTextLine, AuthenSuccess, AuthenFail, AuthenScrchString,
FailedScrchString As String
Dim AuthenDate, AuthenGroup, AuthenUser, FailDate, FailIP, FailUser As
String
Dim GroupStart, GroupEnd, UserStart, UserEnd As Variant
Dim RPTStart, IPStart, IPEnd, FailUserStart, FailUserEnd As Variant
AuthenScrchString = "authenticated."
FailedScrchString = "Authentication failed:"
Open "D:\Work\SyslogCatchAll.txt" For Input As #1 ' Open file.
StartParseSuccess:
Do While Not EOF(1) ' Loop until end of file.

Line Input #1, SyslogTextLine ' Read line into variable.
AuthenSuccess = InStr(1, SyslogTextLine, AuthenScrchString, 1) 'Find Key
string.
If AuthenSuccess = 0 Then GoTo StartParseSuccess Else:
'PARSE
AuthenDate = Mid$(SyslogTextLine, 52, 23)
GroupStart = InStr(75, SyslogTextLine, "[", 1)
GroupEnd = InStr(75, SyslogTextLine, "]", 1)
AuthenGroup = Mid$(SyslogTextLine, GroupStart, (GroupEnd - GroupStart + 1))
UserStart = InStr((GroupEnd - GroupStart + 2), SyslogTextLine, "(", 1)
UserEnd = InStr((GroupEnd - GroupStart + 2), SyslogTextLine, ")", 1)
AuthenUser = Mid$(SyslogTextLine, (UserStart + 1), (UserEnd - UserStart -
1))
Debug.Print AuthenDate & " " & AuthenGroup & " " & AuthenUser 'Check line
and print if valid.
Loop
Close #1 ' Close file.

 

[Thomas Yurick]

Hi John,

This is exactly what I needed. I had looked at some old code and was zeroing
in when you replied. Thank you very much!

Tom