Solved

FeatureWriter Exports to Excel are adding 0's into NULL fields

oliver.morris
Rank
Badge +10

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.

 

21 

icon

Best answer by andreaatsafe 3 September 2021, 23:37

View original

20 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

Do you use an Excel template?

What is the FME version?

oliver.morris
Rank
Badge +10

@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 table3

ebygomm
Rank
Userlevel 1
Badge +21

@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 table3

How do the zeros in the excel spreadsheet screenshot relate to the nulls in the inspector screenshot, they appear to show different attributes?

oliver.morris
Rank
Badge +10

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.

D
Badge +4

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!

oliver.morris
Rank
Badge +10

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:

11

nielsgerrits
Rank
Userlevel 6
Badge +32

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

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?

oliver.morris
Rank
Badge +10

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

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.

ebygomm
Rank
Userlevel 1
Badge +21

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

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?

oliver.morris
Rank
Badge +10

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

thanks for the suggestion, it didnt work unfortunately, I will raise as a bug.

D
Badge +4

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

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.

oliver.morris
Rank
Badge +10

@davtorgh​ thank you for the suggestion, unfortunately no joy:

11

thanks @davtorgh​ I did report it as a bug, no response yet though

jkr_wrk
Rank
Userlevel 3
Badge +18

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?

 

 

Sample: Every Nth Feature (N=1)
oliver.morris
Rank
Badge +10

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

D
Badge +4

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!

oliver.morris
Rank
Badge +10

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

A
Rank
Badge +10

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

oliver.morris
Rank
Badge +10

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

A
Rank
Badge +10

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

oliver.morris
Rank
Badge +10

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

Reply


Community Stats

28,710
Posts
109,487
Replies
36,559
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.