1 Reply Latest reply on Jan 12, 2011 9:16 AM by philmodjunk

    Decimal Place Control with Excel Recurring Pulls

    kingsley47

      Title

      Decimal Place Control with Excel Recurring Pulls

      Post

      I am pulling data into Filemaker Pro from an excel file via a recurring data import. One field in the excel file contains names of customer specifications. The name of each specification is either an alphanumeric code or just a number. The alphanumeric combos pull in just fine, but the specifications whose names are something like 70.80995 get changed when they are imported into filemaker pro. Instead of 70.80995 appearing in the field, 70.809950000000001 will appear. How can I set it so that filemaker pulls the exact text from excel.

      Limiting the number of values after the decimal place is not an option because the number of values following a decimal vary depending on the specification name.

        • 1. Re: Decimal Place Control with Excel Recurring Pulls
          philmodjunk

          There's a difference between how FileMaker and Excel handle floating point numbers. Both use an internal "binary scientific notation format" but with a different number of digits in the mantissa if I've decoded the info from Filemaker correctly.

          The best solution I can recommend is to follow up your import with a Replace Field Contents operation that rounds the contents of this field slightly. This can be done manually or in a script.

          You can use the calculation option of Replace Fields and use something like: Round ( YourField ; 11 ) to clean them up.

          While FileMaker argues that this isn't really a bug...

          For More Information see:    Import from Excel decimal errors

          This is one of many acknowledged issues that can be found in the Known Bug List here in the Report an Issue section of the forum.

          It can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html