.xls writer node 65535 row limit

Hello-

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.

Hi,

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.

Cheers,
Roland

Hi Roland-

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)

1 Like

Scott-

That’s exactly what I’m running into.

Thanks for the clarification. I’ll follow up with one of our development folks.

1 Like

Hi Scott,

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.)

4 Likes

Yes, this is working. Thank you very much.

XLSX format is the solution.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.