8 Replies Latest reply on Jan 17, 2009 5:53 PM by fh

    Import from Excel decimal errors

    gimbal

      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!

        • 1. Re: Import from Excel decimal errors
          TSGal

          gimbal:

           

          Thank you for your post.

           

          I can definitely duplicate the problem with FileMaker Pro.  I do not have Excel 2008 to test out, but that shouldn't matter.  I have forwarded your entire post to our Development and Software Quality Assurance (Testing) departments so they can determine the cause.

           

          Until this is resolved, I would export the data to a tab-delimited file and import in the other application.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Import from Excel decimal errors
            TSGal

            gimbal:

             

            I just received word that the Excel .xls format is limited to floating point arithmetic.  Needless to say, our Developers and testers are aware of this issue, and they also recommend exporting to text files.

             

            TSGal

            FileMaker, Inc.

            • 3. Re: Import from Excel decimal errors
              gimbal
                

              Hi TSGal, thanks for the reply.

               

              The Developer/tester answer doesn't make sense to me, since the only program that touches the data is FileMaker itself.

               

              I did try your suggestion and created numerical data in Excel 2008, and Saved As "Tab-delimited text .txt" format, which did import into FMP9 without the decimal error. But I have developed solutions for clients based on the ability to quickly convert their Excel into FMP, so this problem will still cause us much grief...

               

              Are you saying the only safe way to import numerical data from Excel without decimal errors is to first save the data as text?

               

              That directly contradicts FileMaker's advertising, on your website right now: "It's easy to convert your existing data: Just drag your Excel files onto the FileMaker icon on your desktop, and the information will import automatically."

               

              Thanks for your help. I hope the Developers can figure this out. It seems that with all the computing power we have in this age, it should be easy for a computer to recongnize that 1.2 = 1.2.

               

              • 4. Re: Import from Excel decimal errors
                TSGal

                gimbal:

                 

                A lot of decimal numbers cannot be displayed properly using floating point arithmetic.  The XLSX format does not have this limitation, so hopefully, this file format will be included in a future release.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Import from Excel decimal errors
                  Leontine
                    

                  We just want to say that we have the same problem as gimbal. We are also using Excel 2008 and are forced to save as the old .xls format.

                   

                  The import problem usually appears with numbers containing 3, 4 or more decimals.

                   

                  This is a huge problem to us as we need to import thousands of samples from a chemical instrument that produces Excel files which we first process in Excel and then imports into FileMaker 9.0v3.

                   

                  It works as expected with tab-separated text.  However, it then takes forever to pair the large number of fields.

                   

                  We also would like to get an urgent fix to this unexpected and time consuming problem. Thank you. 

                  • 6. Re: Import from Excel decimal errors
                    TSGal

                    Leontine:

                     

                    Let's hope there will be support for XLSX format in FileMaker.  That gets around floating point arithmetic.

                     

                    Until then, use tab-separated text to get exact results.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: Import from Excel decimal errors
                      fwsue
                         This problem is also happening in Excel 2004, when both Excel and FMP fields are formatted as text, not sure if that was reported in the discussion. FMP9 Adv import from Excel 2004 for Mac 11.5.2.
                      • 8. Re: Import from Excel decimal errors
                        fh
                          

                        Using older Excel 2004 just partly resolves the problem, since decimal number < 1 still partly get the decimals messed up as described.

                         

                        Beside text-tab, one can also use old filemaker 6, where one isn't bothered with messed up decimals.