Manipulation of Column Format

Hi Team. New to KNIME.

Basically, want to know what nodes to use so I can adjust formatting of 2 main columns (see below).

DATE Column:
Currently in this format (TEXT): 2023-12-04T00:00:00
Want to use KNIME to make it (TEXT): 12-04-2023 00:00:00

  • Rearrange to MM-DD-YYYY HH:MM:SS
  • Remove the “T”

Carrier ID Column:
Currently in this format (TEXT): 154224145
Want to use KNIME to make it (TEXT): 00154224145

  • Add 2 Zeros at the start

I’ve already done linking to SharePoint but not sure what nodes to do the manipulation stated above.

Appreciate the help!!

Hi, @arzconci

Welcome to the KNIME Community Forum.

The node column expressions will do it.

Have a try.

Hi @arzconci, and welcome to the KNIME community.

Alternative nodes

For changing format of a datetime string would be to convert it to a datetime specifying the current format, and then converting that back to a string specifying the required format.

Current format: yyyy-MM-dd'T'HH:mm:ss

Required format: dd-MM-yyyy HH:mm:ss

Just overtype the format in both dialogs if the format you want is not shown. Please note the format mask is case-sensitive, so enter it as shown above.

For String Concatenation, you can use String Manipulation:

join("00",$Carrier ID$)

As an alternative, if you find that some of your numbers already contain one or more zeroes and you wish them to universally on have a 00 prefix, you could use String Replacer instead,
and have it replace the following regex:

regex: ^(0*)([0-9]*)$
with this: 00$2

Depending on your version of KNIME, String Replacer config will look like this:

or this:

or you could achieve the same in the String Manipulation node, mentioned earlier with this:
regexReplace($Carrier ID$,"^(0*)([0-9]*)$","00$2")

1 Like

Hi takbb! Thank you so much! I did encounter one issue with the String to Date&Time step.

I’ve followed the entries but is getting this error: Execute failed: Failed to parse date in row 'Row0: Text ‘2023-11-06’ could not be parsed at index 10

Hi @arzconci , it looks like you have at least one row containing data in a different format. In this case it is just ‘2023-11-06’ without the expected time part.

It appears from the message to be on the first row (Row0) so you’d need to look at all those columns that are selected and see which is the offending column.

Alternatively, you could try changing the mask as follows,
to make the Time part optional :


Hi @takbb ! Thank you! Works now and I was able to get the format I need. Appreciate the help!!!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.