Can we extend the row limitation from 65536 to say 100K or higher

[Mark]

I wish to import data into Excel that is typically 90K rows long and could go
to as much as 900K. # of Columns is only say 20 or so.
is there any way to remove the 65536 row limit so the whole data set can be
imported at once?
Alternatively, is there some snippet of code or idea to import the data into
multiple sheets?
Many thanks for any advice.

 

[Paul B]

Mark, that is the limit, next version will be more, have a look at this code
here and see if this will help

http://www.mvps.org/dmcritchie/excel/snakecol.htm
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Nick Hodge]

Mark

Often users are doing something else with the data afterwards, i.e.
summarising, filtering, etc. After all it is impossible to 'read' 900k
records/rows.

If the above is true then consider bringing the data into Excel from the
external source as a pivot table (Data>Pivot table or pivot chart report...)

I regularly bring in 500k records this way, summarised to a few rows (You
can currently have up to around 16k)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[David McRitchie]

Hi Paul,
thanks for the reference but my snakecol.htm page does not address this issue
The information about more columns and rows in Excel 12 (Vista) has been
revealed by David Gainer in http://blogs.msdn.com/excel/archive/2005/09.aspx
the Excel 12 grid will be 1,048,576 rows by 16,384 columns.

Hi Mark,
Importing text files larger than 65536 rows
In Excel 97 and all later versions, text files that contain more
than 65,536 rows cannot be opened in their entirety. You cannot
open these files because these versions of Microsoft Excel are
limited to 65,536 rows. If you open a file that contains more data
than this, the following error message appears, and the text file is
truncated at the row 65,536: File not loaded completely.
the above description and a macro to help work with such files is in
http://support.microsoft.com/default.aspx?scid=kb;en-us;120596

 

[Marc]

Depending on what you want to do with the data, you might think about
importing it into Access, then using an Excel database query to pull
information. I don't know the row limit offhand in Access...