Strange behavior of Excel Writer with date or datetime columns

Hi!

My workflow creates a progress report of sorts relying lot on date or datetime fields. The report works fine but a colleague of mine wants to do some explorative statistics via Excel and some Power Whatever, so I dump the data via the Excel Writer node.

Now he has two problems with the dump resulting from fact that Excel Writer uses a customized format cell instead of date format:

  1. The date format is set by the workflow and does not conform to his local Excel settings
  2. The MS Power Whatever is not recognizing the customized format fields as dates, though Excel does this correctly

Is there any possibility to force the node to output plain date cells?

Maybe you could make an example how your export looks like and how it should look like.

2 Likes

As you can see in the cell in I) the date is exported as set in the workflow. II) shows it is correctly identified as date and converted into locale, but only in the input line. When I open format cell via context menu, format of the cell is set to user defined instead of standard or date, which on the one hand enforces representation and on the other breaks Excel features like Power Pivot.

Excel_Writer_Prob.knwf (6.9 KB)

Have you tried converting the Date&Time columns to strings before exporting the Excel file?

1 Like

The cell is now at least of category standard. I will try tomorrow if this works with Power Pivot.

From my perspective, the Date field in Excel is a regular date or time field, just formatted in the way the date/time would appear in KNIME. But you could always just change the format and you could still use it as an Excel date/time variable. Not sure why any MS program would not be able to use that.

You could try and change the standard rendering of date and time values across KNIME but I am not sure if that would help.

Then indeed you could write it as a string and then tel MS Power (BI?) to use that. Then you could think about another format that might serve as a data transport capsule. Excel is maybe not the best format to store variably types in a consistent way.

The last idea could be to employ the Continental Excel Formatting nodes. They should be able to give you the desired cell format (although I have not tried this myself).

https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

1 Like