FileMaker erroneously imports decimal numbers from Excel cells with NO FORMATTING
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
The number data in the fields should match the original raw data imported from source file.
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
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.