Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can we extend the row limitation from 65536 to say 100K or higher
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. |
#2
|
|||
|
|||
Can we extend the row limitation from 65536 to say 100K or higher
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 "Mark" wrote in message ... 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. |
#3
|
|||
|
|||
Can we extend the row limitation from 65536 to say 100K or higher
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 (DataPivot 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 HIS "Mark" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Can we extend the row limitation from 65536 to say 100K or higher
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...b;en-us;120596 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Paul B" wrote in message ... 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 "Mark" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Can we extend the row limitation from 65536 to say 100K or higher
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... "Mark" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|