Importing parts of a text file into Excel

[ymcasatx]

Hi everyone,

I'm trying to import a text file that is in the following format:
------------------------------------------
Bunch of Junk
------------------------------------------

R01 27 $99.00 DOE, JOHN 000000000 1234567890
028-000058927 (7713021)
R01 -REASON UNKOWN -


R08 27 $99.00 DOE, JANE 000000000 1234567890
033-000064075 (7713057)
R08 -REASON KNOWN 4443 -



R01 27 $99.16 DOLL, BETSY. 000000000 1234567890
033-000075124 (7713131)
R01 -REASON UNKNOWN -
-----------------------------------------
More Junk
-----------------------------------------

I need to import the data in the example with the following criteria:
- ONLY lines with R01 (so the R08 line would be skipped)
- Need the amount in a column
- First and last name (separated)
- The routing numbers (the 9 digit number that is zeroed out)
- The account numbers (variable length)
- The 13 character string that is below the name

I've looked around and think that macros are the best way to do it.
problem is that I have no understanding as to where to even start.

so anyone that could help me with this I'll greatly approciate it !

Thank you for all your help..

 

[Kooster]

From experience, I believe you will run into some exceptions in the source
file, CR/LF, FF, etc. This will throw an extra row, push a field, etc. which
could make a macro go bust.

The better method is to use a data mapping tool like Monarch. Go here for
more info: http://monarch.datawatch.com/monarch-standard.asp

What's the source application that gens the file? ERP? The other options are:

1) Have them script a new report
2) Use an ODBC driver, or Excel's data access, to hit against the source
database and extract only what you need
3) Use a report writer or MS-Access to hit against the source database and
extract the data

P.

 

[ymcasatx]

thanks for the reply!

The source file is a TXT file that is downloaded from a website, the
junk described in the post is basically superfluous text in a report
style (listing page numbers and so on) it's our only access to the
data.

there are about 500 lines of data, mostly irrelevent there's probably
100-200 of the lines that need to be filtered out on a monthly basis.

The interesting thing is that our developers use Monarch for another
project we're working on. (migrating green screen to .NET) so it's
pretty suprising to consider it for this type of application.

do you think that doing a text stream and looking for the R01 code and
do the CR/LF functions from there would work ?

 

[Steve Yandl]

You should set a reference to the scripting runtime and use the
FileSystemObject to read the text file one line at a time. You can use the
InStr function to test each line for the "R01" at the very beginning. If
R01 is found, use the Split function to create an array out of the text in
that line and populate the next row of your worksheet with the parts you
want to record.

Steve Yandl

 

[Kooster]

Monarch should be able to easily handle this and be a better long term
solution. Best of all, there is no programming (macros) to maintain. And, if
the Web site changes their formatting, Monarch can be changed easier than
Excel macros.

P.