Combining a 6 record "Record" into a flat file 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"
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, etc. Does anyone have any suggestions?

 

[BNPMike]

Does the ASCII file just contain one set of 6 records or does it contain 6 for one 'item' followed by 6 for the next and so on? Can you add a key to each of the 6 records eg an autoincrementing counter that shows they belong to the same 'item' eg 1,1,1,1,1,1, and some sort of record type id eg 1,2,3,4,5,6?. If you can do this you can build a series of joins on the item id and each time selecting on the record type. This would give you a single record with all 6 sets of data. This approach would require no coding within Access.

 

[p27br]

Is there a start/end of record mark in the file ?
maybe you could post a real sample ?

 

[swipes77]

The file contains several hundred records(a record being Header-Record 1-4 Trailer). The beginning of each record has a "H". the trailer has no ending marker. I wish I could provide a sample, but all I have is the file specification.
Thank you very much for your responses. I am supposed to get a sample in the next few days.

 

[grnzbra]

Bring them into an access table with one 100 byte text field. You now have them in a workable form. Open a recordset of this table and go to the first record.

When you say concatinate into a record, I assume you mean a 6 field record, since 600 bytes is too big for an Access text field. (you could use a memo field)

You could declare 6 string variables. Then loop through the variables, placing each loop into the next variable until Left(Header,1) = "H". (Actually, you'd need a hold variable which would then be moved to one of the 6, that way, you don't lose any of the 6 when the next header record is read.) Move each of the variables to the corresponding fields in the new table. Then go back to the loop and do it again.

Are these records each a single field or do they contain fields. If that is the case, you need to creat a field specification, but the idea is basically the same.

 

[swipes77]

Each of the 6 "records" have multiple fields. Initially I would have 6-100 byte fields after the concatonation process. How would you go about creating a field specification within the program. I guess I could export the file and re-import with a new import specification but that seems like a lot of work. I'm not an experienced programmer. Thank you very much for your help.

 

[grnzbra]

Asuming that there isn't an exact match between the fields of each record, you could simulate a spec using Left and Mid functions. So, let's say you have the header record. and you have a declared string variable for each field in the receiving table.
Variable1 = Left(rs1!OnlyFieldInImportTable, 8)
Variable2 = Mid(rs1!OnlyFieldInImportTable,9,5)
Variable3 = Mid(rs1!OnlyFieldInImportTable,14,25)
Variable4 = Right(rs1!OnlyFieldInImportTable,62)

Move the variables to the proper fields of the receiveing table's recordset.

Repeat process with next record in the import table. When you've gone through all six records, add a new record to the receiving table with the fields set equal to the corresponding variables.

rs2!Field1 = variable1
rs2!Field2 = variable2
rs2!Field3 = variable3
rs2!Field4 = variable4
etc.

Note: check HELP for the correct syntax of the Mid function. I don't know if the first of the two numbers is the starting point of the length)