Wrong decimal seperator

[Grayfox]

Hi everyone,

I’am dealing with an issue about the decimal seperator in Knime. I have a lot of data (550 colomns) with a wrong decimal seperator.

For example, I have average price and size, witch have a decimal point seperator. So I would like to convert this decimal point with a comma seperator.

But I have too much columns to do one after one. There must be a solution, but I don’t find.

Thank for your help.

[HansS]

Hi @Grayfox

See this example looping over all columns from the KNIME hub. Replace the Math formula node with the nodes you need to replace your decimal seperator.

gr. Hans

[Grayfox]

Thank you @HansS but I don’t understand this solution, it’s too complicated. There is no way to specify the seperator in the Excel reader node ?

[HansS]

Hi @Grayfox I assumed that you have your data already within KNIME and then you needed to replace your decimal separator. That is when my solution comes into play. If you have an example how your “wrong” columns look like and what your desired output is, I will try to make this solution work for your situation.

[Grayfox]

Thank you for trying to help me.

Here is the exemple, when I use the Excel reader node, i’ve got this

Product	Price 2017	Sales 2017	Stock 2017	Size 2017	Price 2018	Sales 2018	Stock 2018	Size 2018
A	1596.1455	126	410	13.50	958.123	63	50	47.50
B	1546.1455	150	140	16.70	569.25	40	20	47.50
C	1546.1455	150	140	16.70	569.25	40	20	47.50
D	1546.1455	150	140	16.70	569.25	40	20	47.50

Like you can see, the price and size have a dot seperator, and I don’t want a dot but a comma seperator.

Product	Price 2017	Sales 2017	Stock 2017	Size 2017	Price 2018	Sales 2018	Stock 2018	Size 2018
A	1596,1455	126	410	13,5	958,123	63	50	47,5
B	1546,1455	150	140	16,7	569,25	40	20	47,5
C	1546,1455	150	140	16,7	569,25	40	20	47,5
D	1546,1455	150	140	16,7	569,25	40	20	47,5

It’s a easy exemple, I have 560 columns, and I want to replace all dot seperator with a comma.

[lisovyi]

Hi,

where would you want to see comma as the decimal separator? At this stage KNIME internally uses . as the decimal separator. So tables that you will look at inside of KNIME will show .. There is a related discussion here: Change standard decimal seperator. There is always a work-around of looping suggested by @HansS: loop over columns, convert numbers into strings and replace the dot with a comma in the string. But this will be slow for a large number of columns and you won’t be able to do math operations on the resulting strings.

Cheers,
Misha

[Grayfox]

I want to use comma seperator because I work with an excel output. and excel don’t like the dot when you do math operations, it’s return #value . So I need to replace this dot by a comma on Knime.

I’am sorry but I don’t understand how the solution of @HansS works.

[HansS]

Hi @Grayfox , like @lisovyi mentioned not the best solution, but see this workflow decimal.knwf (18.8 KB) how with String Manipulation node the “.” are replaced by a “,” for all columns.

gr
Hans

[quaeler]

As others have mentioned, there is no suitable way to do this - and there is an open issue in KNIME’s JIRA to implement this (where ‘this’ is the localization / customization of numeric formatting.)

You could do something like string manipulation, but then you no longer have a column that is a numeric data type, which seems like not what you want. Perhaps as a penultimate step to generating a report / readable text you could then do the string manipulation - depending on your use case.

[Grayfox]

Thank you for your workflow. It’s seems to work, but I have two difficulties.

As I said, I’am working with muliple columns and I have to select my columns in the Column Splitter Node.
So I tried to use Regex selection, but I don’t know how to reverse my selection with the Regex. Instead to having my selection in the Top, it goes on the bottom.
The regular expression used is : (.*)(Price $.*|Size.*|Target.*) and I tried to reverse this selection by using the ^ caracter. But I don’t know how to use correctly with multiple excluded parts.

Finally the very important thing is that I need to have in the output the columns in the same position since the begining. And when I use a Columns Splitters, all columns are only selected if they have a point (price, size …). So these columns should go back to the original files at the same position.

If it’s not clear, do not hesitate to ask me for clarification.
Again, thank you very much for your help.

[HansS]

Hi @Grayfox

I’m not so good in RegEx, but @armingrudd is. Is it an solution to switch your upper output port downstream and your lower output port upstream…? And maybe this site regex101 is helpfull for you.

For remaining your column order there is the Reference Column Resorter node, that will do the trick.

gr. Hans

[armingrudd]

Hi @Grayfox,

The selected columns in the Column Splitter node go to the bottom port. There is no difference between the output ports.
However, If you insist to send the columns which their name begin with “price”, “size” or “target” to the top port of the Column Splitter node, use this regex:
((?!(price)|(size)|(target)).)*

This will select all the columns that do not begin with these names. So the columns which their name begin with price, size or target, go the top port.

[Lahoma3020]

Hi Grayfox,

we had a similar issue and found a good solution with the round double node:
https://forum.knime.com/t/how-to-read-excel-csv-with-german-language-locals-comma/14757/3

Regards
Marcel