Hello everyone,
I have the following case.
We download a csv file from Jira. After that we open this in excel an convert it to an xlsx and replace financial figures thousend from “.” to “,”. This is hard to do in excel.
So my question is how I can do this without lossing data quality of my figures.
@Asensio_Dias you can switch . (dot) and , (comma) with knime nodes. If you have to change them you might have to use an ‘intermediate’ placeholder to not confuse the system.
@mlauber71
Thank you for your feedback.
The problem is I´ve to transform a lot of columns with his values from dot to comma. And here I´m not sure if this works.
Because the figures and values, must not change except that decimals and thousands are converted with a comma instead of a dot.
I tried it even with a python script but not with desired success.
@Asensio_Dias mayb we you can provide an example. Also there are loops to do it and also multi column nodes to handle strings.
Hello @Asensio_Dias
You can try the attached workflow
Dot_comma.knwf (72.4 KB)
It does the following:
Starts with sample strings that contain both dots and commas, and look like numbers.
Replaces . with - (temporary, supportive character).
Replaces , with .
Replaces - with ,
Operates on all columns.
The outcome is as follows:
It might happen you would need to use ‘Number to String’ or ‘String to Number’ node somewhere.
Happy KNIMEing!
Thank you for your help here.
The challenge here is that from the tool where I download the file is in american values format, so when I apply the String to Number, ich have figures like 1,0E-6 und this kind of transformation isn´t correct.
So will look for another possibility.
My proposal refers to ‘string’ type of data, thus 1,0E-6 won’t be an outcome.
Number format is a different story.
It would help if you could share your sample data, because it seems the content and the data type are both important.
Hello,
yes correct, the format is diffrent between the german finance format.
Here are some created example data´s.
What I did in the past is:
I opened in Exel on the top “Data” → “from text csv”-> then a window poped up a new window, where I selected in “data type detection” → “Do not determine data type”
After that I copied the data in a second sheet with copy past just with value.
Afterwards I mark all relevant columns an replace “.” with “,” → save as Xlsx file.
That the whole process and then I start to use this xlsx for my knime transformations.
Maybe this large explanation help here to build a easier and full workflow in Knime.
Thank you
mappexample.csv (883 Bytes)
Hi,
have you tried the “File Reader” Node?
Just press the “Autodetect Format” button to put the right settings in it.
As an option you can skip the first row in the “Limit Rows” section
Now you can work with the Data in KNIME or export directly to Excel. The “german notation” with “,” as decimal delimiter is done in excel automatically
@ActionAndi is right: you can use ‘File Reader’ node as per his suggestion.
Or you can use ‘CSV Reader’ node dedicated to read csv files. Let have a look at such configuration:
And then, ‘Limit Rows’ tab:
I’m getting such output table with just single node:
Instead of replicating current operations done in Excel, you can improve the process:)
Is that something you are searching for @Asensio_Dias ?
If so, you can add ‘Excel Writer’ node to write outcome to Excel.
The String to Number node let’s you specify the thousands and decimal separators.
Could you import the csv as text, then use the node to convert the relevant columns to numbers?
Once you have the column as numbers, you could save to Excel.
