Excel Sheet Appender Issues

[akhildesa92]

Hello Colleagues,
I need to append a table containing 100k rows into one excel file.
Is there a way to index the appending process for example start appending from C2 cell ?
I tried to used the excel sheet appender node and the process required a significant amount of memory to execute the task.
Is there a lighter way to process this task??

[andrejz]

Hi,

Did you check the option “Autosize columns” in the Layout?

I noticed that in the “Excel sheet appender” and “Excel writer” the time to finish depends on selection of the option “Autosize columns”.
I have a table with about 257000 rows and 12 columns. The “Excel writer” node finises in 12 seconds, but if I check the “Autosize columns” option then the node finises in 12 minutes and 45 seconds. I have tried with the “Excel sheet appender” node with a table with about 16000 rows and 26 columns. The node finises in 23 second (without “Autosize columns”) and 5 minutes and 10 seconds with “Autosize columns” option checked.

Question for KNIME developers:
Can be this two nodes optimized to take less time to write to excel with “Autosize columns” option selected.

Thank you

[ScottF]

Hi @andrejz -

I can reproduce this behavior on my machine too. For whatever reason, the “Autosize columns” options increases execution time for the Excel Writer node by a factor of about 10.

Seems like it might be a bug, so I’ll write a ticket (internal: AP-14265) and ping the dev team. Thanks for reporting!

[pigreco]

Hi,
according to me this is not a bug.
Autosizing a column is an expensive operation because final size of each column depends by used font (that isn’t monospace) and specific chars in the cell (‘i’ is closer than ‘O’) for every cell of the column.

[mlauber71]

autosizing was always a very costly operation. Not sure if you could make it faster. One idea could be to use only the first x rows to do it.

What also would be nice is to freeze the top n rows or columns and activate column filter.

[andrejz]

For the column filter there is an open ticket

Output data window - feature suggestion

Hi there @andrejz, so I have checked and ticket for copying the entire table with headers included (internal reference: AP-13584) is done and will be part of KNIME 4.2 release - usually summer. This can already be check with KNIME nightly build version. There is also ticket for filtering output data (Excel like filter) and I have given it +1 for you. Br, Ivan

[akhildesa92]

Thanks for the reply, I will try this…

[ipazin]

Hi @mlauber71,

@mlauber71

What also would be nice is to freeze the top n rows or columns and activate column filter.

What do you mean? Don’t follow

Br,
Ivan

[mlauber71]

Like this:

[ipazin]

You mean in a table output view?
Br,
Ivan

[mlauber71]

Yes for the excel export node

[ipazin]

Hi @mlauber71,

I still don’t follow from where this freezing and filtering should take place

Br,
Ivan

[mlauber71]

When the data is exported into Excel. So if you open the file in Excel you would have the file like shown in the screenshot.

[ipazin]

Hi @mlauber71,

think I get it now But why would you want that?

Br,
Ivan

[mlauber71]

So I would have my file ready to explore. These are the typical steps I would take after exporting to Excel - I might write something with OpenPyxl …

[ipazin]

Hi @mlauber71,

ok. I see. Seems any option to format Excel file from outside would be welcomed by many Excel users Anyways think this is more appropriate for Continental nodes.

Br,
Ivan