4 Replies Latest reply on Apr 2, 2012 4:58 PM by Malcolm

    Importing Numeric Data in from Excel

    gladams

      I am having problems of importing numeric data with decimal points in from Excel, in that it often puts a whole string of zeros after the last digit with a number at the end. e.g. 1.874 could go to 1.8740000000000000000002 or it does the opposite and result is 1.873999999999999999999 or similar. I've tried making the field in Excel and FileMaker as text fields, but even this does not overcome it. Any suggestions on how to avoid the above would be greatly appreciated.

       

      Thanks

       

      Graham Adams

      PlesTech Ltd.,

        • 1. Re: Importing Numeric Data in from Excel
          ch0c0halic

          Graham may have found an ant hill,

           

          That is a known problem with Excel.

           

          FMI Knowledge base.

          Decimal numbers are inconsistent from the original data entered when importing from Microsoft Excel in FileMaker Pro

          Answer ID: 9281

          1 of 1 people found this helpful
          • 2. Re: Importing Numeric Data in from Excel
            Vaughan

            Set the number fields with autoenter calc Round( self ; n ) where n is the number of digits you want to be displayed. Using your sample data, you could probably have n as large as 6 to avoid losing significant digits.

            • 3. Re: Importing Numeric Data in from Excel
              gladams

              Thanks for the replies. Unfortunately the data imported varies considerably in numeric range, so rounding the number is not really a solution. I have instead captured it as text and given it a length of 12 characters, but it does not look very pretty or professional. The import is used to automatically update data on customers machines when they open the program, but there are a considerable number of fields and writing a script to tidy up the values slows down the opening of the database when there are updates.

               

              Looks like I will have to live with it, if it is a known problem

              • 4. Re: Importing Numeric Data in from Excel
                Malcolm

                Thanks for the replies. Unfortunately the data imported varies considerably in numeric range, so rounding the number is not really a solution.

                 

                Excel to CSV

                CSV to FMP

                 

                 

                Also, you can use number formatting options to control the display of numbers. You might want to display at three significant figures. The data remains the same and can be revealed by entering the field.

                 

                Malcolm