Am I able to increase the maximum number of rows per sheet in the excel sheet appender node?
I am appending a sheet for a table that exceeds 65,536 rows. The target file is in a .xlsm format, and have a version of Office that should support ~1M rows.
This limit is usually associated with 32bit Excel. Can you please confirm that you are using a 64bit version of KNIME? I just tried on my machine and could write 1M rows to Excel without any issues.
I can confirm I am using a 64 bit version of KNIME. To reiterate, this is an issue with the sheet appender node. The XLS writer node functionality can accommodate the same table without splitting it.
Hi @ohas -
I did a little testing here on my system (Excel 64-bit, KNIME 64-bit) where I create 66,000 rows of data using the Data Generator node. On my system, using the Excel Sheet Appender node works fine for exporting in XLSX format, but behaves a little strangely for XLSM format - the first 65536 rows get written to a sheet, and the remaining rows are written to a similarly named, but separate, sheet.
Is this what you’re seeing as well? I’ve attached a sample workflow and output files. (Note that I had to change the extension of the XLSM file to txt to get around the upload filter - just change it back after downloading.)
ExcelSheetAppenderXLSM.knwf (6.1 KB)
EmptyTest.xlsx (2.4 MB)
EmptyTest.txt (1.2 MB)
That’s exactly what I’m running into.
Thanks for the clarification. I’ll follow up with one of our development folks.
Did we have a solution to load more than 65535 row from excel/csv?
Use XLSX. The Excel97 format (XLS) only supports 2^16 rows (and so Apache POI - the Java framework used in the nodes, also only supports 2^16 rows.)
Yes, this is working. Thank you very much.
XLSX format is the solution.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.