I have an excelsheet with three columns (you will find it attached) 1.xlsx (1.1 MB)
Identnr
Installationdate
Latestinstallationdate
Installationdate and Latestinstallationdate are containing 3 types of values:
a - (minus character)
a date
a date time
I am doing some calculations with the sheet and writing into another excel sheet.
Unfortunalety the date colums appear in the outbound file in format yyyy-mm-dd or
yyyy-mm-ddTHH:MM:SS
I am looking for a smart way to convert as described below:
the - (minus character) should be replaced by nothing
date should be formatted like dd.mm.yyyy
date time should be formatted by dd.mm.yyyy HH:MM:SS
I tried with string manipulation, string to date, date to string but this seems to me not very smart - at the end of the day I have to do this with 20 sheets and different columns.
I have in mind to do this in one node:
Replace - with nothing for column Installationdate
Replace - with nothing for column Latestinstallationdate
format short date to dd.mm.yyyy for Installationdate
format long date time to dd.mm.yyyy HH:MM:SS for Installationdate
format short date to dd.mm.yyyy for Latestinstallationdate
format long date time to dd.mm.yyyy HH:MM:SS for Latestinstallationdate
Folk order of the identnumber should not be changed.
Hi @Heinz, is the attached file the intended one, as it only appears to contain dates in yyyy-mm-dd format rather than in both date and datetime formats that you described. thanks
inbound.xlsx shows 2 columns:
Customer No and
InstallationDate
Installationdate is shown in german notation dd.mm.yyyy.
Installationdate is date formatted in Excel
With node excel reader I read this file
File Table from this node looks like
installationdate has been changed into Knime format yyyy-mm-ddTHH.MM.SS
With node column expressions I do some manipulations in column installationdate:
When installationdate = - then installationdate= null
when length(installationdate)=8 then installationdate = datetime(installationdate +“T00:00:00”)
when length(installationdate = 19 then installationdate =datetime(installationdate)
with node excel writer I write the file outbound.xlsx
everything is fine, except of the format of the new column:
I have seen the write to template behave differently when passing a string data type vs date, with date format set on the excel side.
I have also seen a few quirks overall with date columns not holding all of the template formatting on overwrites. Like losing bold font / center alignment.
Perhaps you could have a few pre-existing string cells in there that can’t match the date format output which are throwing your formatting off. Have you tried checking remove all existing data in the advanced tab of the Write to Template node? Make sure you are writing your headers though.
I am reading inbound.xlsx from c:\temp
Outbound.xlsx colum b is formatted dd.mm.yyyy HH:MM:SS
Please see screenshot from outbound.xlsx:
cell B4 and B5 are formatted wrong (cell B4 marked red = not ok)
contents of focused cell B4 is shown correctly in editbar (marked yellow = ok); this I am expecting in column B
Didn’t make any workflow adjustments. Just opened it as a temp file and pointed it toward the downloaded excel files, then adjusted the custom formatting on those dates in the output excel file. Ran it again and it held on the overwrite.
Did it not work on your end overwriting the file I uploaded?
I placed the inbound and outbound files in the data folder to keep them in the workflow file. I also set it to open the outbound file after writing. (Never tested the opener on a temp file though, so it might require a “save as” first)
It is not writing “outbound” to that temp file location in my workflow. It is writing to the data folder in the workflow. I have set the component to open the data folder after it runs this time so you can access the location more easily. You can copy and paste that Outbound excel file wherever you like and point a Write To Template toward it for overwrite. Be sure to save this workflow before executing it to help you find the data folder.
Write to File Template keeps the existing formatting in place of the template file and only writes the data changes. Once I changed the custom formatting in the Outbound excel file to the desired outcome and saved it. Subsequent overwrites worked as expected.
If you want to write to that temp file location with my changes, then you could change the output setting of the Write to template node at the bottom from overwrite to create a new file and point the output to your temp file location. (Leave the template file at the top pointed toward my file in the data folder) Then it should incorporate my format change to the outbound excel file in the workflow data folder.
