import text - combine lines..

[dmeehanjr]

Greets!
I have a text file that for some reason was formatted as follows(for example):

FirstName LastName
John Smith
Bob Dole
Jay Jones
DOB Address
5/12/91 12 smith ave
6/10/10 40 union pike
3/1/00 18 hun st.
Zip
11111
22222
33333

...so DOB should follow Lastname, and zip should follow Address.
FirstName LastName DOB Address Zip
John Smith 5/12/91 12 smith ave 11111
Bob Dole 6/10/10 40 union pike 22222
Jay Jones 3/1/00 18 hun st. 33333

Is any way to stitch something like this together?
thanks!

 

[plog]

If I understand you correctly you essentially have a text file with 3 sections: Name data is at the top of the file, then a section for DOB/Address, then a section for Zip Code. Right?

If so, this is what I would do--I would copy that file 3 times, name one file 'NameData' and delete the DOB/Address and Zip Code sections, name another 'DOBAddrData' and delete the Name and Zip code sections, name the last file 'ZipData' and delete the Name and DOB/Addr sections. Next I would import each file into a table with a name that corresponds to the file name being sure to have Access add an autonumber primary key to each table.

This will give you 3 tables (NameData, DOBAddrData, ZipData) from which you can create a query linking each table by the ID number assigned to each by Access. These ID numbers should correspond correctly, but I would spot check a few to make sure they do. Once they do, make a table using that query.

 

[Galaxiom]

I would do this by reading the text file a line at a time in a loop and using logic to write the values to the appropriate tables via recordsets.