SpreadSheets rows limit
First, I don't undestand why excel (or others spreadsheet softwares) have a number of rows limit. My PC has 512MB ram, but I'm limited by this. Sad :(
"First, I don't undestand why excel (or others spreadsheet softwares) have a number of rows limit."
If you're running into the size limitations of a spreadsheet, that's generally an indication that a spreadsheet is the wrong tool for the job. It's like trying to cut railroad ties with a hacksaw. Spreadsheets are designed to do the sort of things that people normally do with spreadsheets. People don't normally go anywhere near the 65000 row limit. Something like MATLAB, IDL, Octave, or even ordinary C/C++/Fortran code would likely be a better fit your purposes.
First, try opening a data source where you keep the data in a Access table or MSDB (free SQL Server) table. That way you can still do pivot tables on it.
There are APIs that allow you to query csv files as if they are database tables, so you could use SQL. I know perl and python have libraries available.
Li-fan Chen, what is wrong with using Excel as a manipulation tool? I know it won't work for his current problem of >65K rows, but Excel is excellent for quick manipulations of data. I even use it to write repetitive code.
It depends on what you want to do after you've gotten the data into a program, but our scientific graphics program (GraphPad Prism) allows an essentially unlimited number of rows. You can get a 30 day demo from:
If you have that much data either dump in in excel, which is the better tool anyway, or throw it on a Unix box and use Sed and Awk.
Oops, meant to say dump it into access
re: If you have that much data either dump in in excel, which is the better tool anyway, or throw it on a Unix box and use Sed and Awk.
I ran into this limitation the other day when I tried to import an IIS log. I wanted to use the filtering and sorting features on it but it exceeded the 64k row limit. I ended up writing a script to prefilter out some of the events that I knew I wasn't concerned with.
Using a canoe to transport people across the Atlantic is feasible but not practical.
Unfortuantely OpenOffice is worse than excel, the row limit is 32768. I usually use awk for simple calculations on csv files.
With the full 64k rows in use, Excel responds instantly for me. It's very quick and easy to drill down to the rows I need using the active filter feature. It also has no problem handling a formula extended throughout all rows. I'd hardly consider it impractical.
Thanks Mike. I didnt know about awk. One 30 minute course on it and I think I get _the_ solution. Great tool.
Augusto, I know a little Sed and am just learning awk myself. We do use Sed in production, on Windows, here at my work. We use it to run over some .csv files and replace characters and whatnot, then the file is later imported to SQL Server.
The column limit is even more severe.
i like i
(To explain how severe the column limit is, my dataset was a 766x766 grid of scores. Could I load it? Could I hell.. grrhhh)
i like i
Fog Creek Home