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/1719342/how-to-read-lines-from-a-text-file-one-by-one-with-power-point-vba-code

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

Once you have your data you can determine the fields with a split on the string https://msdn.microsoft.com/en-us/library/6x627e5f(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