Tuesday, December 23, 2008

Spreadsheets Maximum number of Rows

I had a spreadsheet (Excel sheet for you MS Office users) with over 253,000 entries. Since I run on Linux, I use OpenOffice.org's SpreadSheet program, which unfortunately supports a maximum of 65336 rows only.

A quick search got me to a comparison between multiple products that support more than that limit. OK, I lied. Two products only.

This Wikipedia page shows a nice table but people tend to overlook the tiny little IMPORTANT numbers used for special notes!!!

The products are: Microsoft Office 2007 and Gnumeric.

Unfortunately (yes, another unfortunate event), Gnumeric requires that you recompile it with max number of rows you desire. This can be a good thing if you look at it from the point that you can surpass the limit that Microsoft is boasting.

In all cases, opening such large sheets requires large memory. I'm guessing at least 1GB (for the data itself, not counting the program!). I'll test this claim on MS Office 2007.

6 comments:

Nemo said...

253,000 rows in a sheet? why?

MBH said...

In my case, I had exported a list of all the system transactions from our ERP software.

In the finance department case, they have a lot of calculations done over the years in a single sheet.

Nemo said...

hmmm

even finance here they always extract to excel, i think if the data are in the DB then that's enough?

or

like what they do here .. they divide the data like monthly and store them for every month in excel sheets .. better that storing the whole thing in one file .. too much

MBH said...

I hate to admit, but we don't have a finance management software in the company (we're implementing it as part of the ERP).

So for now (and since ages), they're relying on doing everything by hand on Excel......

well-knownQ8 said...

253000 / 65336 = 4

so divide your entries into four sheets and voila ;)

MBH said...

This doesn't work in case you need to do calculations on ALL the data.

I already exported the data to a text file instead of an Excel file, and will run a quick command line (on Linux) to filter it in the way I want it.