Write to Excel Template Variable Issue

Also, I did notice that there is a difference in the output file of your node vs the built in excel write node. There is almost 1MB of data missing when your node writes the excel.


@languy22 smaller file does not have to be bad? :thinking:
In general my node attaches the data to the existing file without changing the formatting.
So could it be that enabled e.g. auto-sizing in the normal node increases the file size.

Are any rows missing?
Else I would say this is a feature and not a bug :smiley:

1 Like

Not sure yet on the missing rows, I have to look into it.

One thing I would recommend adding is to have an option to overwrite all of the data on the page you want to write to. It looks like it does but if the original data on the page is longer than what it is writing it leaves the old data.

For example, the original file has 7000 rows. The new data the node is putting in is 5000 rows. The node will write the new 5000 rows of data but leave the old 2000 below it. Thus, messing up the data, especially if you have changed what columns you are pulling in.

Excel writer has an option that says if sheet exists overwrite, append or fail. I think this would be a good addition to your node.

1 Like

Hi @languy22,

the new update (which should be available tomorrow morning) fixes the sheet name display with file connection. As well should a sheet be selected which does not exists - then a new sheet with this name is created instead of the node failing.

As well I added the “clear before writing option” (advance settings tab)
The clear option will keep all formatting of the file but removes all data and formulas from the sheet.

*Will check how I will continue with this node in 4.7 - most likely I’ll merge all these changes into the original node and remove the extra “with path” node.

1 Like

Hello @AnotherFraudUser

I just installed the latest update but none of these updates are showing up.

I have the latest installed, I think.

2022-11-21_17h23_06

Hi @languy22,

for 4.6 the update should be 202211201803:

Look fine for me there:

for 4.5


its the same update id you have (so I guess you are using this one?)

Looks fine to me as well :see_no_evil:
grafik

Does the option showup if you move a “fresh” node into the workflow? :slight_smile:

1 Like

No, I don’t get that. I just uninstalled the whole plugin. Reinstalled and dragged new node in and still shows up like the old one.

Hi @languy22

Could you try to add the -clean option to your knime .ini
grafik

Sorry for the trouble - not sure why the version does not show up for you :frowning:

FYI - I updated and tested on my installs as well and didn’t see the new settings either.

Hi @iCFO,

thanks for testing!
What version of knime did you use? :thinking:

4.6.4
I run current release, but not nightly.

1 Like

Thats strange :frowning:
Also have KNIME 4.6.4 and it should showup in the (with Path) Node :thinking:

Could you try to add the -clean option?

I just noticed that the node name changed to add (with Path) at the end. I didn’t realize that this was a new node rather than a change to the old one. I was probably just inserting the old node with a shortcut macro button or from my favorites list. I will test again later this morning.

1 Like

yeah I added all the changes for @languy22 in a temporary new node - just to make sure not to brake anything while testing for other users :slight_smile:
With Knime 4.7 all feature will be added to the normal node

1 Like

@AnotherFraudUser - All of the new settings are showing up on my end in 4.6.4. Now that I am looking at the right node that is… :crazy_face:

1 Like

Hello @AnotherFraudUser I just tried the clean command and it worked. I was able to get the latest one to show up and it works correctly on the clear and write new data check box.

There is still a problem with how it writes dates to the excel file. The original built in Excel Writer node works correctly and Excel identifies the dates correctly. Your node for some reason is not doing what excel writer does and excel cannot identify the column as a date and when you try to use it in things like a pivot table it just doesn’t show up. Let me know if you need some examples.

Thanks for the hard work on this, you have made great progress on making this node much more usable and advanced.

Hi @languy22,

great to hear.
I hoped that I got all the various date cell format in KNIME covered now🙈

Could you provide me with examples - best would be a direct table writer export from knime then I get the exact KNIME cells formats causing the issue

1 Like

See the attachments here. I have created 2 excel files for you. One that was created using the Write to excel template node and another with the excel writer node. I have added a pivot table so you can see what is happening when you try to use the date column.

The forum won’t let me attach the .table file out of KNIME. Do you know how I can attach it?

example created with Write to Excel Template.xlsx (188.8 KB)
Example created with Excel Writer.xlsx (158.7 KB)

1 Like

Hi @languy22,

Great thanks for the examples.
Did not know that .table files are restricted :see_no_evil:
Maybe zip file file and attach the zipped version.

Will check your example files later :slight_smile:

Hi @languy22,

what exact data type are you using in KNIME for these?
With local date and the old date time format I get the normal excel format :confused:
Dates.xlsx (26.0 KB)

I think I had this issue fixed with the new Excel Writer with Path node :see_no_evil:
Before I wrote the dates as text which could cause this type of issue

Could you maybe add your input table as CSV (or just share an example workflow with the exact data types :slight_smile: )