Issue with importing numbers from Excel spreadsheet.

Example:

The data on the Excel spreadsheet is 1.100 but it imports 1.1000000000000000888 in to the field in Filemaker. Why is this happening?

Issue with importing numbers from Excel spreadsheet.

Example:

The data on the Excel spreadsheet is 1.100 but it imports 1.1000000000000000888 in to the field in Filemaker. Why is this happening?

- 1 person found this helpful
See this knowledgebase article

https://support.filemaker.com/s/answerview?anum=000024383&language=en_US

It's a difference into the internal level of precision used by the two applications. It's easily fixed with a bit of rounding via the round function.

Neither the field on the spreadsheet nor the field in FileMaker are calculation fields. The 1.100 on the spreadsheet is not the result of a calculation. The actual amount in the field on the spreadsheet is 1.100 but when I import it into the Filemaker field, the value is 1.1000000000000000888. I could make the FileMaker field automatically round but I do not want to use the "Perform Auto-enter options while importing because other fields in the import should not use the auto-enter option as it would auto replace in unintended places.

Dear CarlaS, I don't understand your comment about Auto-Enter calculation because it is specific to each individual field so if you setup for one field in particular other fields won't be affected. That said, you might have meant other records, in that case yes the same field for all records imported will be recalculated with the Auto-Enter option.

Yes, I meant that I do not want to use the Perform Auto-Enter option while importing because it would have unintended results for other records in the import. Basically, I am importing data provided by a vendor to verify that the data they have is in sync with the field we have in the FileMaker database. I do not understand why the data is not imported with exactly what is in the spreadsheet.

So if you only have the problem for one record, just put your cursor in the field and go to menu Records > Replace Field Contents... > Select Replace with calculated result: Specify... and enter your rounding calculation:

Note: Self instead of the table::field does not work.

You can also do the Replace Field Contents in a script as follows:

**Replace Field Contents [With dialog: Off; table::field; Round(table::field;2)]**philmodjunk - maybe its time for there to be an option on the import dialog box that would get round this issue?? If I can find the words then I feel a product idea coming on... It's frustrating for newer users..

haha maybe that "option" could just be a checkbox that triggers the same floating point arithmetic that excel uses. Or better yet, if a excel file is selected it automatically converts to a simplified .txt file as an intermediate step prior to import.

CarlaS could you convert the excel file to .txt tab delimited prior to import? In my workflow, I have to do this step due to the small difference affecting downstream calculations. An example: in my excel template i wrote a very small VBA macro, that saves a copy of the sheet(s) to the user's temp directory as a tab delimited .txt file. A FMP scripted import then references this location and imports from that temp .txt. This circumvents the direct Excel's floating point direct FMP import issue.

workflow: excel template -> click button -> creates .txt file -> run FMP import script

“maybe that "option" could just be a checkbox that triggers the same floating point arithmetic that excel uses.”

read the knowledge base article. There is no “arithmetic” involved.

“Or better yet, if a excel file is selected it automatically converts to a simplified .txt file as an intermediate step prior to import.”

I think that would cause problems with other data types.

I suggested that but apparently CarlaS does not want that option or maybe I wasn't clear enough and you are making it very clear now.

philmodjunk wrote:

“maybe that "option" could just be a checkbox that triggers the same floating point arithmetic that excel uses.”

read the knowledge base article. There is no “arithmetic” involved.

hmm I have and did again.... i think the point is that FMP handles things differently than Excel which uses IEEE 754 standards. The option would use IEEE 754 standards + the same rounding, instead of non IEEE754 standards or IEEE754 + Rounding. (unsure what FMP uses)

KBA description:

"There's a difference between how FileMaker and Excel handle floating point numbers. FileMaker Pro does not round values for display using the same rules as Microsoft Excel".

microsoft description:

"Microsoft Excel was designed around the IEEE 754 specification to determine how it stores and calculates floating-point numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today's PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.

When numbers are stored, a corresponding binary number can represent every number or fractional number. For example, the fraction 1/10 can be represented in a decimal number system as 0.1. However, the same number in binary format becomes the following repeating binary decimal:

0001100110011100110011 (and so on)

This can be infinitely repeated. This number cannot be represented in a finite (limited) amount of space.

