unix flat file import

[FlakyJake]

I am starting a project where my client wants a unix flatfile imported into access. The file contains 120,000 unique records.

The records are delimited and the following format.

3801111,,,,,,,,$3801111,,,,,,,,,$3801111,,,,,,,,,,.

Whereby the first record is clients name and addressetc then record delimited by $ then booking details with same client ID, then mailings with client ID. the first record is unique and only once, the bookings can be numerous ie more than one and so the mailings ie more than one. Then the process repeats for record 2 with new client Id.


What I would like to do is seperate the client, bookings, and mailings into seperate tables linked by the client ID.

what would be the best way to accomplish this task???

 

[KenReay]

Hi

from past experience your first problem may be that the records are not terminated with a CRLF pair, which is the norm in Dos text files, and what Acecss (and most other PC based applications) expects.

You may be able to overcome this by using a text editor to search and replace all $ with a crlf.

The next thing is you have two record types in one file, what I would do is to import the text file (after fixing teh crlf problem) into a single table. I would do this by writing code to read the text file and just transfer each record to a table where each row was a single text field, I would then write code to read through this table and parse each line into the relevant table.

It is not clear from the explanation thus far how one recognises each record type, I could not spot a 'record type' code in your sample data.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[FlakyJake]

Thanks,

There is no record type, the comma's are just placed there for simplicity, in real life they contain dates, email addresses, booking ref's etc etc.
The problem is after the $ that record mey be repeated.

Below is one row.



0000388,Active,MR,M,Ball,5 Wave Down,,WHITing,Sussex,so32 1EH,UK,01489 772885,SAME,,Aþ0000388,0105297,Firm,12469,10/08/02,OC,12470,12580,12584þ0000388,Broch BH98,0010884,05/11/97,11/03/98,OC,MAIL4,,Encl 99,,07/10/98,10/06/99,,99CLIENT,,,Broch BH99,0035504,04/11/98,10/11/98,OC,99BROC,,,,Encl MS,,04/06/99,04/06/99,,99C,,,,,Broch BH00,,17/11/99,17/11/99,,97R,,,,,,Encl 01,,31/10/00,31/10/00,,CMAIL3,,,,,,,Encl 02,,20/10/01,20/10/01,,CMAIL3,,,,,,,,Broch BH02,0096836,20/11/01,30/11/01,OC,2002BROREQ,,,,,,,,,Broch BH03,,21/11/02,21/11/02,,02C