Hi, really odd one, I had to update an existing workbench that has been in place for at least 6 months. When it exports data from a sql server table to excel the featurewriter puts in 0's where there should be nulls. Another featurewriter using the same source but writing to csv behaves itself. Any ideas what could be going on? Some screenshots of the data going in and the design are below.
I have not upgraded the writer. This is the same if run from desktop or cloud.
Do you use an Excel template?
What is the FME version?
@nielsgerrits thanks for the quick reply. I am using 2020.1.3 I am not using an Excel template but rather setting the attributibutes from an empty sql server table
@nielsgerrits thanks for the quick reply. I am using 2020.1.3 I am not using an Excel template but rather setting the attributibutes from an empty sql server table
How do the zeros in the excel spreadsheet screenshot relate to the nulls in the inspector screenshot, they appear to show different attributes?
How do the zeros in the excel spreadsheet screenshot relate to the nulls in the inspector screenshot, they appear to show different attributes?
Sorry for the confusion, basically anything that is marked as null in the inspector is being populated with 0 when it goes into excel format. If you change to csv it remains as a null field.
Hi @oliver.morris ,
I noticed that in your workspace the attributes coming from the FeatureReader (generic port) are not exposed. Maybe this could affect the Excel Writer, so you could try to insert an AttributeExposer before the FeatureWriter, expose some of the attributes (maybe one or two, just for test) with null values and see how the Excel Writer writes the data.
Hope that helps!
Hi @oliver.morris ,
I noticed that in your workspace the attributes coming from the FeatureReader (generic port) are not exposed. Maybe this could affect the Excel Writer, so you could try to insert an AttributeExposer before the FeatureWriter, expose some of the attributes (maybe one or two, just for test) with null values and see how the Excel Writer writes the data.
Hope that helps!
@davtorgh thank you for the suggestion, unfortunately no joy:
@davtorgh thank you for the suggestion, unfortunately no joy:
Just to be sure, you are writing a new file each time? Not adding to existing?
And are you sure it is not a FeatureCaching issue?
@davtorgh thank you for the suggestion, unfortunately no joy:
yes, a new file each time. The bug was found when I tested the output from a workbench I had published to fme cloud so I dont think it is FeatureCaching. I just dont get why the csv works and excel doesnt.
@davtorgh thank you for the suggestion, unfortunately no joy:
A bit of a longshot, but could it be bulk mode related, can you put in a pythoncaller doing nothing beforehand to break the bulk mode?
@davtorgh thank you for the suggestion, unfortunately no joy:
thanks for the suggestion, it didnt work unfortunately, I will raise as a bug.
@davtorgh thank you for the suggestion, unfortunately no joy:
I discovered myself that if you read from a database (in my case it's a sqlite database) null-value data in a field defined to be of type number, for some reason the excel writer writes out 0 and not null values.
I agree with you @oliver.morris , this should be a bug.
@davtorgh thank you for the suggestion, unfortunately no joy:
thanks @davtorgh I did report it as a bug, no response yet though
Maybe you can trick FME by using the NullAttributeMapper, set all attributes to the value 'Jibberish' and then set all the values that are 'Jibberish' to NULL?
Maybe you can trick FME by using the NullAttributeMapper, set all attributes to the value 'Jibberish' and then set all the values that are 'Jibberish' to NULL?
Thanks for the suggestion but the tables here can be huge so I would really like to avoid this option if possible. The bug is with @daraghatsafe to review at the moment, will update this tread on the findings. thank you
Hi @oliver.morris ,
this issue seems related to numeric type fields in the source database tables and I think I've found a workaround. You can use a NullAttributeMapper to map null values to empty string before the Excel Writer.
Bye!
Hi @oliver.morris ,
this issue seems related to numeric type fields in the source database tables and I think I've found a workaround. You can use a NullAttributeMapper to map null values to empty string before the Excel Writer.
Bye!
apologies for the delay but this seems to have done the trick, thank you
Update on this issue:
This issue was resolved in FME 2021.1 and newer.
Null values will now be written as a blank value when writing to Excel.
You can get the latest version of FME here: https://www.safe.com/support/downloads/
-Andrea
Update on this issue:
This issue was resolved in FME 2021.1 and newer.
Null values will now be written as a blank value when writing to Excel.
You can get the latest version of FME here: https://www.safe.com/support/downloads/
-Andrea
Hi Andrea thanks for letting me know, I dont see the featurewriter as upgradable in 2021.1 it it just a general fix rather than transformer related? thanks
Hi Andrea thanks for letting me know, I dont see the featurewriter as upgradable in 2021.1 it it just a general fix rather than transformer related? thanks
The fix was with the Excel library so it wasn't specific to the FeatureWriter.
The update should happen automatically for the FeatureWriter when you upgrade to FME 2021.1 or newer.
-Andrea
Update on this issue:
This issue was resolved in FME 2021.1 and newer.
Null values will now be written as a blank value when writing to Excel.
You can get the latest version of FME here: https://www.safe.com/support/downloads/
-Andrea
Hi @andreaatsafe tested and now deployed to production, working as expected, nulls not 0's, thank you very much