Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Importing parts of a text file into Excel

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..

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default Importing parts of a text file into Excel

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..


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Importing parts of a text file into Excel

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..



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 284
Default Importing parts of a text file into Excel

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


wrote in message
ups.com...
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..



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default Importing parts of a text file into Excel

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..




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop excel from dropping the 0 in the beginning of a number? Rosewood Setting up and Configuration of Excel 12 April 4th 23 02:12 PM
Weird File Open/Save As Behavior [email protected] Excel Discussion (Misc queries) 0 December 9th 05 03:26 AM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Exporting excel to text file Exceluser Excel Discussion (Misc queries) 1 June 22nd 05 11:26 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 10:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"