Copy text with more than 65,536 lines into Excel

[J@Y]

I have a text file with about 5 million lines.
Question1: Do I have to break down the text file into sections of 65,536
lines?
Question2: How can I get Excel to copy the contents of the text file on a
sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

 

[Pops Jackson]

Depending on how many columns are involved, you are ging to have such a
monstrously large file it may not be manageable.
You will have to split the data into no more than 65,536 rows to paste onto
each sheet. What kind of data source is supplying these 5 million lines?

 

[J@Y]

Its a text file. it's about 130 mb.
The columns are not a problem.

 

[Bernard Liengme]

Chip has done this for you; see his page at
http://www.cpearson.com/excel/ImportBigFiles.htm
best wishes

 

[Dave Peterson]

Microsoft provides a way to import files that won't fit on a worksheet.

But have you thought about what you're going to do with the data after you
import it to 77 different worksheets?

It doesn't sound like it would be a nice thing to work on in excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596
XL: Importing Text Files Larger Than 16384 Rows
(written for xl95, but has a note to change stuff for xl97+)

ps. xl2007 supports 1MB rows per worksheet, but that sounds just as bad to me.
I have no idea what you're doing, but are you sure excel should be the
application?

 

[Jim Cone]

Since you insist, but chew well before swallowing...
http://support.microsoft.com/kb/120596/en-us
"Text files that are larger than 65,536 rows..."
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"J@Y" <[email protected]>
wrote in message
I have a text file with about 5 million lines.
Question1: Do I have to break down the text file into sections of 65,536
lines?
Question2: How can I get Excel to copy the contents of the text file on a
sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

 

[J@Y]

I need to columnize the data afterwards in Excel and then run Vlookup like
searches through them, any suggestions as to how I can do it without Excel?

 

[J@Y]

I need to columnize the data afterwards in Excel and then run Vlookup like
searches through them, any suggestions as to how I can do it without Excel?

 

[Jim Thomlinson]

Do you have MS Access or a similar database program. Using VLookups is going
to grind your XL to a halt and very possible crash the application. Access or
suh will easily handle millions of rows of data in an very efficient manner.

 

[Dave Peterson]

I can't imagine running up to 77 different versions of the =vlookup() through
64k rows would be able to done effectively.

I don't use Access, but it sounds like (from Jim Thomlinson's note) that it may
be time for you to try.