2 Replies Latest reply on Sep 27, 2016 8:12 AM by dariusb

    Import from Excel decimal problem

    dariusb

      Hi

      My first post so I hope I can be clear!

       

      I have a FMP13 database that needs to regularly import from Excel to update matching records in a found set. The matching field frequently contains numbers like 4547.0231 but as the attached screenshot shows, in the import dialog it sees some of these numbers with very small decimal errors which means the fields don't match.  I've done a bit of reading about Excel and am wondering if this arises with decimal fractions which can only be represented in binary with recurring digits or sequences - but if true, it still doesn't help!  This is running on a Mac but I've tried it on Windows as well with the same result.

      Does anyone know what's going on or am I going to have to redesign something?  I should mention that the field 14145.0503 you can see is not a calculated result in the spreadsheet, it's a direct input.

      Many thanks

      Darius

        • 1. Re: Import from Excel decimal problem
          philmodjunk

          This is a long standing known issue with Excel vs FileMaker. You can search this info out in the Knowledge Base if you need to know more.

           

          Floating point numbers are stored in both systems in binary scientific notation, but they don't use the same number of significant digits for the Mantissa. Thus a number imported from Excel may display differently by a very small amount due to this difference between the two systems.

           

          It's sometimes necessary to round the values from Excel after import, but with a large number of decimal places in order to clean up the data.

          1 of 1 people found this helpful
          • 2. Re: Import from Excel decimal problem
            dariusb

            Many thanks for quick reply.