Open Office org Calc (spreadsheet): limit of 64k rows?

views:

1574

answers:

+1 Q:

Open Office org Calc (spreadsheet): limit of 64k rows?

I was using a not-so-up-to-date version of OOo.calc (Open Office Spreasheet component, version 2.04, default for RHEL 5), and bumped into a nasty surprise. When trying to import a csv file (with about 100k row), it said that there is a limitation of max 64k rows, and that remaining rows would just be discarded. I was planning to have users use OOo for client-side processing of large sample sets, to be uploaded to server for processing (and possibly returning results as a spreadsheet), but such a limitation would effectively kill that approach.

Does anyone know if a later versions might have lifted this limit? If not, I will need to consider alternative spreadsheet apps; so if anyone knows of good alternatives beyond Excel (which I am aware of :) ), feel free to share.

EDIT: after downloading and testing OOo 3.1, I found out that unfortunately this limit still exists in its original form. OOo spreadsheets can not have more than 65536 rows, even with version 3.1

+1 A:

No, this is a pretty common limit. Excel 2007 was the first version of Excel to increase this limit to about 1 million. Quattro Pro can handle about a million as well.

With what you're talking about, though, a database would be a better approach.

Don 2009-07-27 18:34:09

Thanks for the comment, but perhaps my explanation of the use case was inadequate. DB is not an alternative here -- data itself will be stored in a DB (probably S3, not a RDBMS, but that's an implementation detail).But for client interface spreadsheets have their uses, for mass editing things; rich functionality for sorting, manipulating; ability to work offline and so on.And by lifting, what I did mean was to increase it to be something more palatable than tiny 64k. :)(not to necessarily make it unlimited)

StaxMan 2009-07-27 19:02:33

+1 A:

According to Wikipedia, the limit is still 64K rows. In case you are leery of Wikipedia, that article does have a link to an OpenOffice.org FAQ, which (1) does corroborate the 64K figure, but (2) is marked as obsolete, and automatically redirects you to the general FAQ wiki. Make of that what you will, but as tesseractor points out, Excel 2007 has a vastly higher limit. (This of course does not help you if you must use RHEL.)

John Y 2009-07-27 19:16:19

Thanks. I think wikipedia is right here, alas.

StaxMan 2009-07-27 21:47:21

This is something I've encountered with folks in our marketing department who use Excel (pre-2007, clearly). One solution is to simply divide the CSVs into 64K or fewer line files. A CSV file is easily divided according to lines with a few bits of script. Then your end users can import the CSV files into different workbooks in each spreadsheet.

It might not catch all your use cases, but it's worked OK for us so far.

shawnr 2009-07-27 19:21:09

Thanks. Yes, splitting works for some cases, and being line-oriented format it's easy to automate.

StaxMan 2009-07-27 21:46:50

If I split the data, how to pivot 78000 rows?

Novice OOo user 2009-11-13 01:29:46

+1 A:

This article on the OpenOffice wiki says:

As of DEV300_m84 the number of rows was increased to 1048576 (1M).

Regard this article as historical...

So, maybe grab a dev version of OO.org and try it out? The DEV300_m84 version number seems like it would be 3.0.0, but it's not working for me in 3.1 either.

wesgarrison 2010-10-10 19:57:29

ansaurus

tags:

views:

answers:

Open Office org Calc (spreadsheet): limit of 64k rows?

related questions