Problems with decimal comma in excel compare

[ludo1208]

Hello,

I am comparing 2 Excel-Files and having problems with the decimal comma.
It seems that in BC3 the decimal comma is always 'ignored'.

Example (see attachments):
Cell D2 contains the value 1,45. The compare always shows this value as 145.
It does not matter which option I set in File Formats Menu ...

Is there a possibility to get the decimal numbers shown correctly?

thanks + regards
Ludo

[Aaron]

Hello,

BC3 should follow your current Windows Regional settings. In these settings, is "," currently defined as the Decimal Symbol? Are you using the latest version of BC3 (3.3.4)? All BC 3.x updates are free for 3.x users.
http://www.scootersoftware.com/download.php

[ludo1208]

Hello,

yes, my regional settings state "," as decimal symbol.
btw: even if I force "," in BC3 in file formats window (the 3rd attachment in my previous post) as decimal symbol it doesn't work.
And also yes: I am using 3.3.4 (Build 14431) ...
Any ideas?

thanks + regards
Ludo

[ludo1208]

Hello,

I have found a workaround for my problem:
I duplicated the first row (containing the header) of the excel and then it worked ...
see attachments:
-) Testexcel2.zip contains the excel with 2 header rows and a gif of BC3 made, when loading the file. It showed the decimal numbers correct (although with a decimal POINT instead of the decimal COMMA, but that is not a big issue).
-) Testexcel.zip contains the excel with only 1 header row and a gif of the wrong interpretation of the decimal numbers.

So, I can do it this way, but maybe you can reproduce this behaviour and fix it ;-)

thanks + regards
Ludo

[Aaron]

Hello,

In your screen shots, it would appear that your columns contain a mix of numeric and text values. This would cause the column to detect as "Text". It would need to be entirely numeric to detect as "Numeric", which would then treat numbers as numbers instead of text. You can right click any column header to manually switch from the detected value to a different type, but switching to Numeric would then cause issues with the plain text values in the same column.

[ludo1208]

Hello,

correct, switching to the other "mode" solves the problem :-)

But the funny thing is, that it's the other way round:
If I switch to "text" the decimal numbers are shown correctly, if I swith to "numeric" the decimal comma is not shown, e.g. "145" instead of "1.45" ... ?!

regards
Ludo

[Aaron]

Thanks for the update. It appears that when we call Excel from vbscript, it seems to be replacing the ',' with a '.', but when opened BC3 is set to use your System decimal value by default, which is expecting a ','. You can switch this decimal value in BC3 in the Tools menu -> File Formats, select your data format, and in the Type tab, switch the Decimal Symbol from "Use System" to ".". How does this work for you? Does this behavior describe the same scenario you are seeing?

[ludo1208]

Okay, i tried the different options:
My excel has still a comma as decimal sign (e.g. "1,45").
Option "Use system": shows "145" if column is defined as numeric, and "1.45" if column is defined as text
Option ".": shows "1.45" regardless whether column is defined numeric or text
Option ",": shows "145" if column is defined as numeric, and "1.45" if column is defined as text

regards
Ludo

[derVampir]

Any update on this?

Hello,

is there any update on this from your side? I have exactly the same issue here with a data compare we use to parse into a database and we use "," as a decimal separator (german settings). I was able to reproduce everything ludo1208 said.

Any help greatly apprechiated.

Regards, Ralph