Importing text file with uneven data

[YANECKC]

Importing text file problem
I have a text file that comes in daily where I want to import in excel.
The problem is I can not use import wizard because there are at least three
rows of information for one record with the different locations in the row.

A. The header row starts with "F57" in the first three characters 1-3.
Then the next important characters are from 9-12 which is ID field.
Last important characters for header record are from 13-21 which is cusip field.

B. The detail row starts with "F58" in the first three characters 1-3.
Then the next important characters are from 7-16 which is cusip field.
Last important characters for detail record are from 20-31 which is quantity.

C. The trailer row starts with "F59" in the first three characters 1-3.
Then the next important characters are "9020000000"
Then the next important characters are from 10-23 which is total field.

Is there any way I can create an import layout screen where I can tell
access when the row starts with "F57" the layout is A.
If row starts with "F58" the layout is B.
If row starts with "F59" the layout is C.

The detail row can be more than one, but there is only one header row
and one trailer row for each record.

See example of one record below.

F57 09025755J4918L17401072005
F58 0271997775112 00000022972600000000000000113GQBR
F58 0271997740710 00000023484600000000000000113GQBR
F58 0271997775518 00000030777500000000000000113GQBR
F58 0271997741015 00000213045200000000000000113GQBR
F590109020000000290279900000000000000000000600000004 000000000000000
==============================================
Is there a way if row starts with F57 then keep in memory "5755" and "J4918L174"
Now the next row starts with F58 so overlay "5755" and "J4918L174" at the end of row two.
Now the third row also starts with "F58" so overlay "5755" and "J4918L174" at the end of row three.
Now the forth row also starts with "F58" so overlay "5755" and "J4918L174" at the end of row four.
Now the fifth row also starts with "F58" so overlay "5755" and "J4918L174" at the end of row five.
Now the sixth row also starts with "F59" so overlay "5755" and "J4918L174" at the end of row six.
=====================================
The goal is to have output look like below


F58 1997775112 000000229726000000005755J4918L174
F58 1997740710 000000234846000000005755J4918L174
F58 1997775518 000000307775000000005755J4918L174
F58 1997741015 000002130452000000005755J4918L174
F59 9020000000 000002902799000000005755J4918L174


Thank you for your anticipated response.
Yaneckc@aol.com

 

[mdmilner]

Only way I know to do this is to:

1) Import all text as a single field
2) Open up the new table and 'parse' it.

What this means is, read the new table line by line to first "identify" what the line is, then write a new table spreading out the data items into fields.

So, follow your rules below and look at the first few characters where F58/F57, etc are, and use that to figure out what a row is



Mike

 

[YANECKC]

After I create a new table how do I parse it ?
The size of the text file some days are extremely large.
If a macro had to be create to indentify every field "line by line".
what would it be ?

Yaneckc

 

[mdmilner]

Wish I had more time to explain, but, line by line means you're going to have to use VBA and read a line ( a record) identify it, break the line apart into all it's component pieces (which group of characters is part of what field) and then save it somewhere else (into a new table)

A macro, by itself, won't do this.

Mike