Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |