VBA to import log file to ACCESS Table

[rajeevs]

Hi All
Again stuck with some issues
I have a log file as attached sample with 2 records. What I am looking is to read the file line by line from MS ACCESS Database and import the records to 4 different ACCESS tables. The table1 field names will be left of the : mark and field values are right of the : mark.
Table2, 3 & 4 will have field names as highlighted.
So while reading the lines and reaching the line starts with Tb2Fld1, then the next lines (maximum 25 which can be decided from the Tb1Fld22 value) need to be written to table2 .
Same rule applies to Table3 while reading the lines and reaching the line Tb3Fld1, then the next lines (maximum 4 which can be decided from the Tb1Fld41 value) need to be written to table3.
For Table4 also while reading the lines and reaching the line Tb4Fld1, then the next 4 lines need to be written to table4 which can be decided from the Tb1Fld43 value.
The field values suppose to be written to Table2,3, & 4 are separated by comma in the log file. The last field Tb1Fld44 is the UID which need to be imported to all the tables.
Each records in log file will start Tb1Fld1 as field name and end with Tb1Fld44.
Hope I explained well. Expecting a favorable reply

[twinnyfo]

rajeevs,

I'm pretty sure we can help you with this, but what have you tried so far? Have you much experience with using File System Objects (which is probably the best way to approach this)?

A re the Tb1Fld1-44 the actual names as they appear in your text file? I think this is very doable, but we expect our posters to at least provide a first basic attempt at solving their problems and we assist with troubleshooting .

I'm glad to help along the way.....

[rajeevs]

Thank you twinnyfo for the quick response as usual.
The field names (which are left of the : in the sample file)are always static but due to the sensitivity of the log file I have changed the data in the log file and given you. The fields in the tables are already defined based on the log file fields. I can understand FSO and VBA. But not that expert. I have a module which was importing the log file to ACCESS DB but now the log file structure has changed. The changes are additional lines for the table3 and table4 where I stuck.

[twinnyfo]

Well, here is an outline of how to attack your code (I will stand by to assist with any hang-ups you may have):

Using FSO, open the file
Create four recordsets (one for each of the tables that you will be saving to)

