decimal and thousands separators

[zielinska_km]

Hi
I’m using csv reader node to upload my data to knime. I’m using “,” as a decimal separator and “.” as my thousands separator. In one string column I have numbers, and for example I have an entry “1.28”, which means 1280. But when I use string to number node, and I convert this column to integer numbers, I suddenly have 128, instead of 1280.
In string to number node I chose “,” as a decimal separator and “.” as thousand separator.
How to resolve this issue?

[iCFO]

Unless the below component offers a better direct string formatting conversion option for you, then you will need to do a multistep conversion and test. This can be done by using the cell splitter node to break out “,” and “.” and then doing formula tests, or by using regex via the column expression node.

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

[Daniel_Weikert]

pragmatic solution. multiply by 10?
br

[iCFO]

The way I am picturing the data, I think it will require a logic test first unless the numbers were extremely uniform.
1.2 would need to become 1,200
1.28 would need to become 1,280
1.281 would need to become 1,281

@zielinska_km - Please upload a workflow with some a column of sample data if you end up still needing help.

[iCFO]

I would probably just do the cell splitter using the “.” as the deliminator (and again a second time using “,”), and then use the column expression node with padRight formula to fill in the zeros to ensure 3 digits on all of the split columns after the 1st one. Then you can combine the columns again with the correct formatting for KNIME using the Column Expressions node.

padRight(column(“column1”),3 ,“0” )

[gonhaddock]

Hi @zielinska_km

‘String Manipulation’ node:
join($text_number$, "E+3")

Then, ‘String To Number’ node configured by default.
"1.28E+3" (text) => 1,280 (double)

BR

[gonhaddock]

Let’s elaborate this idea a bit further.

‘Regex Split’ node (split E+3):

(.*)([.])(.*$)

‘String Manipulation’ ($scientific notation$):

join(
	replace($split_0$, ".", "")
	, $split_1$
	, replace($split_2$, ",", "")
	, "E+3"
	)

‘String Manipulation’ (duplicate column / append $numeric_double$)

join($scientific_notation$)

'String To Number’ node configured by default:

BR