1 Reply Latest reply on Aug 6, 2010 11:53 AM by philmodjunk

    FileMaker erroneously imports decimal numbers from Excel cells with NO FORMATTING

    rsantangelo

      Summary

      FileMaker erroneously imports decimal numbers from Excel cells with NO FORMATTING

      Product

      FileMaker Pro

      Version

      FMP 11  Advanced and FMP10 Advanced

      Operating system version

      Mac OS10.5.8  and  Windows XP SP2

      Description of the issue

      This is related to Knowledge Base answer 7090, but note that it has nothing to do with an Excel file being formatted with scientific notation, or any other formatting issue (as far as I can tell).  Here is a copy of an email I sent to some colleagues:

      I came across something disturbing when with a client yesterday: when importing decimal numbers from an Excel spreadsheet (e.g., take the number 2.345), FileMaker does a "reverse rounding" (e.g., the imported number becomes 2.3450000000000002). 

      My client joked that FileMaker was giving him more accurate readings than the raw data ... but both of us found it a bit disconcerting.

      This does not happen when importing the same data from a text file, but is reproduceable on both Mac and PC, with any combination of FMP10 or FMP11, and .xls or .xlsx files (also happens with FMP9 and .xls files).  You get the same result if you "create new database from spreadsheet", if you want to try it yourself by dropping the attached Excel file onto the FileMaker application to create a new database.  Note that I created the attached sample file from scratch, and did not apply any weird formatting to it or anything.

      Steps to reproduce the problem

      1. Create a new spreadsheet in Excel with just a few columns and rows filled out (use the following data for good example data):

      1.345     1.445     6.122
      1.219     4.332     1.432
      5.132     1.555     1.212


      2. Save the spreadsheet, and drop it onto the FileMaker application to "create database from spreadsheet".

      3. In the resulting FMP file, click into the fields to view complete data

      Expected result

      The number data in the fields should match the original raw data imported from source file.

      Actual result

      The precision of the number data in the fields is sometimes much greater than that of the original raw data.

      Exact text of any error message(s) that appear

      N/A

      Workaround

      You can round the data post import ... but this is not ideal for my current client, who would not know the precision of the raw data unless he were to open the spreadsheet and look at each data point.