When I read the excel with the Excel Reader, it changes the decimal separator in column B for only some cells, it makes no sense at all. After that, I can’t convert it as a string to a number, because it’s a complete mess! Some values are with commas, other with dots, others with dots and commas… chaos!
See bellow 1. The original excel file and 2. The preview on the Excel Reader.
It’s likely to do with how the excel data is parsed. What happens when you hover over the green top left corner of the cell and convert all the rows to number within excel? That should make the excel reader recognize the numbers in the right format
The pattern I see is that where you have a „.“ as thousand separator you keep the „,“.
I also think it will have to do with inconsistent formatting in excel. It looks like the () etc are not formatting of a number, but actual characters in a string…
Thank you for your reply. Yes, I can convert it to number in the excel.
Problem is: I have 100 files like that which I have to consolidate in only one, that’s why I’m using Knime haha, it’s no sense to open 100 excel files one by one to convert all data in numbers, it will take me some long hours
Yes, you are correct, they are all string which I need to convert to number! Point is: this is how I recieve the file from our system, that’s why I’m trying to automatize it with Knime so I don’t have to clean the file up every time I recieve it haha.
Btw, yes I can share it! Which file do you want? Excel? Knime Workflow? Both?
Good news is KNIME is good at preprocessing so I think it will be possible to automate this for all files that are read. I take they all have a similar structure?
