"NathanB" <nathanbloomfield@hotmail.comwrote in
news:1157363287.201636.34310@m73g2000cwd.googlegro ups.com:
Quote:
Hi there,
>
I have a text file (flat file) which I would like to import on
a regular basis into Access. The text file contains 2 record
types, header (prefixed with RHD) and detail (prefixed with
RDT). Each recordtype has a unique structure.
--------------------------------------------------------------
-
Quote:
---------------- For example line one might read:
RHD123456ABCDEF
>
Characters 1-3 is record type, characters 4-9 is field X,
characters 4-15 is field Y
--------------------------------------------------------------
-
Quote:
---------------- Line 2 onwards are detail records for line 1:
RDT999ABC99
>
Characters 1-3 is record type, characters 4-6 is field X,
characters 7-9 is field Y, characters 10-11 is field Z.
--------------------------------------------------------------
-
Quote:
----------------
>
Is it possible to interrogate each line of the file to
determine the recordtype and subsequently import the fields
into a table using a predefined record structure?
>
I am looking for a solution which will work along the
following lines: 1. Check folder for interface file
2. Read file line x
3. Determine recordtype (RHD or RDT)
4. If recordtype is RHD then save each field (X,Y) in
associated variables
5. If recordtype is RDT then import to table using RHD
variables and RDT fields.
6. After successful import move file to "completed" folder
>
I have had experience importing deliminated files but never
attempted to import multiple record types in a single file.
>
I hope this makes sense.
>
Any help/advice will be much appreciated.
>
Regards,
Nathan
>
There are a couple of ways to do this. one is to import the file
into a temporary file with two fields, the first is an
autonumber and the second is the full line of text from the
import. You then build queries that parse the lines and move
tham to the proper destinations, using a lot of mid() functions.
The autonumber is used to keep references between the two
destinations; you can't set a relationship(1) because they
aren't equal, but you can use a whewre clause.
The other is to read the file using good old open() and get()
statements from primitive basic, They are still there in Visual
Basic.
(1) you can set the relationship if you write the sql, but not
if you use the query builder. A Where clause works well, just a
little slower.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from
http://www.teranews.com 
