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.
--
Overcome Sales Barriers
http://salesbarriers.typepad.com/
" wrote:
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 ?
Kooster wrote:
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 a
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.
--
Overcome Sales Barriers
http://salesbarriers.typepad.com/
" wrote:
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..