Localization: Excel data source messed up decimal point

Ask general questions here.
mrt
Posts: 88
Joined: Mon Mar 16, 2020 11:31 am

Localization: Excel data source messed up decimal point

Post by mrt » Fri Apr 16, 2021 7:19 am

Dear all,

I face an issue with Excel data sources:

The Excel sheet contains values 12,6 (with comma) and 12.6 (with dot), but when viewing the data source inside Ranorex they both show up as 12.6 (with dot).
It seems not to have any effect if I format the excel cell as Standard, Number, Text, ...

Most likely this is a localization issue.
On this system I do not have the localization set to standard English (where numberformat would be with dot as decimal point), but in my case comma is used as decimal point instead.

But, here is the issue:
In some data fields I have to enter a value with comma as decimal point, which I cannot achieve now because I get only dot-decimals out of the Excel data source.

Is there any setting in Excel or Ranorex that I can use to change this behavior?

thanks!
mrt

Edit:
Ok, now it gets weird.
In Excel, all values show up with comma as decimal point.
In Ranorex, some of them showed up with comma, some with dot. (?)
I copied values around, played with number format, now I got it that:

In Excel, all values use comma.
In Ranorex data source (also in preview effective data set) also ALL values show up with comma.

But when running the recording, it inputs values with dots into the input fields.

Ever seen this before?

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 6544
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Localization: Excel data source messed up decimal point

Post by odklizec » Fri Apr 16, 2021 8:06 am

Hi,

Well, there is a solution to this issue. But you may not like it? :D There is an option in Excel, which allows to disable usage of system decimal separator. But once you uncheck it, it will automatically change all appearances of 'comma' to 'dot' :D So you will eventually have to edit your excel files and manually replace all automatically replaced commas ;) In my opinion, it's bad design?
ExcelDecimalSeparator.png
ExcelDecimalSeparator.png (39.83 KiB) Viewed 134 times
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

mrt
Posts: 88
Joined: Mon Mar 16, 2020 11:31 am

Re: Localization: Excel data source messed up decimal point

Post by mrt » Fri Apr 16, 2021 8:10 am

Hi,

thanks, I will try this.

What I don't get:
In the meantime I created a whole new workbook from scratch, for the number values I put a ' in front of.
Excel says cell is formatted as "Standard", and if I input a number, it warns with "number formatted as text".

So long, so good.
Excel shows the comma, Ranorex shows the comma in preview.
But in the console output it shows a dot.

If this is interpreted just as a string, I have no idea why this value is changed.


Is this format change then saved to the workbook, or is this the application setting on my machine (and some of my colleagues will face the same issues on their machines if excel setting is not changed?

mrt
Posts: 88
Joined: Mon Mar 16, 2020 11:31 am

Re: Localization: Excel data source messed up decimal point

Post by mrt » Fri Apr 16, 2021 8:22 am

I deactivated the "use system separator", but I don't get it to work.
Which format did you choose for the cells?

I tried different ones, but with no luck:
2021-04-16 09_20_38-testor.xlsx - Gespeichert.png
2021-04-16 09_20_38-testor.xlsx - Gespeichert.png (8.06 KiB) Viewed 133 times

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 6544
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Localization: Excel data source messed up decimal point

Post by odklizec » Fri Apr 16, 2021 8:32 am

Oh I forgot to mention that I converted all cells to "Text". This should do the trick? ;)
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

mrt
Posts: 88
Joined: Mon Mar 16, 2020 11:31 am

Re: Localization: Excel data source messed up decimal point

Post by mrt » Fri Apr 16, 2021 9:50 am

yep, did it, thanks!