Change thousands and decimals separator to #.###,00

Hi,

I’m having trouble converting a string to number with the correct thousands and decimals separator.

Here is some sample data before converting string to number:

FakturabeløbDKK
281,20
1.210,76
5.065,00

I then use the node “String to Number”, which give me a result like belowm but I just want to keep the format from the string like #.###,00:

FakturabeløbDKK
281.20
1,210.76
5,065.00

I use these settings:

How can I convert the column from string to number and keep the format?

Best regards
Morten

There may be a renderer setting in preferences to show the decimal separator as a comma, but it would be a global change across all workflows. I know that this was requested in the past. Check here.

File > Preferences > KNIME > Preferred Renderers > Basic > Number (double)

Edit - I checked and this requested setting was never implemented.

I have used this component for similar tasks in the past (although not for your exact formatting), but it can’t change the actual Double display format in KNIME. It only creates a correctly formatted String column for display purposes.

Not sure i this is relevant, but I always make sure that any csv or other file reader uses the right separators in the Advanced tab in the corresponding reader. Usually the wrong choice means that the column in question is imported as a string, and there is a need for the String to number conversion.

Is this the case for you too?

Cheers,
Sam

1 Like

Unfortunately this option is greyed out. So this is not an option.

@ssq I import a csv file and the column is set to ‘Number (double)’ and in advanced option the separator for thousands are set to dot (.) and for the decimals comma (,). But still the number is displayed as 1,210.78.

Number To String (Locale).knwf (108.0 KB)

It seems to work fine on my end when I select “Icelandic” from the top options. This creates a “Text” column for display purposes only. Unfortunately there is no current workaround for altering the number datatype displays via KNIME’s internal rendering as you are building out a workflow. This will help for native formatting at output and review points.

What is the final goal? Is the data exported to another tool? If so I would rather focus on change the display settings in that tool
br

1 Like

@Daniel_Weikert first I will group data and then export the data to *.csv. Then import the data to either Excel or Power BI. So your advice to focus on change the format in Excel or Power BI sounds like a good idea.

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