AnsweredAssumed Answered

Import from Excel decimal errors

Question asked by gimbal on Dec 7, 2008
Latest reply on Jan 17, 2009 by fh

Summary

Import from Excel decimal errors

Description of the issue

I've come across a math error, or bug, when importing decimal numbers from Excel files into FileMaker -- even when the Excel file is created by FileMaker export, and never touched by any version of Excel. I'm using FileMaker Pro Advanced 9.0v3 (11-02-2007) on a 2.33 GHz Intel Core 2 Duo MacBook Pro 17" with Mac OS X 10.5.5, all current Apple updates, clean format and install of system and software a month ago, no 3rd party system modifications. To replicate the problem:- Create a new FileMaker file with a number field.- In Layout mode, select the field, choose Format > Number...- Set it to "Leave data formatted as entered"- Widen the field on the layout to allow for lots of decimal places.- Go to Browse mode, View Menu, View as List.- Create records with simple decimal numbers: 1.1, 1.2, 1.3 and so on.- Choose File Menu > Save/Send Records As... Excel- This will create a standard .xls excel file.- Now, import the same file back into FileMaker...- You will see the decimals have gotten all messed up!  1.1 is now 1.1000000000000000888  1.2 is now 1.1999999999999999556 etc Now here's a twist:If you create an Excel file using the new Excel 2008 program, and down-save to Excel 2004 (.xls) format (because FMP can't read .xlsx format yet), it has the same screwed up results when imported into FMP9... BUT, if you create the file in the older Excel 2004 program, then import into FileMaker, the decimal numbers are fine!

Outcomes