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.....

[rajeevs]

Dear twinnyfo
I will post an amended sample file tomorrow and explain in detail. The log file structure has small changes. Thank you for your patience and kindness towards me.

[twinnyfo]

No problem! I know what it is like to feel lost trying to create a project. We all need a little help from time to time.

[rajeevs]

Dear twinnyfo
I am attaching a sample file with one record. In real life there could be thousands of entries like that.
Let me explain what i am trying to do.
All the lines in the attached sample file are for one record. Like that there could be several records in the log file. Date of the log file will be part of the file name(in the sample it is 06Mar2015). The field names in table1 are defined as the log file text line string before : sign (Fld1 to Fld45). Right of : sign to be imported to Fld1 to Fld45 in Table1. But while reading the log file and when reach the line starts with Tbl2Fld1 skip the reading to next line. Next lines are numbered from 1 to 25. Fld24 Field value will give an indication of how many lines need to be imported from the 25 lines. The data in these lines are separated with comma and each line will be imported to Table2 as separate records but with three additional fields (Sno, FldDate, Fld45
Table2 field names are as below
Sno, DateFld, Fld45, Tbl2Fld1,Tbl2Fld2,Tbl2Fld3,Tbl2Fld4,Tbl2Fld5,Tbl2F ld6
While importing to Table2 the code checks for a comparison with another table records (FixRs mentioned in my code).
These are all ok with my routine. My problem starts when it reaches the line after Fld42.
That is
Tbl3Fld1,Tbl3Fld2,Tbl3Fld3,Tbl3Fld4:
So when I reach there I look for the word"Tbl3Fld1" then skip to read the next line.
From here I need to read the next 4 lines and if there is any data after the line number, import it to Table3.
The field names in Table3 are same as the log file line plus 4 additional fields
Sno, DateFld, Fld1, Fld45, Tbl3Fld1,Tbl3Fld2,Tbl3Fld3,Tbl3Fld4
After importing to Table3 continue reading the next two lines in log file (Fld43 & Fld44)
When reaches the line starts with "Tbl4Fld1" skip to read next line
Fld44 Field value will give an indication of how many lines need to be read from the 1 to 4 numbered lines.
Then these to be written to Table4. table4 has field names as
Sno, DateFld, Fld1,Fld45,Tbl4Fld1,Tbl4Fld2,Tbl4Fld3.
Hope I explained well. I am trying to fine tune the code posted earlier but no use at all. While waiting for your reply I will continue breaking my head. Hope you could help me to come out of this deadlock with a decent piece of code.
Thank you twinnyfo for you patience and kindness

[twinnyfo]

rajeevs,

Your last post and sample file have given me nothing new. Again, your code does not match up with what you have in your data, so I don't know how it is possible for your code to work. I'm not saying that it doesn't perform as you say, but what I am saying is that it does not match the data you have, so it is impossible for anyone to troubleshoot code that does not match the data we are working with. This is why over the several months that we have been working on this that I have not even attempted to troubleshoot your code. I would not get one line of code to import properly, because your code does not correspond to data in the log file supplied.

Again, I refer back to my earlier posts. You have greatly over-complexified your code. If you use Scripting, and read one line at a time from your data, find out where it should go, this should all run smoothly.

However, again, I don't know how your four tables are related to each other (if at all).

Concerning the Field Names listed in the sample log file, it is truly immaterial, as the recordsets you create based on your tables can use references to the number of the field vice an actual name.

Have you tried re-engineering your code as I recommended? If the log file contains simple records, listed in order, there should be no complication in how the data is imported. You have the tables at hand, you have the data at hand, you have the number of records to import at hand, you know the field types (because they are in your tables). You must simply match the data to the tables.

This is not an "easy" import, because there are many different fields to play with and you must match the data to the field. But it is neither complex nor complicated, as your data is always presented in the same format.

Please don't think that I am being dismissive. I just can't provide any additional assistance based on what you have provided. My recommendation is to re-engineer your code or provide a log file that actually corresponds to the code you are currently using. Even with that, my recommendation would still be to re-engineer. Sometimes starting from scratch is the best way to see the simplicity of a problem.....