Importing multiline files into Access 1

[swipes77]

I have an ASCII file in the following form:
Header (100 bytes)
Record 1 (100 Bytes)
Record 2 (100 bytes)
Record 3 (100 bytes)
Record 4 (100 bytes)
Trailer (100 bytes)
These 6 lines constitute a "Record" with Data Names for each byte position('s). There are several hundred records in this file. There is an "H" at the beginning of the "record set of 6".
I need to concatonate (Header - Record 1-4 - Trailer) into a single "Record" ie a flat file so I can deal with it in Access. How can this be done with Access macros, code, import spec, etc. Does anyone have any suggestions?

 

[BSman]

Make a table with one column of 100 bytes text to load the data into initially. Then load the table into a recordset and go through the recordset, taking each 6 records and getting the appropriate data from them and writing it to the correct destination table(s).

Or, if you don't want to fiddle around with the recordset, include an autonumber column as part of the interim table, then get one recod at a time using the autonumber column to get the records in the correct sequence, and process each record as you need so you can write the correct data into the correct destination table(s).

 

[dhookom]

Consider importing into a table with just one field named "FieldA". When prompted, allow Access to add a primary key field "ID". Save the table as "tblImport".

You can then create a query with SQL like:
TRANSFORM First(tblImport.FieldA) AS theValue
SELECT ([ID]-1)\6 AS RecordNum
FROM tblImport
GROUP BY ([ID]-1)\6
PIVOT ([ID]-1) Mod 6;

This assumes you have consistent groups of six.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.