import a text file in to MS Access table

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I need import a text file in to mt table.
The file format is like following:

Name: MyName Address: MyAddress
Phone: 000-000-000-0000 Birthday: 1900/01/01
Title: My Title Company: My Company

Are there any easy way to do this?
I can read text file and parse the information and import to table or
import in to some kind of table and manage data to have column heading
as field name.

Any suggestion is great appreciated,
 
For a file of the structured format you describe (I understand each line to
be a separate record in the text file), I would use VBA and the classic File
IO statements to read the text file, find the data, separate it from the
labels, and store it, then write the record to an Access table when all the
data has been accumulated.

I don't believe the TransferText is quite up to the task, when it is
complicated by having multiple input records for a single Access record and
having the data fields identified by a label in the data itself.
 
Access said:
For a file of the structured format you describe (I understand each
line to be a separate record in the text file), I would use VBA and
the classic File IO statements to read the text file, find the data,
separate it from the labels, and store it, then write the record to
an Access table when all the data has been accumulated.

I don't believe the TransferText is quite up to the task, when it is
complicated by having multiple input records for a single Access
record and having the data fields identified by a label in the data
itself.
Click to expand...

VBA is best if you will do this a lot.
If it is a one time thing, then Word or a text editor can be used to convery
the file.
Work with a copy
WORK WITH A COPY!

Delete the first occurance of "Name:"
Search and replace Address:, phone: Birthday:, etc with a tab (^T)
Search and replace "Name" with a paragraph mark (^P)

Now you have a tab delimited file that is easy to import.
If there are already tabs the "|" characters is safe to use about 99% of the
time

That's the basic idea if there are tabs between name and address then just
get rid of the Address:
It goes faster than writing code most of the time but remember:

WORK WITH A COPY!
WORK WITH A COPY!
WORK WITH A COPY!
WORK WITH A COPY!

Not that I ever didn't...
 
 VBA is best if you will do this a lot.
If it is a one time thing, then Word or a text editor can be used to convery
the file.
Work with a copy
WORK WITH A COPY!

Delete the first occurance of "Name:"
Search and replace Address:, phone: Birthday:, etc with a tab (^T)
Search and replace "Name" with a paragraph mark (^P)

Now you have a tab delimited file that is easy to import.
If there are already tabs the "|" characters is safe to use about 99% of the
time

That's the basic idea if there are tabs between name and address then just
get rid of the Address:
It goes faster than writing code most of the time but remember:

WORK WITH A COPY!
WORK WITH A COPY!
WORK WITH A COPY!
WORK WITH A COPY!

Not that I ever didn't...- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks millions for the tips,
 
Back
Top