FixExcelImport ( value )

Cleanup imported numbers from Excel, suffering from rounding issues

Average rating: 3.0 (2 votes) Log in to vote

Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Sample input:

FixExcelImport ( 497.69400000000002 )

FixExcelImport ( 296.64999999999998 )

FixExcelImport ( "296.6499.99998" )

Sample output:

497.694

296.65

296.6499.99998

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// FixExcelImport ( value )

Let(
	[
		notDecimal = PatternCount( value ; "." ) ≠ 1 ;
		fractional = Middle ( value ; Position ( value ; "." ; 1 ; 1 ) + 1 ; 64000 );
		frac0 = Filter( fractional ; "0" );
		frac9 = Filter( fractional ; "9" )
	] ;

	Case(
		notDecimal ; value ;
		Length( frac0 ) < 5 and Length( frac9 ) < 5 ; value ;
		Length( frac0 ) < Length( frac9 ) ; Round( value + .0000000000005 ; 11 ) ;
		Round( value ; 11 ) 
	)
)

When importing data from Excel, data which could be interpreted as numeric in Excel may sometimes be imported with excessive decimals, due to the way Excel stores numbers. This is a quick & dirty fix for this. Add a call to

FixExcelImport ( Self)

in the field definition for any field you're importing into, and it should be cleaned-up.

The rounding (11) and length checks (5) can of course be changed to suit. :-)

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

FixExcelImport ( value )

Comments

Support this website.

Support this website.