Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a text file before import - which language to use?
Somewhat off-track ... I don't know where else to post this.
I need to pre-process a potentially many-row text file before importing it. The text file has 2 header rows, followed by X-values, a line containing " " (hex 20) and then Y-values. I only need the header rows plus X-values. The first header row contains an integer representing the number of X-values. Can someone tell me the appropriate programming language to use to strip the blank line and the lines below it, then save the file with its original name? The pseudo code would look something like: count lines in file = result1 result2 = result1 - 3 (2 header lines plus blank line (contains hex20) between x and y values) result3 = result2/2 result4 = result3 + 2 delete rows whose number is result4 save file under original name I assume I can code VBA to pass the file name I am importing to this new program and then continue the import when done. This process will help until the count of X-values exceeds 65,534, at which time I will have to move the database to ACCESS or SQL Server. Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a text file before import - which language to use?
Larry -
I just use VBA for this. I open the file from within VBA, read it line by line, look for whatever patterns I know or suspect exist, and parse the lines into usable data, which gets dumped into the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Somewhat off-track ... I don't know where else to post this. I need to pre-process a potentially many-row text file before importing it. The text file has 2 header rows, followed by X-values, a line containing " " (hex 20) and then Y-values. I only need the header rows plus X-values. The first header row contains an integer representing the number of X-values. Can someone tell me the appropriate programming language to use to strip the blank line and the lines below it, then save the file with its original name? The pseudo code would look something like: count lines in file = result1 result2 = result1 - 3 (2 header lines plus blank line (contains hex20) between x and y values) result3 = result2/2 result4 = result3 + 2 delete rows whose number is result4 save file under original name I assume I can code VBA to pass the file name I am importing to this new program and then continue the import when done. This process will help until the count of X-values exceeds 65,534, at which time I will have to move the database to ACCESS or SQL Server. Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a text file before import - which language to use?
Hi Jon --
Thanks for responding. I used the line-by-line method successfully, but the president of the company, whose laptop is newer/faster than mine, says the processing is too slow. And, since the customer is always right .... Larry "Jon Peltier" wrote in message ... Larry - I just use VBA for this. I open the file from within VBA, read it line by line, look for whatever patterns I know or suspect exist, and parse the lines into usable data, which gets dumped into the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Somewhat off-track ... I don't know where else to post this. I need to pre-process a potentially many-row text file before importing it. The text file has 2 header rows, followed by X-values, a line containing " " (hex 20) and then Y-values. I only need the header rows plus X-values. The first header row contains an integer representing the number of X-values. Can someone tell me the appropriate programming language to use to strip the blank line and the lines below it, then save the file with its original name? The pseudo code would look something like: count lines in file = result1 result2 = result1 - 3 (2 header lines plus blank line (contains hex20) between x and y values) result3 = result2/2 result4 = result3 + 2 delete rows whose number is result4 save file under original name I assume I can code VBA to pass the file name I am importing to this new program and then continue the import when done. This process will help until the count of X-values exceeds 65,534, at which time I will have to move the database to ACCESS or SQL Server. Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a text file before import - which language to use?
You can read it all in or either in chunks into a huge string, then parse it
in memory then write it out. http://support.microsoft.com/support...eio/fileio.asp File Access with Visual Basic® for Applications -- regards, Tom Ogilvy "L Mehl" wrote in message ... Hi Jon -- Thanks for responding. I used the line-by-line method successfully, but the president of the company, whose laptop is newer/faster than mine, says the processing is too slow. And, since the customer is always right .... Larry "Jon Peltier" wrote in message ... Larry - I just use VBA for this. I open the file from within VBA, read it line by line, look for whatever patterns I know or suspect exist, and parse the lines into usable data, which gets dumped into the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Somewhat off-track ... I don't know where else to post this. I need to pre-process a potentially many-row text file before importing it. The text file has 2 header rows, followed by X-values, a line containing " " (hex 20) and then Y-values. I only need the header rows plus X-values. The first header row contains an integer representing the number of X-values. Can someone tell me the appropriate programming language to use to strip the blank line and the lines below it, then save the file with its original name? The pseudo code would look something like: count lines in file = result1 result2 = result1 - 3 (2 header lines plus blank line (contains hex20) between x and y values) result3 = result2/2 result4 = result3 + 2 delete rows whose number is result4 save file under original name I assume I can code VBA to pass the file name I am importing to this new program and then continue the import when done. This process will help until the count of X-values exceeds 65,534, at which time I will have to move the database to ACCESS or SQL Server. Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Import from Acess problems with SQL language | Excel Discussion (Misc queries) | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
how to write other language in text file from VBA | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |