Log file import

[rajeevs]

Hi All
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:

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.

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:

[jforbes]

Rajeevs, are you getting an error of any kind? What happens when you run the code?

You are correct, the code you provided doesn’t account for lines that begin with "TEST". I would guess it attempts to insert a new blank record for each of these lines, which hopefully gets rejected by your database constraints.

A cheap and sleazy workaround would be to change lines 77-94 to:

    If Left(Trim(txtLine), 4) <> "TEST" Then
        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
    End If

This should change the code to ignore the lines beginning with “TEST”, but you might want to address these new additions to your Log File as they look important.

[rajeevs]

Thank you jforbes
It can be anything after that NUM REGPOINTS field if the value greater than 0. If NUM REGPOINTS = 0 then the next fields will be like below
ORIGINAL REGS =
ORIGINAL REGX =
And that time my old code works fine. But most of the time the NUM REGPOINTS will have a value greater than 0 and there will some lines corresponding to the number and then the ORIGINAL REGS & ORIGINAL REGX appear.
So I cannot ignore the values after the NUM REGPOINTS