AnsweredAssumed Answered

FileMaker erroneously imports decimal numbers from Excel cells with NO FORMATTING

Question asked by rsantangelo on Aug 6, 2010
Latest reply on Aug 6, 2010 by philmodjunk

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.

Outcomes