8 Replies Latest reply on Feb 18, 2014 5:36 AM by mikebeargie

    Bug in import from Excel

    manjit

      Hi

       

      Since from a long time development in Filemaker I am facing a strange issue while importing data from an excel file to my Filemaker application. In the excel (.xls/.xlsx) the data are showing correct (e.g. 1.1524) which is entered by me but when it get imported into Filemaker its showing a different value (e.g. 1.1524000000000001).

       

      This issue was not showing if I do import for same data using CSV file into Filemaker.  I have tried a lot by removing all type of formatting from the excel file even I made the column to text still on import the data in Filemaker is showing wrong data. Is there anybody faced the same issue and got any solution please share with me.

       

       

      Thanks,

      Manjit

        • 1. Re: Bug in import from Excel
          jlamprecht

          Hi Manjit,

           

          Is this Min Weight field calculated from any other fields in Excel? Also are you rounding the number in Excel using formatting? I believe Filemaker ignores that, but have not done an import on that kind of data. It is something to check, however. If someone else with experience can chime in that would be great.

           

          -JohnAustin

          • 2. Re: Bug in import from Excel
            mikebeargie

            John is correct. This is resultant of the raw data being output by the xlsx format. CSV is a format that stores no calculation information as XLS/XLSX does so it is not affected by this issue. Excel has the same type of display options that FileMaker has (IE, a FileMaker numeric field can be set to only display 2 decimal places, or currency, etc..)

             

            Excel is displaying a shortened value of the field based on it's display settings, but the raw value is different.

             

            Either use CSV to get an expected result, or modify your calculation & formatting in excel to output a more compatible result.

            • 3. Re: Bug in import from Excel

              The difference you notice is a floating decimal issue and has been described here:

               

              http://help.filemaker.com/app/answers/detail/a_id/9281/kw/floating%20decimal%20excel

              • 4. Re: Bug in import from Excel
                manjit

                Thanks LaRetta for your attention.

                 

                I have already followed the link mentioned by you.

                By using CSV we can get rid of that but the thing is that I want a resolve solution for our users. CSV does not export column name while exporting data from Filemaker. So always the system will export data to Excel file and then user enter/modify the excel file and save them to CSV format and then do the importing.

                I think this issue is a genuine in case of Filemaker and the Filemaker development team is not yet taken a step to resolved this issue.

                 

                 

                Thanks,

                Manjit

                • 5. Re: Bug in import from Excel
                  manjit

                  Hi John and Mike,

                   

                  - I have tried by exporting the data as TEXT and import as TEXT but that also showing the same bug.

                   

                  - 2nd attempt I have created a new Excel file removed all formatting and made all the columns as TEXT and enter few numeric data as described above and do the import but the Filemaker doing something strange with the excel import due to which some garbage data are adding into the field.

                   

                  - In my application there is a restriction of number of decimals which should not exit 4 digits where as Filemaker itself adding some additional decimals so the vali8dation failed and its discard the imported data and no records getting import each time.

                   

                  - If I use CSV that again headache as I need to export the column headers as thats also coming under validation in my application.

                   

                  - Can we take an attention from Filemaker development team to be focus towards our problem and give a better excel import feature in the next updates?

                   

                  Thanks,

                  Manjit

                  • 6. Re: Bug in import from Excel
                    mikebeargie

                    Export it as a merge file, but name your path as .csv. Using this technique you can export a CSV file with column names.

                    • 7. Re: Bug in import from Excel
                      manjit

                      Thanks Mike for your nice tips.

                      By using this method we can able to solve the issue and that is possible only if the export/import is doing by a FM professional. But here the thing is that export/import are going to do by the end user and we cannot advice an end user to do that much of complexity. Why don't we ask Filemaker development team to look for a fix to this bugs in Filemaker.

                       

                       

                       

                      Thanks,

                      Manji

                      • 8. Re: Bug in import from Excel
                        mikebeargie

                        Don't wait for FileMaker, because these are not bugs.

                         

                        The CSV format has no spec for a header row, but the .mer (merge) format does. Your other issue is a problem with the Excel format as LaRetta pointed out, it's not a bug, it's a difference in how two systems handle floating point decimals.

                         

                        You can script your export to automatically designate a name so the user doesn't need to interact with the dialogs. You can also script your import to handle the floating point decimals.