vba code for excel to extract data from txt file

[JE]

USING Excel 2000.
I have a text file that when viewed in excel lists the entire row of data in
one cell. I cannot parse data because each line does not have the same
break. I want to extract the fields into individual cells. I will need to
define "what" I am looking for. Is there sample vba code for excel I can
reference to learn how this may be done?

sample report

REF #:A1B CUSIP:12345678
TD:1/1/00 SYMBOL:ABC QTY: 1
SD:01/03/00 ACME COMPANY PRICE: 9.101
MKT:OTC


want to extract

REF# TD SD CUSIP MKT SYMBOL FIELD1 QTY
PRICE PRICE
A1B 1/1/00 1/3/00 12345678 OTC ABC ACME COMPANY 1
9.101

 

[JLatham]

JE,
Is your sample report example shown here exactly like the real deal? Each
record has 4 rows:
1st row starts with Ref #:
2nd row starts with TD:
3rd row starts with SD:
4th row starts with MKT:

and there is no additional information on each row other than what you've
shown here? What about the rest of the report - are there blank lines or
such between the 4-row records, although my method doesn't really care, still
nice to know.

I perform similar parsing on variable sized fields in a text file output by
an accounting system for personnel records. I have an array that holds the
text that defines a field, such as REF #: and CUSIP: and it uses that
information to pick up the data within the fields in the records.

If what you've shown is accurate, we can put together something fairly
quickly.

 

[JE]

JLatham,

Thank you for the response. The sample report is not the exact report.
Each record has varying rows. The record begins with the same text:
RR:
ACCOUNT: 12345533 SHORT NAME: DOE

Each record then has 1 to many sub records. These records contain 6-9 rows.
There are 4 columns in the printed report. Certain fields consistently
appear (e.g., row 1 begins with REF #) but others are not consistent (e.g.,
comments).

I first attempted to parse using the MID function with fair/poor success.

I apologize if this is not clear. This request may be beyond the intent of
this discussion group.

 

[JLatham]

It probably is beyond the scope of this forum. However, I hate to see
someone suffer needlessly too often. If it is possible for you to email me a
couple of sample files (2 is better than one because it gives me a chance to
see more variations in the data layouts), I'll take a look and give you an
idea of whether or not it is doable, and or whether or not I'm willing to
tackle it.

If it is possible for you to do that, then email .txt files as attachments
to (remove spaces) HelpFrom @ jlathamsite.com
and I'll take a look at it this evening. If we can consistently determine
where a record starts, and what the various fields you need to capture are
delimited by (and if that's consistent) then it should be possible. Even if
they're not in order - we just keep a counter that tells us how many fields
we've found and when it reaches the required number, then we know we're done
with that record, we process the information (place it into excel sheet),
reset the counter and grab the next record. That kind of deal.

The entries that may offer a challenge are going to be the one like ACME
COMPANY (your Field1, I believe) since it doesn't seem to have explicit
delimiter, but is dependent on the end of the SD field before it to define.
Not insurmountable, but another case of really needing to see the real-world
data to deal with.