Add a new record to Table1 for fields 1-21 (for this, you just read a new line into a string variable, get rid of the data to the left of ":" and save the data to the right.

At tb1Fld22, get that value (which will tell you how many rows to Add to Table2.

For each of those rows, add a new record to table2, evaluate the necessary data and copy it into your table.

Then read more lines, being on the lookout for tb1Fld23. Ad those values to the same record in table1.

At tb1Fld41, again get that value, add that many records to Table 3.

Watch for tbl1Fld42--add more to Table1.

Tbl1Fld43, again add that many rows to Table4.

Add the UID to Table1.

NB: When I say to add records to those tables, this is all done with the recordsets you have opened. Then, make sure you update the recordsets, and get ready for the next record in Table 1.

This is the general concept you should take. I recommend working through each piece slowly, debugging and double-checking each step, to make sure the results for each field are the desired results (i.e. numerical values are imported as numbers, and not as text).

Again, I'm glad to help walk you through any snags, but you said you understand both FSO and VBA. So, although you are not an expert, as we work through this, we will strengthen your skills!

[rajeevs]

Thank you twinnyfo
Sorry for the late reply. Yesterday I was trying with a sample DB but few issues where I start adding the records to Tbl3 and Tbl4. Tbl1 & 2 seems ok. I will try to fine tune the module and will update you later today about my progress. I am so grateful for the kindness you are showing in assisting and the promise of help

[twinnyfo]

It would also be helpful if you posted the code for your module here and identified at which lines the code seems to break down. We can troubleshoot from there....

[rajeevs]

Dear twinnyfo
So for the delay. I was on holidays.
Can I PM u the code

[twinnyfo]

We prefer you post the code on the forum, so others with similar problems may also learn.

[rajeevs]

Dear twinnyfo
I was stuck with so many other things in the office due to the year end and so delayed to reply. I am sorry for that. I am back to the same project now and my issue remains unsolved. The VBA i tried to use is not a clean procedure because it was copied from someone and modified by me whenever new things required.
It does all the other parts but not importing the correct records to tbl3 and tbl4.
Hope i can still expect your help. Please advise then i will post the code.

[twinnyfo]

rajeevs,

Please post any code you have and explain where it is going wrong. Without it we cannot help to troubleshoot.

[rajeevs]

Function LogImport(AddPth As String)

On Error GoTo ErrRtn
Dim hfile As Integer
Dim MainRs As Recordset
Dim FixRs As Recordset
Dim WPRs As Recordset
Dim SkipWPRs As Recordset
Dim fldName As String
Dim fldValue As String
Dim txtLine As String
Dim DOF As String
Dim NumFix As Integer
Dim FltNum As Integer
Dim FirstTime As String
Dim LastTime As String
Dim FirstPt As String
Dim LastPt As String
Dim CharNo As Long
Dim LineNo As Long
Dim NoOfChrs As Long
Dim x As Variant
Dim fileinfo As Variant
Dim MyArray As Variant
Dim theCount As Variant
Dim LATinfo As Boolean
Dim Comma1 As Integer
Dim Comma2 As Integer
Dim Comma3 As Integer
Dim Comma4 As Integer
Dim Comma5 As Integer
Dim TimeOver As String
Dim LevelOver As String
Dim NewSkip As Boolean

Dim holdrs As Recordset
Dim Comma6 As Integer
Dim Comma7 As Integer
Dim Comma8 As Integer
Dim NumHold As Integer

Dim FlowRs As Recordset
Dim Comma9 As Integer
Dim Comma10 As Integer
Dim NumFlow As Integer


DOF = Format(Forms!processpage!txtDate, "ddmmmyyyy")
hfile = FreeFile()
Set MainRs = CurrentDb.OpenRecordset("Tbl1", dbOpenDynaset, dbAppendOnly)
Set FixRs = CurrentDb.OpenRecordset("Select * from Tbl2 order by SNo", dbOpenDynaset, dbAppendOnly)
Set WPRs = CurrentDb.OpenRecordset("Select * from TblWpt order by Waypoint")
Set SkipWPRs = CurrentDb.OpenRecordset("Select * from TblSkip order by Waypoint")

Set holdrs = CurrentDb.OpenRecordset("Select * from Tbl3 order by SNo", dbOpenDynaset, dbAppendOnly)
Set FlowRs = CurrentDb.OpenRecordset("Select * from Tbl4 order by SNo", dbOpenDynaset, dbAppendOnly)

Open RawPath & AddPth & "FileName_" & DOF For Input As hfile

fileinfo = Input(LOF(hfile), hfile)
MyArray = Split(fileinfo, vbCrLf)
theCount = UBound(MyArray)
Close hfile
Open RawPath & AddPth & "FileName_" & DOF For Input As hfile
x = Now()

Do While Not EOF(hfile)
DoEvents
Forms!processpage!lblProcess.Caption = "Processing ... " & Format(LineNo / theCount * 100, "00.00") & "% - Line " & LineNo & " of " & theCount
Forms!processpage.Repaint
   
   Line Input #hfile, txtLine
     
   LineNo = LineNo + 1
   If txtLine = "F1" Or Trim(txtLine) = "" Then
      If MainRs.EditMode <> dbEditNone Then
         Select Case MainRs!F19
         
        Case "IN"
            MainRs!F11 = IIf(IsNull(FirstTime) Or FirstTime = "", Null, FirstTime)
            MainRs!F12 = IIf(IsNull(MainRs!F12) Or MainRs!F12 = "", Null, MainRs!F12)
            
            MainRs!COPN = FirstPt
            MainRs!COPX = MainRs!F10
         
         Case "OUT"
            MainRs!F11 = IIf(IsNull(MainRs!F11) Or MainRs!F11 = "", Null, MainRs!F11)
            MainRs!F12 = IIf(IsNull(LastTime) Or LastTime = "", Null, LastTime)
            
            MainRs!COPN = MainRs!F8
            MainRs!COPX = LastPt
         
         Case "1OVR"
            MainRs!F11 = IIf(IsNull(FirstTime) Or FirstTime = "", Null, FirstTime)
            MainRs!F12 = IIf(IsNull(LastTime) Or LastTime = "", Null, LastTime)
            
            MainRs!COPN = FirstPt
            MainRs!COPX = LastPt
         
         Case "DOM"
           MainRs!F11 = IIf(IsNull(MainRs!F11) Or MainRs!F11 = "", Null, MainRs!F11)
           MainRs!F12 = IIf(IsNull(MainRs!F12) Or MainRs!F12 = "", Null, MainRs!F12)
           
            MainRs!COPN = MainRs!F8
            MainRs!COPX = MainRs!F10
         End Select
         MainRs!DDate = Forms!processpage!txtDate
            
            MainRs!Excel = True
            MainRs!Billing = True
         
         MainRs.Update
         FirstTime = ""
         LastTime = ""
         FirstPt = ""
         LastPt = ""
      End If
      If Trim(txtLine) = "" Then GoTo SkipProc 'to read next line
   End If
   
   'Tbl2 start
  
   If Left(txtLine, 4) = "FIX," Then
        If InStr(txtLine, "LAT") > 0 Then
         LATinfo = True
      Else
         LATinfo = False
      End If
      GoTo SkipProc ' read next line
   End If
   
   If Trim(Left(txtLine, 2)) >= 1 And Trim(Left(txtLine, 2)) <= 25 Then 'if line starts with numbers between 1 to 25 (fixes)
      If Trim(Left(txtLine, 2)) <= NumFix Then 'Processes upto the number of fixes recorded in earler line of text file
         FixRs.AddNew
         FixRs!Sno = MainRs!Sno
            
         Comma1 = InStr(3, txtLine, ",") '1st comma is after fix id
         Comma2 = InStr(Comma1 + 1, txtLine, ",") '2nd comma is after Time over fix
         Comma3 = InStr(Comma2 + 1, txtLine, ",") '3rd comma is after Level over fix
         If LATinfo Then
            Comma4 = InStr(Comma3 + 1, txtLine, ",") '4th comma is after LAT
            Comma5 = InStr(Comma4 + 1, txtLine, ",") '5th comma is after LONG
            If Comma4 > 0 Then
               FixRs!LAT = Mid(txtLine, Comma3 + 1, Comma4 - Comma3 - 2)
            End If
         
            If Comma5 > 0 Then
               FixRs!Long = Mid(txtLine, Comma4 + 1, Comma5 - Comma4 - 2)
               FixRs!DIST = Val(Mid(txtLine, Comma5 + 1))
            End If
         Else
           If Comma3 > 0 Then FixRs!DIST = Val(Mid(txtLine, Comma3 + 1))
         End If
         
         
         FixRs!Fix = Trim(Mid(txtLine, 4, Comma1 - 4 - 1))
         If Asc(Left(FixRs!Fix, 1)) > 57 Then
            WPRs.FindFirst "Waypoint = '" & FixRs!Fix & "'"  'Checking if waypoint is defined
            If WPRs.NoMatch Then  'Only process for defined waypoints
                SkipWPRs.FindFirst "Waypoint = '" & FixRs!Fix & "'"
                   If SkipWPRs.NoMatch Then
                      SkipWPRs.AddNew
                      SkipWPRs!Waypoint = FixRs!Fix
                      SkipWPRs.Update
                      NewSkip = True
                   End If
                   FixRs.CancelUpdate
                   GoTo SkipProc
                
            End If
         End If
         If Comma3 > 0 Then
            'stores time in a variable
            TimeOver = Trim(Mid(txtLine, Comma1 + 1, Comma2 - Comma1 - 1))
            'stores level in a variable
            LevelOver = IIf(Len(Trim(Mid(txtLine, Comma2 + 1, Comma3 - Comma2 - 1))) = 2, "0" & Trim(Mid(txtLine, Comma2 + 1, Comma3 - Comma2 - 1)), Trim(Mid(txtLine, Comma2 + 1, Comma3 - Comma2 - 1)))
         Else
            'stores time in a variable
            TimeOver = Trim(Mid(txtLine, Comma1 + 1, Comma2 - Comma1 - 1))
            'stores level in a variable
            LevelOver = IIf(Len(Trim(Mid(txtLine, Comma2 + 1))) = 2, "0" & Trim(Mid(txtLine, Comma2 + 1)), Trim(Mid(txtLine, Comma2 + 1)))
         End If
         If Val(TimeOver) > 2100 And FltNum > 5000 Then 'adjust fltnum based on movement data
            'previous days flight
            FixRs!FTIME = TimeOver 'Function converts DateGroup to 1 day less
            If FirstTime = "" Then
               FirstTime = TimeOver
               FirstPt = FixRs!Fix
            End If
            LastTime = TimeOver
         Else
            
            FixRs!FTIME = TimeOver
            If FirstTime = "" Then
               FirstTime = TimeOver
               FirstPt = FixRs!Fix
            End If
            LastTime = TimeOver
         End If
         LastPt = FixRs!Fix
         FixRs!LVL = LevelOver
         FixRs!DDate = Forms!processpage!txtDate
         FixRs.Update

      End If
      
      GoTo SkipProc 'jump to read next line
   
   End If
      'Tbl2 end
   
   'Tbl3 Start
   
   If Left(txtLine, 4) = "HLD," Then
      GoTo SkipProc      'jump to read next line
   End If
   
   If Trim(Left(txtLine, 1)) >= 1 And Trim(Left(txtLine, 1)) <= 4 Then 'if line starts with numbers between 1 to 4 (holds)
      If Trim(Left(txtLine, 1)) <= NumHold Then 'Processes upto the number of hold recorded in earler line of text file
'End If
'GoTo SkipProc
'
'End If

         holdrs.AddNew
         holdrs!Sno = MainRs!Sno 'links positions with flight information

         Comma6 = InStr(3, txtLine, ",") '1st comma is after hold
         Comma7 = InStr(Comma6 + 1, txtLine, ",") '2nd comma is after hnt
         Comma8 = InStr(Comma7 + 1, txtLine, ",") '3rd comma is after hxt

        If Comma6 > 0 Then
        holdrs!HLD = Mid(txtLine, 3, Comma6 - 3)
        End If
        If Comma6 > 0 Then
        holdrs!HNT = Trim(Mid(txtLine, Comma6 + 1, Comma7 - Comma6 - 1))
        End If
        If Comma8 > 0 Then
        holdrs!HXT = Trim(Mid(txtLine, Comma7 + 1, Comma8 - Comma7 - 1))
        End If
        If Comma8 > 0 Then
        holdrs!HDUR = Right(txtLine, 8)
       End If
        holdrs!DDate = Forms!processpage!txtDate
        holdrs.Update

    End If

    End If
   
'Tbl3 end

'Tbl4 Start
  If Left(txtLine, 6) = "FLPNT," Then
      GoTo SkipProc 'jump to read next line
   End If

   If Trim(Left(txtLine, 1)) >= 1 And Trim(Left(txtLine, 1)) <= 4 Then 'if line starts with numbers between 1 to 4 (FLOW)
      If Trim(Left(txtLine, 1)) <= NumFlow Then 'Processes upto the number of FLOW recorded in earler line of text file
'End If
'GoTo SkipProc
'
'End If

         FlowRs.AddNew
         FlowRs!Sno = MainRs!Sno

         Comma9 = InStr(3, txtLine, ",") '1st comma is after hold
         Comma10 = InStr(Comma9 + 1, txtLine, ",") '2nd comma is after hnt

        If Comma9 > 0 Then
        FlowRs!FLPNT = Trim(Mid(txtLine, 3, Comma9 - 5 - 1))
        End If
        If Comma9 > 0 Then
         FlowRs!FLTIME = Trim(Mid(txtLine, Comma9 + 1, Comma10 - Comma9 - 1))
         End If
         If Comma9 > 0 Then
         FlowRs!FLLVL = Right(txtLine, 3)
        End If
         FlowRs!DDate = Forms!processpage!txtDate
         FlowRs.Update

 End If
 GoTo SkipProc 'jump to read next line
 End If


'Tbl4 End

fldName = Left(txtLine, InStr(txtLine, ":") - 1)

   If fldName = "F1" Then 'first line of text file
      FltNum = FltNum + 1 'increment number of flights variable
      If MainRs.EditMode = dbEditNone Then
         MainRs.AddNew
      Else
         MainRs!DDate = Forms!processpage!txtDate
         MainRs.Update
         MainRs.AddNew
      End If
   End If
   
   If fldName = "F20" Then
      NumFix = Trim(Mid(txtLine, InStr(txtLine, ":") + 1))
   End If
   
 If fldName = "F39" Then
      NumHold = Trim(Mid(txtLine, InStr(txtLine, ":") + 1))
   End If
  
   If fldName = "F41" Then
      NumFlow = Trim(Mid(txtLine, InStr(txtLine, ":") + 1))
   End If
  
   
      
  fldValue = Trim(Mid(txtLine, InStr(txtLine, ":") + 1))
   
   
   If (fldName = "F11" And MainRs!F8 Like "OM*") Or (fldName = "F12" And MainRs!F10 Like "OM*") Then
      If Val(fldValue) > 2100 And FltNum > 5000 Then
        
        fldValue = fldValue
      Else
        fldValue = fldValue
      End If
   ElseIf fldName = "F11" Or fldName = "F12" Then
      GoTo SkipProc
   End If
   
   If fldName = "F42" Then
   
   'Update UID in Tbl2 table
      FixRs.FindFirst "Sno = " & MainRs!Sno
      Do While Not FixRs.EOF
         If FixRs!Sno = MainRs!Sno Then
            FixRs.Edit
            FixRs!f42 = Val(fldValue) 'UNIQUE value extracted from the text line
            FixRs.Update
            FixRs.MoveNext
         Else
            Exit Do
         End If
      Loop
      MainRs.Fields(fldName) = Val(fldValue)
   
   
   Else
   If (fldName = "F11") Or (fldName = "F12") Or (fldName = "F32") Or (fldName = "F33") Or (fldName = "FPL_ARR_TME") Or (fldName = "FPL_FIR_ET") _
   Or (fldName = "F13") Or (fldName = "F14") Or (fldName = "F20") Or (fldName = "F38") Or (fldName = "F41") Then
   If (IsNull(fldValue) Or fldValue = "") Then
   Else
   
      MainRs.Fields(fldName) = fldValue
      
   End If
   Else
   MainRs.Fields(fldName) = fldValue
   End If
   End If
 
SkipProc:
Loop

If MainRs.EditMode <> dbEditNone Then
   Select Case MainRs!F19
   
  Case "IN"
            MainRs!F11 = IIf(IsNull(FirstTime) Or FirstTime = "", Null, FirstTime)
            MainRs!F12 = IIf(IsNull(MainRs!F12) Or MainRs!F12 = "", Null, MainRs!F12)
            
            MainRs!COPN = FirstPt
            MainRs!COPX = MainRs!F10
         
         Case "OUT"
            MainRs!F11 = IIf(IsNull(MainRs!F11) Or MainRs!F11 = "", Null, MainRs!F11)
            MainRs!F12 = IIf(IsNull(LastTime) Or LastTime = "", Null, LastTime)
            
            MainRs!COPN = MainRs!F8
            MainRs!COPX = LastPt
         
         Case "1OVR"
            MainRs!F11 = IIf(IsNull(FirstTime) Or FirstTime = "", Null, FirstTime)
            MainRs!F12 = IIf(IsNull(LastTime) Or LastTime = "", Null, LastTime)
            
            MainRs!COPN = FirstPt
            MainRs!COPX = LastPt
         
         Case "DOM"
           MainRs!F11 = IIf(IsNull(MainRs!F11) Or MainRs!F11 = "", Null, MainRs!F11)
           MainRs!F12 = IIf(IsNull(MainRs!F12) Or MainRs!F12 = "", Null, MainRs!F12)
           
            MainRs!COPN = MainRs!F8
            MainRs!COPX = MainRs!F10
   
   End Select
   MainRs!DDate = Forms!processpage!txtDate
  MainRs!Excel = True
  MainRs!Billing = True
   MainRs.Update
End If

Forms!processpage!lblProcess.Caption = "Complete: Records = " & FltNum & ". Time taken = " & Format(Now() - x, "nn:ss") & ". Lines = " & theCount
Forms!processpage.Repaint

EndRtn:
Close hfile
WPRs.Close
Set WPRs = Nothing
SkipWPRs.Close
Set SkipWPRs = Nothing
FixRs.Close
Set FixRs = Nothing
holdrs.Close
Set holdrs = Nothing
FlowRs.Close
Set FlowRs = Nothing
MainRs.Close
Set MainRs = Nothing
If NewSkip = True Then DoCmd.OpenReport "SkippedWaypoint", acViewPreview 'this report is not in this sample DB
Exit Function

ErrRtn:
Select Case Err.Number
Case 3020
   MsgBox "Complete", vbInformation, "Data Import"
      Forms!processpage!lblProcess.Visible = False
GoTo EndRtn
Case 53
   MsgBox Err.Description, vbExclamation, "Import Data"
      Forms!processpage!lblProcess.Visible = False
GoTo EndRtn
Case 3022
   MsgBox Err.Description, vbExclamation, "Import Data"
   Dim rsProb As Recordset
   Dim i As Integer
   Dim FName As String
   DoCmd.RunSQL "Delete * from Problems"
   Set rsProb = CurrentDb.OpenRecordset("Problems")
   rsProb.AddNew
   For i = 0 To rsProb.Fields.Count - 1
       FName = rsProb.Fields(i).Name
       If MainRs.Fields(FName).Name = FName Then rsProb.Fields(i) = MainRs.Fields(FName).Value
   Next
   rsProb.Update
   FltNum = FltNum - 1
   MainRs.CancelUpdate
   Resume Next
Case Else
   MsgBox Err.Number & " " & Err.Description
   Forms!processpage!lblProcess.Visible = False
   Stop
   Resume
End Select


End Function

That is is the function used to import the log file. It is not a well written code because I mentioned earlier that I am not an expert and it was not fully done by me. Hope you will help me out to solve this. Tbl3 & Tbl4 imports are not doing proerly and F42 is not imported to Tbl3 & 4. Thank you twinnyfo

[twinnyfo]

rajeevs,

What creates this log file? Are there "real" table and field names or are they truly "Tbl1" and "tb1Fld1" etc.? I understand if there is proprietary or confidential information in your database, but the more ambiguity in a DB, the more likely you are to get confused (and guaranteed that others trying to help will be confused).

Based on just a cursory glance at your code, I'm not sure how it is supposed to work (I understood better when I just had your text file....).

In lines 75-119 above, based on the text file, will never fire, because I found to text that matched those criteria (granted, only two records were given). BUT, apparently, that is the entire first table! however, the same goes for tables 2, 3 and 4. I don't know how your code would have imported any data from your sample.

Not to mention, there are a number of irregularities in how one might normally use recordsets. I understand that you did not write this code, but I must offer my apologies that I am really confused by it.

As far as advice on how to approach this problem, I would attack the log file as containing a series of records with items 1-44 (since "Tbl1" has 44 records, and its record fully encapsulate the other three tables, this is where to start).

When importing Tbl1, you watch for Fields 22, 41 and 43 (whatever their particular names are if other than "tb1Fld22", etc.), and then import that number of rows to the appropriate Table, skipping any blank rows.

Once you hit Field 44, you know you are at the end of the record, close out Table 1's record, and start with the next record.

This is not a "solution" but an "approach" to solving your problem. Without seeing actual Table and Field Names, not to mention having no knowledge of the structure of your DB, I can't provide much more.

However, I am willing to try and work you toward a final solution. I will just need more information on this project.

And... This will take some time, as we are doing it over a forum....

[rajeevs]

Dear twinnyfo
Sorry for the delay. I was also trying to figure out how to do this in a better way.
The code I have posted does the Tbl1 & Tbl2 imports correctly. But Tbl3 & Tbl4 it doesn't. It is importing some values but not the desired one. So can you please guide me how to import only to Tbl3 as a separate procedure when Left(txtLine, 4) = "HLD," and same for Tbl4. So I can skip Tbl3 & Tbl4 imports in the main module.

[rajeevs]

Dear twinnyfo
Any luck with my question?
May be we can have a different approach. I am able to import the log records to table 1 and 2 successfully. Is there a way i can read the file from certain line then import only four lines after that criteria then read and import the last line also. So I can try it as a separate procedure. Hope you would be able to help.
Thank you

[twinnyfo]

rajeevs,

One of the problems that I have to keep coming back to is the inconsistency of the code with the text file you provided. There is no value in the text file called "HLD", which is what tbl3 is apparently looking for?

Again, my approach would be a much "cleaner attack on your text file (pseudo code here):

Option Compare Database
Option Explicit

Public Function LogImport(AddPth As String)
On Error GoTo EH
    'Declare your variables

    Import a Line of text
    Determine which Table It Belongs to
    Select Case Table
        Case Table1
            Add Values to Table1
        Case Table2
            Add Values to Table2
        Case Table3
            Add Values to Table3
        Case Table4
            Add Values to Table4
    End Select

    Go to the Next Record in the Text File or Close out the code.

EH:
    MsgBox "There was an Error!"
End Function

Again, this is an "approach" and not a solution. Please see the concept in Post #4. I think this is your best approach, but you must start out with small chunks of your log file and ensure that each piece is imported perfectly before you move along to the next.

I am still willing to assist, but I don't know where to start with this log file when I have no idea what the final data is supposed to look like.

Are Tables 1-4 related to each other somehow? What is their relationship, as this could also pose particular challenges.

I do hope we can make headway on this one.....