Import Interface File

[NathanB]

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.
-------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
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.
-------------------------------------------------------------------------------

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

[Bob Quintal]

Re: Import Interface File

"NathanB" <nathanbloomfie ld@hotmail.comw rote in
news:1157363287 .201636.34310@m 73g2000cwd.goog legroups.com:

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.
--------------------------------------------------------------

-

---------------- 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
--------------------------------------------------------------

-

---------------- 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.
--------------------------------------------------------------

-

----------------
>
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