**Therefore, this number is rounded down by approximately -2.8E-17 when it is stored**."EDIT****

Example of differences in calculations Excel vs FMP with calc results of "1/11"; Display rounding is another topic, but at end of the day they are different numbers

CarlaS wrote:

Yes, I meant that I do not want to use the Perform Auto-Enter option while importing because it would have unintended results for other records in the import. Basically, I am importing data provided by a vendor to verify that the data they have is in sync with the field we have in the FileMaker database. I do not understand why the data is not imported with exactly what is in the spreadsheet.

Please see previous comment. It has to do with how the data is stored and nothing to do with "rounding display" (rounding functions wont be consistent if the number stored is different) using a round work around only masks the problem. When FMP imports from Excel it appears that it takes the "stored" binary value and imports exactly as such. This stored binary value has already been modified by an internal rounding function within Excel. :

On the Excel Side: I believe the Excel stored value and it's respective display, is augmented within excel to account for the ~2.8E-17 rounding of the binary stored values. By converting the Excel file via Excel to a format other than Excel, it seems to strip the numeric stored value (which is different due to the rounding) and exports the Excel Display value as a text string equivalent to the Excel "Display" string.

e.x. .1 is no longer stored as "0.0001100110011001101..." but it turns into a character string of "." & "1" binary(00101110 & 00110001)

Thank you all for your input. This has been a frustration for several years. The import is automated. The excel file is received from the vendor and imported and I have no control of the formatting on the spreadsheet.. The file is in the old XLS format not XLSX. There is no formatting in the excel spreadsheets. This issue happens with multiple records (but not all records) when doing the automated import and then has to be manually corrected. Using the Round function does not work because the data on the spreadsheet can have numbers with varying number of decimal points.

I saw that you gave her a correct way, but judging by her comment it appeared she may have misunderstood or glossed over your explanation.

The bigger problem is it seems the like you have to go to the source and correct it there, or pick a precision amount in FM and go with that.

I pretty much twice weekly, do an import from QB to Excel to FM, and never see a problem with any numbers.

400 digits huh? haha you can joke about it, but there is a difference between precision and accuracy. At the end of the day, the numbers are different due to methods and therefor

**not equal**. those differences can be consistently 'wrong', or consistently 'right' depending on the context. if being right or wrong inst as important as being consistent, then please go ahead and poke fun at precision... in the OP, it is assumed being 'right' is important, and this difference methodology is significant enough to cause problems even though the precision is well beyond the hundredth or thousandths place. FMP & Excel is neither right or wrong. They are just different.an example is, in my application the numeric values are generated via a log scaled process. it generates a value to the -9 place that initial value is then converted into a numerical value that is used in medical research. In my specific case, the differences between excel "rounding" of floating vs filemaker caused an min of ~2% difference in accuracy and increased significantly due to the nature of initial value. this 'error' in some cases reached nearly 10% difference in calculation methods (Excel vs FMP). It just happens every collabrator and research lab uses Excel over FMP, so in my case FMP is "wrong" relative to 20+yrs worth of international research and data

Just imagine your accountant or business CFO inform you that your budget is 10% over because he decided to use a different brand calculator, then dismiss the significance with a " Its just 10%"

CarlaS, I'll jump on the answer as I originally suggested the correction that SteveMartino reiterated.

If I understand correctly, you have problem with one field at time of import so my suggestion was to apply an Auto-Enter calculation in the field definition for that field only. To do so: Go to File > Manage > Database... > Table Tab > choose the table where the field is located > then double-click on the field in question then first tab: Auto-Enter > Check Calculated value Specify... and enter Round(Self;2).

Once done 3 times OK to go back to your layout. Find all the records that have a field with problem and modify the contents of all these field once for all (after that the auto-enter calculation you just entered will tke over). Once the records found, put you cursor in the field in question and go to menu Records > Replace Field Contents... > Check Replace with calculated result: Specify and enter:

Replace Field Contents [With dialog: Off; table::field; Round(table::field;2)] then ok

This will correct all the field that were imported prior to the Auto-Enter

Thank you Thierry. The reason I cannot use this method is that the field in the Excel file contains data that can be any number digits after the decimal point. Some have no decimal points, some have several digits after the decimal points so I cannot use the rounding function because I wouldn't want it to round numbers with many digits after the decimal point.

