Unable to show large number of records in excel

pbatra has asked for the wisdom of the Perl Monks concerning the following question:

In our application we have made a functionality to show the data in Microsoft Excel in addition to showing data on web page. On web page we manage to show large number of records at a time(say 20,000 records) but when we download large amount of data into excel(say 20,000) it gives the out of memory error.On the code side(perl) we are using an array to hold data to be displayed in excel. This is different from query timing out as query runs very fast. Please suggest how to handle it. Excel has limitation of 64K rows and 256 columns and in my case report data is much less than that.

Considered: Happy-the-monk What's Perl gotta do with it? - delete
Unconsidered: ysth - Keep/Edit/Delete = 17/0/23 - enough keep votes

Comment on Unable to show large number of records in excel

Replies are listed 'Best First'.
Re: Unable to show large number of records in excel by Corion (Patriarch) on Jul 05, 2004 at 12:10 UTC
You may be running up against limitations of Excel - Excel only allos 16384 rows of data, for anything larger, you are supposed to use Microsoft Access or any other database program. You should reduce the number of rows in your output until you hit the memory limit, to test out the exact number. You should also try it on a computer with more RAM and less programs running to verify that the problem happens at a specific number of rows.	[reply]
Re^2: Unable to show large number of records in excel by guha (Priest) on Jul 05, 2004 at 12:23 UTC
Excel only allows 16384 rows of data, for ... True for older versions, however Microsoft Excel 8.0 (97), Excel 2002 and Excel 2003 have a limit of 65,536 rows.	[reply]
Re^3: Unable to show large number of records in excel by Corion (Patriarch) on Jul 05, 2004 at 12:31 UTC
True indeed (for Excel 97-SR2): `Q:\>perl -le "print $_ for 1..16383" >list.xls Q:\>start list.xls Q:\>perl -le "print $_ for 1..32768" >list.xls Q:\>start list.xls Q:\>perl -le "print $_ for 1..70000" >list.xls Q:\>start list.xls # "Excel did not load the whole file"` [download] So the problem seems to lie elsewhere, like maybe not enough memory or other problems	[reply] [d/l]
Re^3: Unable to show large number of records in excel by Crian (Curate) on Jul 05, 2004 at 15:50 UTC
Yes, but Excel is still very buggy if you try formating with the help of filters on more than 16k rows (it crashes ragulary).	[reply]
Re^2: Unable to show large number of records in excel by pbatra (Initiate) on Jul 08, 2004 at 03:03 UTC
Excel has limitation of 64K rows and 256 columns and in my case report data is much less than that.	[reply]
Re: Unable to show large number of records in excel by dragonchild (Archbishop) on Jul 05, 2004 at 13:53 UTC
Exactly how are you showing this data in Excel? There are several different ways and they have their own limitations. ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply]