Overwrite the previous data of Write to Excel Template

[huynhduc]

Hi everyone,

I am using Write to Excel Template to export data to an existing file.

If the data is wrong and I want to export it again with overwrite the data which is exported previously without affecting the format of data in the existing file.

Is there any way to do that?

Thank you.

[tqAkshay95]

Hi @huynhduc,

Could you please confirm if there’s an issue with the data or structure of the table?

Additionally, could you provide more details on what exactly you’re looking for, so we can address your request accurately?

[iCFO]

There is a setting under “Advanced Options” for “Remove all data before writing?. I recommend placing your table in its own tab and using this setting. The table can always be referenced in other tabs for live calculations.

[huynhduc]

Thank you for supporting me.

I mean, if you use the node Write to Excel Template 1st time, for example, column G is having data.

But, when you use it for the 2nd import, if the column G of your data that you want to import is missing value, the column G in the template will remain the previous one.

Have you ever met this case and how did you solve it?

[huynhduc]

Yes, I tried. But it would remove all of the data setting for my template, which is the formula, merge cells or something.

If I try to reference as you share, it would create a large Excel file. Because, initially, I already had more than 200k rows data already. And, my importing data is approx 400k rows.

[iCFO]

Some options:

You can always create a new excel file on each write from a different template file which has the data area empty, but all necessary formulas and formatting.

You can do a “case switch” in KNIME so that you can conditionally create the excel file from the template without data based on a test.

You can replace the null values with “” or “ “ so that the cells don’t retain old values in the event of a null value, however if you go this direction then you will also need to append enough dummy rows to the bottom of your new data to completely over write down to the bottom of the old table.

You can also build the necessary excel formulas as Strings in KNIME and have the Write to Template node convert them into live calculating formulas during the write. Which should allow you to then remove the old data during writing.