Announcement

Collapse
No announcement yet.

Problems with decimal comma in excel compare

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • derVampir
    replied
    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

    Leave a comment:


  • ludo1208
    replied
    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

    Leave a comment:


  • Aaron
    replied
    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?

    Leave a comment:


  • ludo1208
    replied
    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

    Leave a comment:


  • Aaron
    replied
    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.

    Leave a comment:


  • ludo1208
    replied
    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

    Leave a comment:


  • ludo1208
    replied
    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

    Leave a comment:


  • Aaron
    replied
    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

    Leave a comment:


  • ludo1208
    started a topic Problems with decimal comma in excel compare

    Problems with decimal comma in excel compare

    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
Working...
X