Announcement

Collapse
No announcement yet.

Problems with decimal comma in excel compare

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

  • 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

  • #2
    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
    Aaron P Scooter Software

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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.
          Aaron P Scooter Software

          Comment


          • #6
            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

            Comment


            • #7
              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?
              Aaron P Scooter Software

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment

                  Working...
                  X