parse a text file

[David]

Hello all,
I have a very large but odd text file. It has only one row, with 5 fields.
The fourth field, RULE_TEXT has all of the data I want with the data elements
seperated by a semi-colon.
I would like the create a table from data in Field4, with each semi-colon
creating a new row in the table.

I am using this code I found in another post and trying to modify it, but I
receive on line 100, the error 62, Input past end of file.

Function ReadTextFile()

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String

Dim rstData As DAO.Recordset


10 Set rstData = CurrentDb.OpenRecordset("tbl_TABLE_RULE")

20 strFile = "C:\Program Files\Database\table_rule.txt"

30 intF = FreeFile()
40 Open strFile For Input As #intF

50 Do While EOF(intF) = False
60 rstData.AddNew

70 Line Input #intF, strLineBuf
80 Line Input #intF, strLineBuf
90 Line Input #intF, strLineBuf
100 rstData!RULE_TEXT = Split(strLineBuf, ";")(-1)
110 Line Input #intF, strLineBuf

120 rstData.AddNew
130 Loop
140 Close intF

End Function


Any assistance you can provide is greatly appreciated.

David

 

[Steve Sanford]

How do you know when one "field" of this text file ends and another begins?
Are they comma seperated?

How many data elements do you expect to have in the "4th field" of the text
file?

Is this a one time thing or will you have to do this over and over? (It
might be easier to edit the text file to remove the unwanted "fields" if this
is a one time import)

Without making the line too long, could you provide a sample of the text file?


So the steps are:

Read the line from the text file
Get rid of (parse) the unwanted data
Split the "4th field" elements into an array
using the array, loop thru the array elements, inserting them into the table
Clean up (close the text file,close the recordset)

Not too hard, but specific examples really help.