Text file to access Table

[rajeevs]

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

[jforbes]

I don't have an Access code sample that does what you are looking for, but it's not that difficult to write a parsing routine that can handle the file format that you have provided.

The basic steps are:

• Create a DataStructure with all the fields that will be available in the TextFile. These can be just discrete variables or you can make a class file full of variables
• Open the File to read.
• Loop through the File Line by Line and populate the DataStructure as the elements are found.
• When a specific row is found that signifies the end of a group of data, like a blank line in your file, the DataStructure is as full as it's gonna get. If it has enough information, then insert a record into the database based on the DataStructure and then clear out the Datastructure.
• Keep doing this until the end of file is reached

This sort of seems to be a good starting place for an example on how to read a file and loop through it: Code to parse out selected fields of .txt to new table You could Split on the Equal Sign(=) instead of the Comma(,)

If you develop some code and get stuck, post it and we might be able to help you.

[rajeevs]

Thank you jforbes for the kind reply.
I have found a code which is actually importing the txt to one table, but not what I expect. It splits and import data to 2 fields. The code is as below:

Sub ReadandImport()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, str2() As String
Dim i As Integer
 
Set DB = CurrentDb
Set RS = DB.OpenRecordset("Layers")
 
Open "path & filename.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, str1
 
str2 = Split(str1, "=")
RS.AddNew
For i = 0 To UBound(str2)
RS(i) = str2(i)
Next
RS.Update
Loop
Close #1
RS.Close
MsgBox "Finished", vbInformation + vbOKOnly, "Import"
End Sub

I dont know how to process each line and get the expected result as shown in my attached excel file. Can you please guide me

[jforbes]

I created a Sample for you to work with. It's written the way I mentioned in my first post. I kind of got the impression that you ignored what I had wrote in that post, so please try to understand what steps are need to be done to complete this type of Import. This is a pretty common type of import... .INI files work this way. I think there is even a class available in .NET to handle this file type.

The sample I created has only one Table to house both datatypes that are defined in the Sample Text that you provided. It was easier for me to just create one table, but if you need to have two separate tables it shouldn't be that hard for you to determine the Table when the datatypes are inserted/updated into the Tables.

Lastly, the sample has the location of the Import File hard coded, you'll need to update that to a real location...

    Open "C:\Users\jforbes\Desktop\Bytes\TextImport\Sample-1.txt" For Input As #1

[rajeevs]

Dear jforbes
It was amazing. I don't know how to thank you. I have tested with the actual data and it is perfectly imported. I don't know much about class modules but I will try to understand. I dont need 2 tables, this is more than enough for me. Thank you so much

[DavidHP]

@jforbes
Awesome file upload.
:)