I am struggling with a similar problem as this post (I think?). One of my attributes in the Workspace contains a 0 or 1 that is used as a Boolean. The value has been assigned the datatype ‘buffer’ in AttributeCreator and ‘string’ in the FeatureWriter, which outputs an Excel file. I am using a template, where the cell format is set to ‘General’.
For some reason, the Excel file that is generated will give me inconsistent formatting of the attribute value:
What is going on here???
As you can see, even though they all should be interpreted as strings, some of the values become numbers. The formula bar does not show anything different. I started using the Excel template in hopes of making the data format consistently, but even when I changed the cell format to ‘Text’ it still gave me issues when being read by Python.
I would like to solve this issue before reverting to a Yes/No or True/False format. What could be causing this? Any insight is appreciated.
-Thanks
Page 1 / 1
Hello @larue, are you able to share a screenshot of the writer feature type user attributes definition? (eg. Open FeatureWriter > click User Attributes section). Also, are you using a template in the excel writer? Best, Kailin.
Hi @kailinatsafe,
Here is a screenshot of the FeatureWriter User Attributes for the sheet in question:
...and just for thoroughness, here it is when set to Automatic:
I have it set to Manual because I don’t want the extra attributes showing up in the file.
Yes, I am using a template Excel file, see attached. The strangest thing is that it doesn’t matter if I manually format the columns/cells to text after running the Workspace. I have to cut and paste the ‘1’ and ‘0’ from one of the cells that works (text) into the respective ones where it is justified right (number). Then, Python will see it.
Hi Larue,
I tried to reprodcue this in FME 2023.2, but fortunatly it works how it should be.
Which version do you use? and can you reproduce this by isolating the process of writing 1 or 0 to an xlsx with a featurewriter?
Kr,
Job
Hi @jobvdnoort,
I am using 2023.2.4 and was able to get the exact same result when replicating your Workspace.
But, that is not what I am going for. What you are showing are numbers. I want text, where the ‘numbers’ are left-justified. Interestingly, the Boolean attribute is set to ‘string’ in the FeatureWriter, yet returns a number in Excel. Is that what you are getting also? Why is it not showing up as text?
My own Workspace does this behavior consistently, meaning that the same specific features are coming into the Writer and show up in Excel with the same contrarian format! These features are not coming from an isolated source but go through the same pipeline. There doesn’t seem to be anything different from the rest of the features.
I tried setting the Excel template cell format of the Boolean columns to Text. The output shows all left-justified. When running my Python script with this file, it ignores the same features that were shown right-justified in the screenshot from my original post. So, it doesn’t seem to matter what the format or justification of the cell is.
Unless another idea is put forward, I’m going change to Yes/No or true/false. Your mockup Workspace seems to show that you cannot truly change a number to a string when creating an Excel file with FME.
Thanks,
@larue
Hi @larue . I cannot say whether this is intended behaviour, but in my FME 2021 workspace in my test Excel writer, a “String” data type does not give a Text Format in the Excel cells.
Instead this had to be set in the separate Formatting parameters for the Attribute, an set the Numeric Format as “Treat as Text”.
I’m curious though why you would want to treat a Boolean as Text though? By definition, they are a 1 bit digital number and are normally handled/interpretted as an integer data type.
Hi @bwn,
Those are good points. To treat the 0 and 1 as integers was the original plan. Somehow in the process of troubleshooting, I started using text. You know what, I need to circle back on that. I was fixated on why the Excel output was doing random things! Plus, I was used to FME interpreting strings as numbers when needed. I think I can close this post for now. Thanks for the reality check!
