Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vba code for excel to extract data from txt file
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vba code for excel to extract data from txt file
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" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vba code for excel to extract data from txt file
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vba code for excel to extract data from txt file
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. "JE" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create links to extract data from one excel file to another excel | Excel Worksheet Functions | |||
how do i extract data from pivot table to txt file | Excel Discussion (Misc queries) | |||
How do I extract an Excel Chart as a graphics file? | Excel Worksheet Functions | |||
Search/Extract Data w/in Text File | Excel Discussion (Misc queries) | |||
Code Post: Extract Trendline coefficients | Excel Discussion (Misc queries) |