• Home
  • Forums
  • FME Form
  • Data
  • Working with Excel writer data types / numeric values output as text vs. number in Excel
Question

Working with Excel writer data types / numeric values output as text vs. number in Excel

  • 26 August 2019
  • 3 replies
  • 43 views
bwasserstein
Rank
Badge

Hello there.

I'm having trouble with an Excel writer.

Where the value being read in FME is completely numeric, the data value is output with an additional '.0' suffix. I've tried changing attribute data types to auto or string, and I tried choosing 'Treat as Text @' from the attribute formatting/'Edit...' button in the writer - no luck.

Example...

I've got the following values being written to an attribute in an Excel writer...

  • TEST VALUE HERE 1234
  • 1234567
  • ABC1234

And it is outputting the following values to Excel...

  • TEST VALUE HERE 1234
  • 1234567.0
  • ABC1234

I don't want the ".0" attached to the end of values that are fully numeric, rather I want all of the values in the attribute to be treated as a string.

Perhaps this is just an issue with Excel itself. When I look at the cells with these particular values, Excel gives me a warning box that shows 'Value stored as text'; when I choose 'Convert to number' the formatting appears as expected (without the additional '.0' suffix on each value). I don't want to have to manually go into Excel afterwards to change the formatting of these values.

I've tried setting the attribute definition to 'manual' rather than 'auto' and changing the data type to a string, but this still is not correcting this formatting issue.

Anyone have any ideas on how to handle this?

3 replies

drc43
Rank
Badge +10

Are you using a template for your Excel writer? I know when I've had issues with formatting in Excel, I will write the data into a different tab and then have the template read that data into the first tab that has all the formatting that I want. This has worked well in the past, but I know it doesn't work for every scenario.

bwasserstein
Rank
Badge

Are you using a template for your Excel writer? I know when I've had issues with formatting in Excel, I will write the data into a different tab and then have the template read that data into the first tab that has all the formatting that I want. This has worked well in the past, but I know it doesn't work for every scenario.

I am using a template for the Excel writer, but with this particular sheet I've got the writer mode set to insert & output field names set to yes. Thus, this particular spreadsheet tab is just a blank sheet in the template.

Since the attribute names in this sheet wont change, I added them into the template .xlsx and specified the data types as 'general' in excel for the particular problem columns. I also tried setting this to 'Text' or 'Number' with 0 decimal place precision, and no luck. Excel still displays the numeric values with the added '.0' along with the 'number displayed as text' error. When I choose 'convert to number' in excel, then it converts the value to the actual value without the one decimal place precision.

Screenshot of the issue in Excel:

When I choose 'Convert to Number' it displays the correct value (133746 in this example).

bwasserstein
Rank
Badge

Found a fix for this awful Number Stored as Text/Convert to Number Excel issue.

I set the Writer's User Attributes to a 'number' data type for the particular problem attributes. The majority of values in these attributes are not solely numeric, but regardless, they still get written whether they're numeric or not. It seems that with this fix, now these particular attributes default to a 'number' data type, but if any particular value doesn't fit that, then either FME or Excel converts the cell formatting accordingly to 'text'.

As a result, my data values that are fully numeric get written exactly as they should (without the '.0' suffix), and any non-numeric data values get written as 'text'.

Oddly enough though, if I look at the cell formatting in excel, it shows everything (including the original problem numeric values) as being 'text' even though I set the writer to 'number'.

I don't know why that is, but there are no more added '.0' suffixes on my data values that are all numeric.

Problem solved - thanks for helping me troubleshoot!

Reply


Community Stats

28,706
Posts
109,466
Replies
36,549
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.