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.
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.
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.
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?
Export it as a merge file, but name your path as .csv. Using this technique you can export a CSV file with column names.
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.
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.