we want to read a table from a .xlsx Format with German Language encoding (digits as double with , instead of . ).
When we use the Excel reader node, the comma is changed to dot. We did not find an option in the node configuration to change this.
We tried the file reader or csv. In csv reader we did not find a suitable option and file reader did not show the right table.
Can you help or do you know a working solution? Thanks
Welcome to KNIME community forum.
not sure this is possible (Umlaute like Ä oder Ü) but if you can share an example Excel file someone will check it for sure
Regarding comma and dot that is the notation in KNIME and not sure can be changed. Why is it important to you to have a comma as a decimal separator in KNIME?
thanks a lot for the fast reply and nice welcome.
The issue with the Umlaut Ä or Ü was due to our source files. Therefore I deleted it in my question to stop confusing other forum users.
The issue with comma and dot is half solved. Just the export to csv ist not working:
Here an example with image and Excel file attached. The first value for “Umsatz” is 56478378,91. The interpreter of the XLS reader node changed it to 56,478,378.91
Then we do some transformations of the table and write the file als Excel or CSV.
In CSV the output is: 5.647837891E7
In Excel it is correct.
What would you suggest to make the .CSV export with the right measure format? I tried different Encodings but the result for the measure was the same.
Testdummy.xlsx (10.0 KB)
One solution is to convert the double to a string before exporting to csv. You can use the Round Double node and select the standard string output format. Then use the String Replacer node to swap the dot to a comma.
This way you get around the default double to string conversation settings in the CSV Writer node that’s giving you the scientific notation.
Example workflow below.
Hope this helps!
writing to csv.knwf (9.4 KB)
Thanks a lot for all your help. This case is solved.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.