only 16383 rows in Spreadsheet::ParseExcel

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

Hallo,

I want to use your Spreadsheet::ParseExcel to read Data from an 8MB excel file to store them into an cvs-file (on a Linux-PC).

Everything works fine unless the file is too big. Open Office only manages to load 32000 Rows instead of all data of the sheet, while Spreadsheet::ParseExcel only loads 16383 lines of the data sheet and so I miss quite a lot.

Is there a possiblity to get every thing?

I tried to find out myself by looking up the code, but from my first glance I couldn't see where a limitation is set

Thanks for all your effort,
Carl

Comment on only 16383 rows in Spreadsheet::ParseExcel

Replies are listed 'Best First'.
Re: only 16383 rows in Spreadsheet::ParseExcel by bart (Canon) on Apr 16, 2005 at 14:10 UTC
I have no idea on whether it's actually possible... but I doubt it. This was originally a limitiation in an old version of Excel, I think it was Excel 5.0/95. So more rows than that is actually stored internally in the file in an incompatible way. Just like the old limitation of cells to 255 characters, you occasionally get weird results if you have longer text in one cell, simply because there are several ways to store the same data in the spreadsheet. I guess that if it was simply possible to get past the 16383 rows limitation in Spreadsheet::ParseExcel, that it then would support it transparently, without any action required from your behalf. And that's why I'm guessing that it is not possible. Not with the version of the module you have. Perhaps in the future... But actually, it's just a guess.	[reply]
Re^2: only 16383 rows in Spreadsheet::ParseExcel by Anonymous Monk on Apr 16, 2005 at 16:58 UTC
Hi and Thanks, 1st it is - I think - because of the old Excel-row-limitation. 2nd, I don't have Windows, so an Excel-export is not possible. 3rd, Finally I load the file into Gnumeric (all 65500 rows), but exporting to text ot csv did work, so I 4th, copied (ctrl-c -> ctrl-v) all in several slices to an text editor and saved that. So I've found a solution but it make me believe that computers are s.th. very, very new. Have a nice weekend, Carl	[reply]
Re: only 16383 rows in Spreadsheet::ParseExcel by CountZero (Bishop) on Apr 16, 2005 at 15:37 UTC
Excel can export its data in CSV-format by itself, so you would not necessary need Spreadsheet::ParseExcel to do it. Still if you want to do it through Perl, you could think of using Win32::OLE to control Excel from Perl, but you do need to have Excel installed on the target machine. For a moment I thought one could also use DBD::Excel or XML::Excel but as they are based upon Spreadsheet::ParseExcel you will hit the same limitations. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: only 16383 rows in Spreadsheet::ParseExcel by sk (Curate) on Apr 17, 2005 at 18:00 UTC
Carl, I have not used ParseExcel myself and so I cannot comment on the limitations. As CountZero said if you can export to CSV from Excel or GNUmeric then that will save you a lot of work! I did not understand why you need to copy them in slices and paste into text editor. Doesn't GNUmeric allow you to export to CSV directly? Even if it doesn't I hope it should still let you 'select all' and then paste it in your editor. I am guesssing it will paste them as "tab" delimited. So if you are familiar with vim (you can use your favorite editor too that support regex) to change from tab delimited to CSV - `:%s/\t/,/g`. Perl can work with tab files directly but if you want CSV then this is one option (you can tr, s// in Perl too!). I have used jmcnamara's Spreadsheet:WriteExcel and it works beautifully (really fast on Linux machines!). I think he also gives you another module to work with files > 7MB. I found this post Reducing the memory usage of Spreadsheet::ParseExcel by jmcnamara. Hope this helps! cheers SK	[reply] [d/l]