Search code examples
datemetadatapentahokettlepdi

Excel writer date format error

I have a source data(.csv) with a Date column which a format "dd/mm/yyyy" and when I try to output this date column into Excel writer, it gives me an error and also Excel writer step doesn't has the same format built-in in the content tab-->fields-->Type of this step. I even tried using select values step and specified the metadata of date format as "dd/mm/yyyy".

Transformation:

enter image description here

Drop-down date format list from Microsoft Excel Writer step:

Date formats

Solution

  • Microsoft Excel Writer does support Date type with dd/MM/yyyy format which is properly saved in a cell within a created file. I suggest you don't choose the option from drop-down, as it may not be full, but rather type it by hand.

    Here's an example I've run which proves it:

    Sample

    As an additional note it's good to remember, that if you ever face such issues and find that even though you can ensure formatting of a column in previous steps in the step where it can't be enforced you could go for String type with # format - this would treat the input column value as-is and thus not altering it in any way that it came from the previous step.