Import only certain rows of a huge text file

A

AriBari

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
 
E

Earl Kiosterud

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

AriBari

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
 
E

Earl Kiosterud

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

AriBari

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
 
Top