How many rows in an xls file
December 20, 2009 3:29 AM   Subscribe

How to tell how many rows are in an excel file?

I've been sent an excel file that *should* be around 80k rows. Open Office Calc can only open 65,536 (2^16) rows. I need the file as a csv and I've not had any luck getting the person who sent the xls to resend the data as a csv yet and I need to get on with it today.

I've tried a couple of different conversion tools and they've only been able to produce 65,536 rows so I want to find out if that's because they're as limited as excel/OO or if there really are only 65,536 rows in the file.
posted by missmagenta to Computers & Internet (8 answers total)
 
Excel 2003 had the 64K rows limit. Excel 2007 allows 1M rows. Excel 2003 is still widely used, because it works fine and because Excel 2007 has a dramatically different user interface that many users have shunned.

Perhaps your converters are written with Excel 2003 in mind. Or someone saved the spreadsheet in Excel 2003 format, eliminating rows in excess of 64K.

Best thing would be for you to open the file in Excel 2007 and then export it.
posted by randomstriker at 3:42 AM on December 20, 2009


Up until the latest release, Excel could only handle '64k' (216 = 65,536) rows, which is probably why no tools will work with more then that.

You could try loading it into Google spreadsheets, otherwise you might have to get your hands on Office 2007.
posted by delmoi at 3:43 AM on December 20, 2009


Oh and OO can only handle up to 64K rows.
posted by randomstriker at 3:43 AM on December 20, 2009


Response by poster: Google Docs only allows uploads of spreadsheets up to 1MB. The file I have is 5.8MB. I don't have Office 2007 - obviously, else I wouldn't be having this problem.
posted by missmagenta at 3:53 AM on December 20, 2009


Best answer: There are free trial versions of Office 2007 and the Office 2007 Excel viewer might help, too.
posted by oxit at 4:12 AM on December 20, 2009


Response by poster: I opened the file in excel 2007 trial and it still wouldn't opened more than the 64k rows (I tried to add another row on to the end and it wouldn't) but resaving it and comparing file sizes does seem to confirm that there really are only 64k rows in the document.
posted by missmagenta at 4:54 AM on December 20, 2009


If you can trust someone else with the file feel free to memail me, I have both office 2007 and also a tool that can convert xls files to csv, and I'd be happy to give it a shot.
posted by RustyBrooks at 8:11 AM on December 20, 2009


If you open a Excel 2003 file in Excell 2007 it runs it in compatability mode, which basically treats it like a 2003 file. If this is happening for you then maybe that's why you don't see the extra rows. Try opening it as a 2007 file or making a copy and changing the file extension to xlsx (the 2007 extension) opening that. Something to remove the compatability thing.

But yeah, if it was saved as a 2003 or earlier file (.xls extension) then the extra rows are probably gone.
posted by shelleycat at 4:09 PM on December 20, 2009


« Older Help nip this middle aged spread in the bud.   |   What should I serve with my christmas Sea Bass? Newer »
This thread is closed to new comments.