read txt file line by line and import to access

[jacks]

Hi All
I have a text file which has records in a multi line structure.
Each record is separated with a blank line in between.
Some records will have 9 fields (in text file it is 9 lines)
and some of them will have 12 fields (or 12 lines in the text file). If the line has only "Layers" in the start line that line and the next following lines to Tbl1 and if the line has "Groups" in the middle of the line it should be saved in the Tbl2.
There are lines end with "*" symbol should not imported to any of the tables.There could be one Layer record then several group lines then again layer then groups like that.
I am trying to import this data to an access table. Hope some of the experts can help me to resolve this. I am attaching 2 sample files. One is a txt file and another one an Excel file which shows the expected result.
Please help

[Movian]

I would suggest researching the File System Object for reading a text file line by line. https://stackoverflow.com/questions/...point-vba-code

you can then read the data into a dynamic array (Redim preserve) https://stackoverflow.com/questions/...im-preserve-do

Once you have your data you can determine the fields with a split on the string https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

Then loop through your fields and built an SQL insert and execute it (Currentproject.execute SQL) https://www.w3schools.com/sql/sql_insert.asp


Hope this helps!

[jonh]

This should import everything to fields of text datatype

Private Sub Example()


    strFile = "C:\testfile.txt"


    fs = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFile, 1).readall
    fs = Replace(fs, "/Groups", vbNewLine & "Groups")
    For Each itm In Split(fs, vbNewLine & vbNewLine)
        tbl = ""
        flds = ""
        vals = ""
        fLines = Split(Trim(itm), vbNewLine)
        
        Select Case UBound(fLines) + 1
        Case 9:   tbl = "tbl1"
        Case 13:  tbl = "tbl2"
        End Select


        If tbl <> "" Then
            For Each i In fLines
                f = Splitter(i)
                If Not IsEmpty(f) Then
                    If Len(flds) Then
                        flds = flds & ","
                        vals = vals & ","
                    End If
                    flds = flds & f(0)
                    vals = vals & f(1)
                End If
            Next
            SQL = "insert into [" & tbl & "] (" & flds & ") values (" & vals & ")"
            Debug.Print SQL
            CurrentDb.Execute SQL
        End If
    Next
End Sub


Private Function Splitter(s)
    If Trim(s) = "" Then Exit Function
    s = Replace(s, "/", "=")
    s = Replace(s, "[", "")
    s = Replace(s, "]", "")
    If InStr(s, "=") Then
        a = Split(s, "=")
        a(1) = "'" & a(1) & "'"
        Splitter = a
    End If
End Function