Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Import only certain rows of a huge text file
Hello, I try to open a 27MB text file (~ 495670 rows) and import rows 495662 - 496665 to an Excel sheet. Too many rows to handle with the Wizard. I would like to have a VBA sub to open file, fetch given rows, copy those to an Excel sheet and close text file. I have to do this for 300-400 text files that are numbered in ascending order but not necessarily continuously, that is 01,02,03,05,07 etc. Thanks for the help. Ari Bari -- AriBari ------------------------------------------------------------------------ AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043 View this thread: http://www.excelforum.com/showthread...hreadid=385663 |
#2
|
|||
|
|||
AriBari,
If there is a field that identifies the record numbers you want, try Data - Import external data - New database query. Presumably your text files are delimited, and have a header record with field names. As you go through that process, you'll make a query that can specify the range of record numbers you want. I haven't tried this with a large 64K file, but if your criteria is for <= 64K records, it might work. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "AriBari" wrote in message ... Hello, I try to open a 27MB text file (~ 495670 rows) and import rows 495662 - 496665 to an Excel sheet. Too many rows to handle with the Wizard. I would like to have a VBA sub to open file, fetch given rows, copy those to an Excel sheet and close text file. I have to do this for 300-400 text files that are numbered in ascending order but not necessarily continuously, that is 01,02,03,05,07 etc. Thanks for the help. Ari Bari -- AriBari ------------------------------------------------------------------------ AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043 View this thread: http://www.excelforum.com/showthread...hreadid=385663 |
#3
|
|||
|
|||
Hello Earl, Thanks for the recommendation, beacuse it brings up a point that I should calrify regarding my posting. The problem with your suggestion is that the text file is not a database. There are no field names or specific structure that is common to all rows. However there are sections within the textfile. The line that starts the section I want is only found in one place - so I can search for that line and say copy from that line the next 1005 lines into the Excel sheet. Hope this helps. Thanks, Ari Bari -- AriBari ------------------------------------------------------------------------ AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043 View this thread: http://www.excelforum.com/showthread...hreadid=385663 |
#4
|
|||
|
|||
AriBari,
I fear this is a bit more of a "How do I build a car engine" than "how do I start it" question. Well, perhaps I exaggerate a bit, but there isn't a quick answer. There isn't anything in Excel that does this automatically, and a macro won't exactly be a three-liner. A macro will have to determine the first file name, open the file, start reading it, looking for whatever it is that will identify the start of the rows you want. Then it must continue reading, putting the stuff into a sheet, keeping track of rows and columns, and, presumably, counting the records until it has reached the end of the section. Then it must close the file, determine the next file name, open the next file, and do all this again, putting the data in the sheet, or another sheet, or whatever is needed. Repeat, watching for the end of the files, or file names. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "AriBari" wrote in message ... Hello Earl, Thanks for the recommendation, beacuse it brings up a point that I should calrify regarding my posting. The problem with your suggestion is that the text file is not a database. There are no field names or specific structure that is common to all rows. However there are sections within the textfile. The line that starts the section I want is only found in one place - so I can search for that line and say copy from that line the next 1005 lines into the Excel sheet. Hope this helps. Thanks, Ari Bari -- AriBari ------------------------------------------------------------------------ AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043 View this thread: http://www.excelforum.com/showthread...hreadid=385663 |
#5
|
|||
|
|||
Hello Earl, I guess that wha I was afraid of. Clearly, I am trying to find a way to minimize mundane repetative tasks to analyze these text files, but i guess that is what i have to do. Thanks for the advice, Ari Bari -- AriBari ------------------------------------------------------------------------ AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043 View this thread: http://www.excelforum.com/showthread...hreadid=385663 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening text files that have more rows than 65536 | Excel Worksheet Functions | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
I have a Comma delimited text file how do I import in to excel an. | Excel Discussion (Misc queries) |