Skip to main content
  • EN

I’m using an FME FeatureWriter to read a content of an Excel file containing various values, including decimal numbers, some of them small with more then 5 decimal zeroes.

The FME, apparently, handles these small numbers using scientific e notation.

After processing the data, I write it into a CSV file using a FeatureWriter. The problem is, that the small numbers are written in their scientific notation form, and not as real decimal numbers. This is causing an issue further down the line.

I tried various things to force the FME to write the numbers as decimals:

  • forcing datatype of the affected attributes in the FeatureWriter to Decimal or Real
  • using AttributeManager to change the datatype of the affected attributes to Real, Decimal and even String
  • forcing the datatype of the affected attributes in the FeatureWriter to String
  • changing the datatype of the affected cells in the Excel file to Text

Nothing helped.

I tried to find a String or Math function that would achieve that, but I’m not sure if there’s any.

I’m considering to use a PythonCaller to write a script that would convert the numbers in scientific notation to real decimal values, but I would like to avoid it if possible. I’m also not sure if FME wouldn’t immediately convert those values to their scientific notation again.

Any idea what should I do?

Have you verified that the values you are reading from the excel file are actually decimal numbers, and not the scientific notation as text? 

 

In my experience, if the numbers are actually decimal numbers but just displayed with scientific notation in fme then just sending them to the csv writer will work

 

If however, your source is actually numbers in scientific notation, then a stringformatter should work to convert to decimal. 

 

Have you verified that the values you are reading from the excel file are actually decimal numbers, and not the scientific notation as text? 

 

Yes, I checked it. The values are decimal numbers:

Small decimal in XLSX

 

In my experience, if the numbers are actually decimal numbers but just displayed with scientific notation in fme then just sending them to the csv writer will work

 

 

This doesn’t seem to be the case. After the reading these decimal values are shown in e-notation format, both in the Inspector table and in the FeatureInformation tab:

Small decimal in FME inspector table
Small decimal in FME inspector Feature information

And it’s written in the same form to the CSV:

Small decimal as written to CSV

 

If however, your source is actually numbers in scientific notation, then a stringformatter should work to convert to decimal. 

The StringFormatter, however, seems to solve the issue. 

Force decimal in StringFormatter

Although the value is still shows in e-notation in the inspector table, it is shown as a real decimal in the Feature information, and is written as a decimal to the CSV.

Small decimal in FME inspector table after StringFormatter
Small decimal in FME inspector FeatureInformation after StringFormatter
Small decimal as written to CSV after StringFormatter

I must say it’s strange. The value is already a number before the StringFormatter, so why it is written  to CSV as e-notation string, but once I format it it’s written as a real decimal? Is this a feature or a bug?

Has it changed how this works? (We recently updated to version FME 2024.1.2.1.)

Just to wrap this up, in order to avoid the small decimals being written in their scientific E notation form to the .csv, I did the following.

In an AttributeManager, a conditional value is set for all numeric attributes where this may become a problem.

In the Test part I used CONTAIN REGEX to do it only for values which contain the E notation with negative exponent (I have not detected FME doing the E notation for large numbers, only for the small ones). The regex test is case insensitive.

The value is set using a @Format function to enforce the decimal form on the value.

It’s nested inside a @TrimRight function, which removes the excessive zeroes added by the @Format.

An example:

 

Reply


Community Stats

30,351
Posts
115,341
Replies
38,082
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.