By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

Text file to access Table

100+
P: 171
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
Attached Files
File Type: txt Sample.txt (556 Bytes, 239 views)
File Type: xlsx SampleTblStructure.xlsx (9.5 KB, 178 views)
Mar 30 '17 #1

✓ answered by 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...
Expand|Select|Wrap|Line Numbers
  1.     Open "C:\Users\jforbes\Desktop\Bytes\TextImport\Sample-1.txt" For Input As #1

Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
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.
Mar 31 '17 #2

100+
P: 171
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:
Expand|Select|Wrap|Line Numbers
  1. Sub ReadandImport()
  2. Dim DB As DAO.Database, RS As DAO.Recordset
  3. Dim str1 As String, str2() As String
  4. Dim i As Integer
  5.  
  6. Set DB = CurrentDb
  7. Set RS = DB.OpenRecordset("Layers")
  8.  
  9. Open "path & filename.txt" For Input As #1
  10. Do While Not EOF(1)
  11. Line Input #1, str1
  12.  
  13. str2 = Split(str1, "=")
  14. RS.AddNew
  15. For i = 0 To UBound(str2)
  16. RS(i) = str2(i)
  17. Next
  18. RS.Update
  19. Loop
  20. Close #1
  21. RS.Close
  22. MsgBox "Finished", vbInformation + vbOKOnly, "Import"
  23. 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
Apr 1 '17 #3

jforbes
Expert 100+
P: 1,107
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...
Expand|Select|Wrap|Line Numbers
  1.     Open "C:\Users\jforbes\Desktop\Bytes\TextImport\Sample-1.txt" For Input As #1
Attached Files
File Type: zip TextImport.zip (45.7 KB, 184 views)
Apr 1 '17 #4

100+
P: 171
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
Apr 2 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.