Understood, so you could maybe use the second part which is to Replace field Contents from the top menu (that can be done by script as well). Also, if you wish, I would not mind brainstorming with you on a logic to sense how many decimal digits would be relevant and determine on the fly what would be the appropriate rounding for each individual field.

May I ask what data from Excel you are dealing with? I am an engineer and I cannot imagine that many decimal digits would be really relevant (there are some cases but not many), maybe a consensus could be made (on your side with people using the data) on how many decimals would be relevant, if you know that then you could keep the field(s) the way it is but just format the display (4th tab of inspector) to only display 2, 3, 4, n digits while FMP will still store the full length of digits so calculation will be accurate but display will be approximate.

I just did a test importing from Excel 1.100 into a Number field and into a Text field. Both answers are the same but different from your import and I wonder why. See picture below:

a 1 at the 16th decimal is not relevant (I do not criticize your approach, just trying to help). Another approach if critical could be to import the numbers and the formula that create that 1.10 and have FMP to do it.

- 2 people found this helpful
Maybe the difference is due to how the original excel is generated. as mentioned before it is how excel decided to handle floating point and the respective binary storage requirements. Excel rounds to nearest 2.8e17in order to truncate a repeating binary value so that it fits within its stored value constraints Whereas a different software could perhaps perform the truncation and rounding using A different method. Much like FMP doesn’t round and appears to just truncate. All the software tare doing is reading the augmented binary and translating the binary string in different ways For display purposes. That’s why excel cell value of 1.100 expanded past 16th places will still be viewed as 1.100000000.... within excel and fmp reads it as 1.10000...1 Excel knows it’s internal method and can ’reverse’ it for display whereas FMP does not know excels internal method and translates the stored binary in a way it knows how to. In the end the raw data is different and i Suspect FMP just imports the raw data.

If you want exact replication, that binary storage modification or the translation compensation mechanism included relative the the specific ’compatible’ softwares is needed. ATM FMP doesn’t compensate for the excel binary modification so therefore the only option is to eliminate the modification from Within excel. A txt file conversion seems to a common file type where excel and FMP translates the string the same way.

i suspect an excel vba macro, not a worksheet macro, could resolve This issue. I will work on something and report back. I have a few ideas I can try.

I am not sure that Excel is the culprit of anything as I plugged 1.10 in my spreadsheet so there was no rounding to begin with.

What I was trying to express is that between 1.10 and 1.10000000000000001 the difference is negligible. I am curious to know the unit. But CarlaS might have a boss who sees a difference between these two numbers so that is not my business to say what's right or not but I have an opinion.

Thierry, You are right, the difference is negligible but the whole reason for the import is to import data from the vendor's database in order to compare and verify that it matches the data in the FM database exactly. The field is a per unit cost. I have found manual ways to modify the Excel file so I import the data exactly as the vendor exported it from their database. The problem is that I want users to be able to click a button in FileMaker to import the spreadsheet without ever opening it. Until I can find a real fix for this, I will continue to download the excel file from the vendor, modify it, and then import it into the FM database. I will ask the vendor if they can provide their output in TXT or CSV file formats to see if that would work.

- 2 people found this helpful
CarlaS wrote:

I will continue to download the excel file from the vendor, modify it, and then import it into the FM database. I will ask the vendor if they can provide their output in TXT or CSV file formats to see if that would work.

as previously suggested saving the excel worksheet as txt then importing that text file will be easiest and fastest way, no need to modify any of the data. Whatever is being displayed will be saved exactly the same in a txt. an excel add in with a quick macro script to save as format type txt can be created so that all you have to do is click a button in the toolbar and its saved, then the FMP script can be duplicated and changed to that txt source.

- 2 people found this helpful
Excel have only 15 digits Excel specifications and limits - Excel

then further digits are not need in FM.

Round(Self;16-Log(Self))

(not much tested)

So if the unit is a cost per unit, I cannot remember if I have ever seen a cost more precise than 3 decimal digits. Maybe the Truncate function could do the trick like Truncate(number;precision).

Is 1.100 the result of a calculation?

You can set up the FM field in the inspector to show any number of decimal places, or even an Auto Enter calc for the field.