Excel Reader is changing some cells for no reason

Hi! :wave:

My file has 2 columns:
A → KPI Name
B → KPI Value

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.

Any toughts on why it’s happening? Since it’s all different cases, I can’t do something like “Find and Replace” :disappointed:

Thank you in advance!

Hey @Rodrigo_Rasswei

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…

Can you share the file by any chance?

Hi @Add94 !

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 :joy:

I think your issue is that you have numbers that are stored as text as indicated by these green-ish triangles:

If that is the case I think you may have to add a preprocessing step to remove “()” and “.” for those cases and then finally replace “,” with “.”.

Then you should be able to use String to Number to get an aligned format…

If you can provide sample data happy to have a crack at it…

1 Like

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?

Thanks!

1 Like

The more the merrier - 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?

1 Like

Hello @Rodrigo_Rasswei.
I also noticed that the characters are not in the correct format: “Deduções” is not correct. Should you use UTF-8?

Br

1 Like