How can I parse a very wide text data file into access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing a wide (950 field) text file into access. I have created a
multiple table scema to receive the data. The number of fields appears to be
too much for the Text Import Wizzard. Is there a way to parse first or 'parse
and reparse' the data file?
Thanks,
 
Probably the best way to do this is through VB Low-level I/O. Basically,
you read each line of your text file into a string variable and then parse
each field yourself. Something like this:

(NOTE: this assumes a comma delimited text file. Other delimiters or a
fixed-width file require modifications. Also, assume your textfile is named
LineInput.txt and is on the root of the C drive, and your tables are Table1,
Table2, and Table3)

Open "C:\LineInput.txt" For Input As #1

' Create a dynaset-type Recordset object based on Shoes table.
Set rst1 = dbs.OpenRecordset("Table1")
Set rst2 = dbs.OpenRecordset("Table2")
Set rst3 = dbs.OpenRecordset("Table3")

Do While Not EOF(1)
Line Input #1, MyString
'Add a new Record
rst1.AddNew
rst1!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1!Field2 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1!Field3 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1.Update
rst2.AddNew
rst2!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2!Field2 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2!Field3 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2.Update
rst3.AddNew
rst3!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst3!Field 2= MyString
rst3.Update
Loop
' Close text file.
Close #1

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "InputLineInput.mdb" which illustrates how to do this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top