Increased Excel file size and Excel Writer sheet append issue with KNIME 4.6.0

[alexkrah]

Hi everyone,

this is indeed the same topic as the closed one here:

Increased Excel file size with KNIME 4.3.1

Hi, Hope everyone is doing well! I updated my KNIME to 4.3.1 recently. I started replacing legacy nodes with the new ones, and started from Excel Writer nodes. I really liked the new nodes. I am collecting information from multiple places and combining all information in one Excel file using append under file and overwrite under sheet name options. The final file has 4 sheets; sheet 1 has up to 10 rows sheet 2 has up to 15 rows sheet 3 has up to 50 rows sheet 4 has 200k rows Previously whe…

I didn’s see a real solution in it, that’s why I’m opening this topic again.
So I have this issue with KNIME 4.6.0 and I am wondering, if a real solution has been found.

My issue
The workflow creates several tables which are then written down to excel. Ideally it should be one Excel file with several Worksheets. The problem occurs with appending new Sheets to an existing Excel file which always runs into Java heap Error.

On my computer there is no issue, on the computer of my customer I run into Java Heap Error.

Troubleshooting so far

1. Checked RAM and allocated RAM on the customers computer and upgraded it so my customer has same amout of RAM allocated to KNIME. Issue still there.

2. Originally, the workflow was created with KNIME 4.3.5 so I thought upgrading will help. After upgrading to 4.6.0 and replacing the depercated Join nodes, been facing the same issue.

3. If I write each sheet to a separate Excel file, it works. The created files are however larger than they should be (like 100 MB instead of 10 MB for the first sheet, the other ones are ok, like 100 KB). Their size reduces to normal if I open them and save them (?!). Maybe it does have something with the columns of type number to do?

Is there anything I’m missing? What else can I do?

[mlauber71]

@alexkrah could you give us any idea how large the data is in its original form? Maybe by exporting it to a KNIME table or Parquet file? If you have a later KNIME version somewhere (4.7+) you could try and use the bundled Python version with OpenPyxl to export the data from within KNIME thru Python to Excel either replacing the Excel file or by creating a new sheet.

Export Excel File with OpenPyxl (simple) from KNIME - also add some formats –...

[alexkrah]

It is an Excel Sheet with 25K rows and approx. 2K columns.
File size of the source file is a bit large because of macros and other formulas: 187 MB. But they fall out in KNIME so we are only exporting the data. The other worksheets have less than 100 rows.
In this project we cannot use Python or R.

[mlauber71]

@alexkrah with the bundled Python version you would not need to set up anything besides loading the extension like very other KNIME extension.

2k columns seems to be something … is there any other way to do this?

How to Set Up Your Python Extensions | KNIME

[mlauber71]

@alexkrah you might have found another solution but at least on a Mac, it is possible to write a file of your size into a large existing excel file with the help of the bundled Python and OpenPyxl although I would not necessarily recommend it …

import a large Excel file with 2,000 columns and 25,000 lines - 300 MB size –...

[Daniel_Weikert]

Have you tried other file formats? Does this also occur in knime 4.7? Is there a sample flow with files to share?
br