Excel Cell Updater - Date format

Hi!

Today I’ve tried the new Excel Cell Updater-node. I created a formatted empty Excel-template to be filled with data from another Excel document. Generally that worked well and format of the template was preserved as promised. The only problem I had was that all the imported data was delivered as string value. So date values and numbers were not formatted the way I defined it in the empty template.

Is there a way to get date values and numbers formatted too while using the Excel Cell Updater?

Thanks for your help!

Regards,
Mario

Hi @fuerstm,

Welcome to the KNIME Forum!

What may be of use in this specific case is the XLS Cell Formatter node Node in tandem with the XLS Formatter (apply) node. You can make cell style conversions from String to numeric, boolean, local date, local date/time, and local time.

Hopefully this helps with customizing the formatting to what you want, but if not please reach out again!

Regards,
Dash

Hi @dash_brookhart !

Thank you for the warm welcome and your help! I’ve tried to use the XLS Cell Formatter node and the XLS Fomatter (apply) node, but couldn’t find a solution so far.

As you can see in the enclosed screenshot I created an empty template with date format in column D. Then I used the Excel cell updater for filling in the data. The result looks good except Column D. There I find the number, but no date format.

I only want to change the format of column D to date DD.MM.YYYY. The format of all the other columns shouldn’t be changed. Is it possible to this the way you suggested? How can I define that just this one column should be changed by the node?

Thank you very much for your help!

Regards,
Mario

Hi @fuerstm,

I’m having some issues getting the XLS Cell Formatter and Excel Cell Updater nodes to create the date format you wanted.

Would a String Manipulation node work in your specific case? Something along these lines:

The end result would look something like this:

Hopefully this is similar to your desired result. If not please reach out again and I’ll see what else I can come up with!

Cheers,
Dashiell Brookhart

1 Like

Hi @dash_brookhart !

Thank you for this new idea! In my case that doesn’t work because for the Excel Cell Updater I just have 2 columns (1 with the cell address and 1 with the data). The second column consists of different kind of data, so I can’t change the type to date for the whole column.

But meanwhile I’ve found a much easier solution for my case. I change the data in the Source Excel file so I receive a string value of the date that looks the way I want. I start the KNIME-process right after that and so I can be sure that the column remains as expected after using the Excel Cell updater.

So I’ve found a good solution for me. Thank you for your help!

Regards,
Mario

2 Likes

Hi @fuerstm,

I’m glad you were able to find a simple solution! Those are always the best moments when creating workflows.

Please reach out again if you ever run into any other issues!

Cheers,
Dash