import text into tables

[Ron Berns]

I receive a text file that contains two types of records, a header
record and a detail record. Currently I import the file into Excel, sort
the records, make spreadsheet for the header records and a spreadsheet
for the detail records. Then I import each spreadsheet into the
respective table in Access.

"H",43251284,20090328,"ST","W-M/BOF #1470",12,23.52
"D",12,"EA",1.96,"UP","076721404687","VN","407990"
"H",35114677,20090330,"ST","W-M/BARRELL O'FUN #1861",21,53.04
"D",9,"EA",3.28,"UP","076721394551","VN","408600"
"D",12,"EA",1.96,"UP","076721396050","VN","425730"
"H",35204575,20090330,"ST","W-M/BARRELL O'FUN #3513",29,77.60
"D",8,"EA",3.28,"UP","076721394575","VN","043950"
"D",12,"EA",1.82,"UP","076721403758","VN","408400"
"D",9,"EA",3.28,"UP","076721394551","VN","408600"


I was wondering if it is possible to write some code to read this
text file and automatically import the data into each database.

I am hoping that someone could point me in the right direction.

Thanks in advance.

Ron

 

[Roger Carlson]

I would use low-level I/O to read one line at a time and parse the values
into individual tables. On my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ImportLineInput.mdb" which illustrates
how to do this. It doesn't do *exactly* what you need (no two import files
are the same), but the general principles are the same. For your's, you'd
want to test each line for the "H" or "D" and do something different
depending.

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=340

--
--Roger Carlson
MS Access MVP
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

 

[Jeff Boyce]

Ron

In addition to the approach Roger offers, you could import the text file
into a 'temp' table you've set up in Access that includes the maximum (?8)
number of fields that will be in the incoming text file.

The import process can append the records to this "table".

Then you could use queries to parse the information into a more normalized
data structure in your permanent Access tables.

If you want the whole operation automated, you could either create a
procedure that strings these pieces together, or use a macro to do the same.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP