record spanning more than one line

[Guest]

I used to use ACL for importing complex data table (motly reports). These
records typically span across two-three lines. I am not able to import these
files to access 2003 through 'Import Text Wizard'. Sample of imput file is as
below:

Supplier Type: All
Payment Start Date: 01-MAR-05
Payment End Date: 31-MAR-05

Supplier: X.Y. ENTERPRISES
Number: 1234

Site: XYZ
Address: No.31/1, ABC,, DEF, CAL, 60048

Payment
Account Name Payment Number Payment Date Currency Payment
Amount Functional Amount Void Date
------------ -------------- ------------ -------- -----------------
----------------- ---------
ABN Dinar Cu 87084 25-MAR-05 DIN 6,998.00
6,998.00

Invoice Number Invoice Date Invoice
Currency Invoice Amount Amount Paid
------------------ ------------
----------------- ----------------- -----------------
I/1271/21-MAR-2005 21-MAR-05 DIN
6,667.00 6,667.00
I/1304/25-MAR-2005 25-MAR-05 DIN
331.00 331.00


-----------------
Site Total:
6,998.00

-----------------
Supplier Total:
6,998.00

I want to bring in many important fields like , supplier no, supplier
address, invoice no, invoice amount, payment date, site total, supplier
total, etc into my access table. Pl note that against one supply line item,
multiple payments are possible and one payment can cover multiple invoices.

Can anyone help me out. Thanks in advance.

 

[John Nurick]

Hi,

Access's built-in import tools can't handle data like this without a lot
of programming. The approaches are basically

1) Use commercial software (e.g. ACL, Monarch Data Pump, TextPipe Pro)
specifically designed for parsing report files and putting their data
into "proper" tables.

2) Write a script or program (using a langauge, such as Perl or Ruby,
with powerful text-handling features) that converts each input file into
one or more text files in conventional CSV format ready for import to
Access. (These language can also use OLE to insert the data directly
into your tables.)

3) Write Access VBA code to do the same.