Text file processing

Hi All,

I am trying to import a text file into a database and am having difficulties with how the text file is storing the data, example below:

---Text file start---

Name:
Joe Bloggs

Age:
21

Address:
Somewhere

Name:
Bob Builder

Age:
65

Address:
anywhere

---Text file end---

This is how the records are stored, but what i need to do is pull both of these records out of the text file and insert them into an access database. The problem is that when I try standard imports, they do not pick up the field names and import the text file as a single list as it appears in the text file.

Any ideas on how to import this using a tool or with code appreciated.

dagoose:bigyello:

Here is some from one of my systems where I import data from a text file.

rs.Open "payrolltransactionhistory", Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable
Open "z:\PR\PAYROLLTRANSHISTORY.csv" For Input As #2
Do While Not EOF(2)
Input #2, EMPLOYEEID, TRXBEGINNINGDATE, TRXENDINGDATE, UNITSTOPAY, PAYRATE, PAYROLLCODE, PAYROLLRECORDTYPE, UPRTRXAMOUNT
rs.AddNew
rs![EMPLOYEEID] = EMPLOYEEID
rs![TRXBEGINNINGDATE] = CDate(TRXBEGINNINGDATE)
rs![TRXENDINGDATE] = CDate(TRXENDINGDATE)
rs![UNITSTOPAY] = CSng(UNITSTOPAY)
rs![PAYRATE] = CSng(PAYRATE)
rs![PAYROLLCODE] = PAYROLLCODE
rs![PAYROLLRECORDTYPE] = PAYROLLRECORDTYPE
rs![UPRTRXAMOUNT] = CSng(UPRTRXAMOUNT)
rs.Update
Loop
Close #2
rs.Close

Export a sample database to a textfile first so you'll know the format necessary to import using the standard thingy.

Otherwise, you have to parse the file into a format you can use to save to the database.