Question of the Week: Writing Fixed Length Numbers to Excel

Today we're looking at how to write numbers to Excel, and preserve their formatting.

This question was asked by @bernarddinon who wanted to know how to preserve number formatting to 3 decimal places when writing to Excel.

Q) In my workspace, I use LengthCalculator to measure "Distance". I'd like to have three Numbers after [i.e. 3 decimal places]. I use Stringformatter on this attribute with Format String .3f but all value not have this setting.

Someone can help me to fix this?

A) There's a mix of factors here because you are calculating a number, viewing it in FME, and then writing it to Excel. Any one of those factors could affect the result you want.

Let's look and see what is happening...

Here's the issue in brief.

A length is calculated. Let's say it is 123.45678. This number is passed through the StringFormatter with a format set to .3f (i.e. give me a number with 3 decimal places). The result is 123.456

However, if the length was calculated as 123.4500001, passed through the StringFormatter transformer it appears to be 123.45, instead of 123.450.

It could be handled in Excel, but the user wishes for FME to fix the problem.

When I said that the number "appears to be" missing the end zero, I chose my words carefully. Why? Because all of the screenshots I saw were of the Table View window:

But for me the ultimate source of truth is either the Logger transformer or the Feature Information window. So I created an area feature and measured it, formatting it to 3dp:

And when I looked at it in the Logger or Feature Information window, I saw this:

The StringFormatter has made it 3 decimal places, it's just that the Table View is like Excel. It will reduce the data to fewer decimal places if they are zero.

So actually the result is correct! And FME has converted the data from numeric to string (real64 to UTF-8) in order to achieve this.

But what about writing to Excel?

To an extent, when the data goes into Excel, it's out of our control. The basic solution there would be to open the data once it is written and change the column display to 3 decimal places:

The user (understandably) wants an FME-only method. While FME has some formatting tools for each column, but it doesn't have the ability to set decimal places:

So what can we do? Well there are two solutions...

Firstly we can change the column type written to Excel to be a string, not a number:

Excel will recognize it as text, not a number, but it will be formatted as required.

Or, secondly, we can set up the number formatting in an empty Excel spreadsheet, and use that as a template to write to:

I tried that out. Let's see what I got...

• Column 1 is writing the data to Excel as text, untouched in the Excel template.
• Column 2 is writing the data to Excel as a number, with Excel set to 0 decimal places.
• Column 3 is writing the data to Excel as a number, with Excel set to 3 decimal places.
• Column 4 is writing the data to Excel as a number, untouched in the Excel template.

You can see that when I write the data as text, Excel puts a green marker in the cell to indicate that it realizes there is a type mismatch. So the safest way is to use a template - with that column set to 3 decimal places - and use that to write data to.

I've made a template of my workspace here:

This has been a crazily busy week for some reason, and there have been many questions I could highlight. Plus a few support questions from last week finally got solved and I thought they warranted highlighting.

So here are a choice selection:

• How to Install a Python Library on FME Server
  • In the support queue a user said: "I want to run a workspace that uses a Python library. I tried to put the library in the resource folder, but it failed. Can you help?"
  • Absolutely! As my colleague Laura explained, the solution is to ensure the library goes into the correct folder. If the library is for Python v3.6, then you need to copy it to Resources > Engines > Plugins > Python > python36. Once the user chose the right folder, it was all good.
• Bug in the VariableRetriever?
  • User @jlbaker2779 found that the VariableRetriever didn't retrieve variables when the input came from a Creator transformer. As I mentioned in a previous question-of-the-week, I don't really like variables for the reason that you need to work harder to get them in the correct order. Here the Creator feature was likely trying to retrieve a variable that had not yet been set. When - as @david_r suggested - the Creator was set to "Create at End", it fired in the required order.
• What will be the performance impact if the directory watch time interval is small such as 4 sec?
  • This question came to the panel during the Schema Drift webinar this week. The answer is that watching won't take up any engine time or license because these watches are run by the FME Server core. However, it is an extra process that's running (you should be able to see it on the Task Manager I'm told) so it'll be fighting for processor space.
  • We suggest a 1 minute minimum interval, but I don't know if we've really analyzed anything to come up with that number. But 4 seconds is getting quite short. Less than that and I would argue that it's real-time, and for that you need to go to continuous monitoring. Set up a workspace to listen for data (usually like a tcpip packet) and have it run permanently. It takes up an engine, but it does give you near instantaneous response time.
• Measuring Geodesic Length
  • Another question from the support queue. How do you use the GeographicLengthCalculator to calculate geodesic length? The answer - courtesy of Dave Campanas - is that this transformer assumes that data in a projected coordinate system (as opposed to lat/long) should return projected length. But if you reproject your data to LL84 first, the GeographicLengthCalculator will internally reproject to a local Azimuthal Equal Distance coordinate system before measuring the length. This local coordinate system has almost no distortion over short to medium distances, so while the length calculation will not be a true geodesic, it will be extremely close.
• Why is PDF color different in FME to in Adobe Acrobat/PDF Reader?
  • This question from @disco2000 also came in through the support queue and I helped my colleague Christian figure out the issue. Basically you can set up a PDF for either printing or viewing on a computer. When it's for printing you fix the colors absolutely, even if they look different on a computer. That's what was happening here. Alternatively you can flip a switch in ArcGIS to say "Preserve Color Values". When you do that, then the colors are preserved on a digital display.
• The InlineQuerier and the SQL Except Command
  • So, this is a follow-up to the question-of-the-week about using the InlineQuerier for lists. It came in handy this week for the Schema Drift webinar. Basically schema drift is when the schema defined in your data changes from what is defined in FME. We can read the schema from a dataset and compare it... except that the schema is returned as a list attribute. So explode the list, dump it into an InlineQuerier, and you can use SQL to compare the two. The nice part is that you can use the Except command to find the different; so:
    • select columns from schemaA except select columns from schemaB
  • Here's that does this schema test.