I am back with another issue.
I have an ACCESS DB which used to import a process log (.log extension). The VBA code was fine until now but there was few new field names and values are added in the process log file and now the code is not importing all the records to the database table.
I have attached a sample of the log file. To attach in this post I have changed the file extension to .txt. Normally it is .log file. Earlier the log has entries upto the the below line.
NUM REGPOINTS = 4
TEST1 (Unknown)
TEST2 (2014-06-11 10:20:00) ETO
TEST3 (2014-06-11 10:28:56) ETO
TEST4 (2014-06-11 10:31:44) ETO.
And the new process starts with a first line similar to below line
2014-06-11 10:20:18 PROS_TST: New System LOG changed:
But now some process has additions like below two lines after the NUM REGPOINTS and then the first line starts.
ORIGINAL REGS = TEST1 (Unknown)
ORIGINAL REGX = TEST4 (2014-06-11 10:31:44) ETO
Some entries may have NUM REGPOINTS=0 and then the new fields appear like below. then the first line of another process entry will be available
ORIGINAL REGS =
ORIGINAL REGX =
2014-06-11 10:22:35 PROS_TST: New System LOG changed:
In that case also code works.
I doubt about the "Buf=10" mentioned in the code stops importing anything when NUM REGPOINTS= any other value than 0 (because that time there is no = sign for the test1, test2, test3 records
Hope I have explained my issue.
Please help me to make the code import the new log file.
The code as follows:
Expand|Select|Wrap|Line Numbers
- Function ImportLOG(AddPth)
- On Error GoTo ErrRtn
- Dim hfile As Integer
- Dim MainRs As Recordset
- Dim fldName As String
- Dim fldValue As String
- Dim txtLine As String
- Dim DOF As String
- Dim DateGroup As String
- Dim FirstLine As Boolean
- Dim i As Integer
- Dim TempUIDs As String
- Dim TempAtTime As String
- Dim TempByWhom As String
- Dim TempWhat As String
- Dim Buf As Byte
- Dim CharNo As Long
- Dim NoOfChrs As Long
- Dim x As Variant
- DoCmd.Hourglass True
- DOF = Format(Forms!startpage!txtImDate, "ddmmmyyyy")
- DateGroup = Format(Forms!startpage!txtImDate, "yyyymm_dd")
- hfile = FreeFile()
- Set MainRs = CurrentDb.OpenRecordset("LogTbl", dbOpenDynaset, dbAppendOnly)
- Open LogPath & AddPth & "logs_" & DateGroup & ".log" For Binary As hfile '
- NoOfChrs = LOF(hfile)
- x = Now()
- Do While Not EOF(hfile)
- DoEvents
- Forms!startpage!lblProcess.Caption = "Processing ... " & Format(CharNo / NoOfChrs * 100, "00.00") & "% - Character " & CharNo & " of " & NoOfChrs & " (uidq = " & TempUIDs & ")"
- Forms!startpage.Repaint
- InputUnixLine:
- txtLine = ""
- Do
- NextChr:
- Get #hfile, , Buf
- CharNo = CharNo + 1
- If Buf = 13 Then GoTo NextChr
- If Buf = 10 Then Exit Do
- If EOF(hfile) Then GoTo SkipProc
- txtLine = txtLine & Chr$(Buf)
- Loop
- If txtLine = "" Then
- If MainRs.EditMode = dbEditNone Then
- MainRs.AddNew
- Else
- MainRs!LogDate = Forms!startpage!txtImDate
- MainRs.Update
- End If
- GoTo InputUnixLine 'jump to read next line
- End If
- If Val(Left(txtLine, 4)) = Year(Now()) Or Val(Left(txtLine, 4)) = Year(Now()) - 1 Then FirstLine = True
- If FirstLine Then
- If Trim(Mid(txtLine, InStr(21, txtLine, ":") + 1)) = "" Then
- FirstLine = False
- GoTo InputUnixLine 'jump to read next line
- End If
- MainRs.AddNew
- MainRs!AtTime = Left(txtLine, 19)
- MainRs!ByWhom = Trim(Mid(txtLine, 21, InStr(21, txtLine, ":") - 21))
- MainRs!What = Trim(Mid(txtLine, InStr(21, txtLine, ":") + 1))
- TempAtTime = Left(txtLine, 19)
- TempByWhom = Trim(Mid(txtLine, 21, InStr(21, txtLine, ":") - 21))
- TempWhat = Trim(Mid(txtLine, InStr(21, txtLine, ":") + 1))
- FirstLine = False
- GoTo InputUnixLine 'jump to read next line
- End If
- If InStr(txtLine, "=") = 0 Then
- If MainRs.EditMode = dbEditNone Then
- MainRs.AddNew
- End If
- MainRs!REG_DETAIL = Trim(txtLine)
- MainRs!UIDS = TempUIDs
- MainRs!AtTime = TempAtTime
- MainRs!ByWhom = TempByWhom
- MainRs!What = TempWhat
- MainRs!LogDate = Forms!startpage!txtImDate
- MainRs.Update
- GoTo InputUnixLine
- Else
- fldName = Trim(Left(txtLine, InStr(txtLine, "=") - 1))
- fldValue = Trim(Mid(txtLine, InStr(txtLine, "=") + 1))
- End If
- Select Case fldName
- Case "ARRIVAL TIME"
- MainRs!ATIME = fldValue
- Case "EST. TIME OF ENTRY"
- MainRs!ETIME = fldValue
- Case "COORDINATED EXIT TIME"
- MainRs!CTIME = fldValue
- Case "PREVIOUS REG"
- MainRs!REG = fldValue
- Case "NEXT REG"
- MainRs!NREG = fldValue
- Case "NUM REGPOINTS"
- MainRs!NUM_REG = fldValue
- Case "ORIGINAL REGS"
- MainRs!ORG_REG = fldValue
- Case "ORIGINAL REGX"
- MainRs!ORG_REX = fldValue
- Case "UIDs"
- MainRs.Fields(fldName) = fldValue
- TempUIDs = MainRs!UIDS
- Case Else
- MainRs.Fields(fldName) = fldValue
- End Select
- SkipProc:
- Loop
- Forms!startpage!lblProcess.Caption = "Complete: Time taken " & Format(Now() - x, "nn:ss") & ". " & Format(CharNo / NoOfChrs * 100, "00.00") & "% - Character " & CharNo & " of " & NoOfChrs & " (SFPI = " & TempUIDs & ")"
- Forms!startpage.Repaint
- EndRtn:
- Close hfile
- MainRs.Close
- Set MainRs = Nothing
- DoCmd.Hourglass False
- Exit Function
- ErrRtn:
- Select Case Err.Number
- Case 3020
- MsgBox "Complete", vbInformation, "Data Import"
- GoTo EndRtn
- Case 53
- MsgBox Err.Description, vbExclamation, "Import Data"
- GoTo EndRtn
- Case Else
- MsgBox Err.Number & " " & Err.Description
- Stop
- Resume Next
- End Select
- Resume
- End Function
>Text from attached Log.txt file.
Expand|Select|Wrap|Line Numbers
- 2014-06-11 10:18:18 PROS_TST: New System LOG inserted:
- UIDs = 1
- ARRIVAL TIME = 2014-06-11 10:43:08
- EST. TIME OF ENTRY = 2014-06-11 10:20:00
- COORDINATED EXIT TIME = Unknown
- PREVIOUS REG =
- NEXT REG =
- NUM REGPOINTS = 4
- TEST1 (Unknown)
- TEST2 (2014-06-11 10:20:00) ETO
- TEST3 (2014-06-11 10:28:56) ETO
- TEST4 (2014-06-11 10:31:44) ETO
- ORIGINAL REGS = TEST1 (Unknown)
- ORIGINAL REGX = TEST4 (2014-06-11 10:31:44) ETO
- 2014-06-11 10:30:18 PROS_TML: New System LOG updated: