importing text files into access 2003

[geek]

Hi,
i'm now facing a problem of importing data from txt files into access
2003. here's the sample of how the text file looks like:

DATE :11/08/06
BUYER : M00000 S.A.S

ORDER NO. SAMS-SP-000/00) LOCA

TERMS/CURRENCY : 15th of next month / S$ or US$ or Yen
PAYMENT : 15th of next month
PACKING : CARTON CASE
PORT/DESTINATION: SINGAPORE
SHIPMENT : 4:BY SEA & ROAD
APPROVED BY : XXXXX
REMARK :

ITEM PARTS CODE MODEL NO. Q'TY DESCRIPTION
PRICE
1 8500030000206 see desc 5 GRILLE COVER I-CAPX-0002
0.30

May i know in this case, how can i make use of ACCESS to help me
transfer all these data into a table?

Thanks in advance!

 

[John Nurick]

Hi Geek,

Access's standard text import system doesn't work with a file like this,
and there's no easy way of doing this. The alternatives are

1) write Access VBA code that reads the file line by line, extracts the
data and appends it to a table

2) write code in your favourite scripting language to convert the file
into a standard format (e.g. CSV) that Access can import

3) use a third-party product designed for precisely this task, e.g.
TextPipe from DataMystic and Monarch from Datawatch.

 

[geek]

Hi there,
thanks for the help!

Hi Geek,

Access's standard text import system doesn't work with a file like this,
and there's no easy way of doing this. The alternatives are

1) write Access VBA code that reads the file line by line, extracts the
data and appends it to a table

2) write code in your favourite scripting language to convert the file
into a standard format (e.g. CSV) that Access can import

3) use a third-party product designed for precisely this task, e.g.
TextPipe from DataMystic and Monarch from Datawatch.

 

[Surendran]

Hi Geek,

Try the following threads posted in AccessMonster where in I have given
sample VBA codes for handling text files in various scenarios.

This post provides sample code for reading a single text file and also
multiple text files and insert in an Access table
http://www.accessmonster.com/Uwe/Th...m-from-text-file-into-access#6324e9c0f0e0duwe

This post provides sample code how to read a text file with delimiters like
(Pipe)!,tab,etc
http://www.accessmonster.com/Uwe/Th...SOME-fields-based-on-Primary#6322d8f56ebfauwe


This post provides sample code for reading multiple text files and append to
a new text file
http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/11352/Appending-Txt-files-coding