3 Replies Latest reply on Jan 9, 2013 7:36 AM by philmodjunk

    FMP 12.0v3 changing excel data after import?

    S.H.

      Title

      FMP 12.0v3 changing excel data after import?

      Post

           Has anybody run into a problem where FMP is changing the excel data that is being imported?

           I'm using FMP 12.0 v3 and excel 2011 v 14.1.0.

           I'm doing a simple import.  Excel data is formatted as a number with 2 decimal places and I'm importing it as a number (indexed).

           One excel cell has 20.4 In FMP it is 20.399999999999999

           Another is 4.6 and FMP has 4.5999999999999996

        • 1. Re: FMP 12.0v3 changing excel data after import?
          philmodjunk

               This is a long standing issue due to differences in how Excel and FileMaker store floating point numbers. A Replace Field Contents operation with Something like Round ( YourField ; 9 ) can clean them up pretty effectively.

               For More Information see:     Import from Excel decimal errors

               This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

               It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

          • 2. Re: FMP 12.0v3 changing excel data after import?
            S.H.

                 Thank  you.  Strange that this has been going on so long without resolution.  

            • 3. Re: FMP 12.0v3 changing excel data after import?
              philmodjunk

                   For those of us that have used FileMaker for a long time, this isn't all that strange. For one, it's  a pretty easy issue to live with for another, there are quite a few "issues" with FileMaker that have been with the product just as long as this one.

                   On a technical note, floating point numbers are stored in most software as a Binary value in scientific notation (It has a mantissa and an exponent, but in binary). This is an internal format, not something we developers have any control over. I find it interesting that Excel--software specifically designed for "number crunching appears to have less precision--fewer digits in the mantissa, than FileMaker does.

                   PS. In addition to useing Replace Field Contents to fix this, you may be able to use Round(Self ; 9 ) as an auto-enter calculation to fix this if you can enable auto-enter operations during the import. This would seemlessly correct the issue for each such data import. (And I am guessing from imperfect memory at the precision of 9 here. experiment with the exact precision used to get what best works for you.)