When importing a fixed width text file, a specification telling Access where the fields begin and end is used. However, some files have fields that change depending on the value in one of the fields. Is there any way to get access to handle this kind of situation?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Variable file specs
- Thread starter grnzbra
- Start date
JerryKlmns
IS-IT--Management
Is it a delimited file that you are referring to??
How about an example , just few lines from that file.
How about an example , just few lines from that file.
- Thread starter
- #3
No, it's not delimited. You have a specification that says
KeyField 5
LastName 15
FirstName 10
Address 25
City 20
State 2
Zip 7
There are no delimters. To import this thing, go through the beginning process of importing a text file. When you get to the first dialog form after selecting the file to import, instead of going ahead with the normal process for importing a delimited file, click on the ADVANCED button. It will display a grid into which you enter the field names, the field lengths and the start positions of the field. This is the specification which is given a name (we usually used TABLENAMESpec for the Spec name.) This is the SpecificationName used in the TransferText command.
The problem is, on a mainframe, there can be several field layouts in use in the same table. The computer looks at the layout field and uses the record layout for whatever value is in the layout field.
Is there any way to set up a series of specs and, for record, select the appropriate spec?
KeyField 5
LastName 15
FirstName 10
Address 25
City 20
State 2
Zip 7
There are no delimters. To import this thing, go through the beginning process of importing a text file. When you get to the first dialog form after selecting the file to import, instead of going ahead with the normal process for importing a delimited file, click on the ADVANCED button. It will display a grid into which you enter the field names, the field lengths and the start positions of the field. This is the specification which is given a name (we usually used TABLENAMESpec for the Spec name.) This is the SpecificationName used in the TransferText command.
The problem is, on a mainframe, there can be several field layouts in use in the same table. The computer looks at the layout field and uses the record layout for whatever value is in the layout field.
Is there any way to set up a series of specs and, for record, select the appropriate spec?
JerryKlmns
IS-IT--Management
You are saying that record layout is not the same for all records in that file???
Sorry, but I think it 's either fixed length or delimited.
Could just paste 3-4 lines of that file??
Sorry, but I think it 's either fixed length or delimited.
Could just paste 3-4 lines of that file??
- Thread starter
- #5
I don't have the file. It was a job I couldn't figure out how to do so they took it back. I can no longer connect to that file.
Typically you will have a header record with one record layout, a detail record with another layout, and a footer record with a third layout. It is a sorted table with one header record followed by the associated detail records followed by the associated footer record.
I would like to be ready if anything like that ever shows up again.
Typically you will have a header record with one record layout, a detail record with another layout, and a footer record with a third layout. It is a sorted table with one header record followed by the associated detail records followed by the associated footer record.
I would like to be ready if anything like that ever shows up again.
Hi
I have don ethis kind of thing before for reasons similar to those you give ie importing legacy main frame data,
Not saying mine is the only (or best) way to do it, but what I did was to import the data as fixed width data with just two columns (eg (say) the record type Cols 1-3 and the rest of the data in one chunk col 4... whatever). I imported this into a table, then depending on the record type in cols 1-3, I broke the remaining single chunk up into individual columns using left, mid, rioght functions
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
I have don ethis kind of thing before for reasons similar to those you give ie importing legacy main frame data,
Not saying mine is the only (or best) way to do it, but what I did was to import the data as fixed width data with just two columns (eg (say) the record type Cols 1-3 and the rest of the data in one chunk col 4... whatever). I imported this into a table, then depending on the record type in cols 1-3, I broke the remaining single chunk up into individual columns using left, mid, rioght functions
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #7
Thanks.
That's the way I ended up trying to do it. However, I told them that it was good for the given specs. If any specs changed, it would have to be re-done again. I was hoping that there would be some way to create multiple specs that could be changed in a grid.
That's the way I ended up trying to do it. However, I told them that it was good for the given specs. If any specs changed, it would have to be re-done again. I was hoping that there would be some way to create multiple specs that could be changed in a grid.
earthandfire
Programmer
Presumably the header/footer records will contain information such as filename, date and time and possibly a record count or a total of one of the fields for checking purposes.
If this is the case, the solutions that I have used are:
1:
The header and footer records are not required - rewrite the file:-
Read the first line
Enter a loop read a line, if not at end of file write the line into a new file for the data records.
2:
The header and footer records are required - rewrite the file:-
Read the first line, write it to a file for header/footer record
Enter a loop read a line, if not at end of file write the line into a new file for the data records.
If after reading the line end of file has been reached this is the footer record so write this line to the header/footer file instead.
Import the new data file using the Access import fixed with dialogs.
Process the header/footer file (if created) as needed.
Hope this helps.
If this is the case, the solutions that I have used are:
1:
The header and footer records are not required - rewrite the file:-
Read the first line
Enter a loop read a line, if not at end of file write the line into a new file for the data records.
2:
The header and footer records are required - rewrite the file:-
Read the first line, write it to a file for header/footer record
Enter a loop read a line, if not at end of file write the line into a new file for the data records.
If after reading the line end of file has been reached this is the footer record so write this line to the header/footer file instead.
Import the new data file using the Access import fixed with dialogs.
Process the header/footer file (if created) as needed.
Hope this helps.
Hi
If the layout or the incomming records is likely to change, you could consider having a table which contained the record laout of each record type eg something like
tblLayout
RecType
sFieldName
nStart
nLength
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
If the layout or the incomming records is likely to change, you could consider having a table which contained the record laout of each record type eg something like
tblLayout
RecType
sFieldName
nStart
nLength
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Status