Question

Zeroes Dynamic Excel Writer

  • 25 February 2019
  • 4 replies
  • 6 views

Badge +7

Hi,

I have a problem setting up a dynamic excel writer. If an input attribute has leading zeroes, they are removed in the output excel file. Can this behavior be prevented somehow? The input excel files are read via a FeatureReader.


4 replies

Userlevel 4
Badge +25

This is interesting: from what I can tell the column header in Excel is of the "general" type and the cells with the values in them are "text" type, but when you check out the schema as read by FME it's a 'number' type and thus it gets written as a number and all leading zeroes are removed.

Even when I specify that column header cell to be "text" as well FME still reads it as a number and thus outputs a number.

I don't really have a handy workaround for this I'm afraid, especially not when it has to be dynamic.

Badge +2

I tested this in FME 2018.1 and FME will preserve the leading zeros IF you set the source data type to 'string' in the Excel reader parameters in your FeatureReader. BUT... the parameters are very sensitive and will always try and reset to the 'scanned' data type. So every time you open the Excel reader Parameters dialog, FME will set the Type back to Numeric and you'll be back to square one.

I've attached my example workspace (2018.1): dynamic_excel_zeroes2018_1.fmwt

Badge +1

Hi @kasparlov

I have been trying to figure out the inverse of this problem on dropping number values into template excels using column and row insertions. It is default at string is all cases in my current puzzle.

I have done three things to your workflow:

I exposed fme_type on the FeatureReader <Generic> Port

Exposed this same attribute on the Dynamic Writer

AND added a template seed file to your Dynamic writer. In the template i think i forced the field to be (inside excel) as custom field type 00000### in this template

the template file is embedded in the .fmwt below, hope 2018.1 version will open on your end.

 

I am unsure if it was the template file or exposing the fme_type did the trick. But i hope together they solve your problem and i hope it scales up to a larger directory of files for you.

*Another workaround is perhaps not use the FeatureReader and just a normal xls reader for a directory of somepath\\ *.xlsx and single merged feature type as the output port. I think the use of the user param in the reader disconnects some of the fme magic meta attributes. and causing excel to be peskier than it normally is. (which is very & always)

dynamic_excel_zeroes.fmwt

Badge +7

Hi @kasparlov

I have been trying to figure out the inverse of this problem on dropping number values into template excels using column and row insertions. It is default at string is all cases in my current puzzle.

I have done three things to your workflow:

I exposed fme_type on the FeatureReader <Generic> Port

Exposed this same attribute on the Dynamic Writer

AND added a template seed file to your Dynamic writer. In the template i think i forced the field to be (inside excel) as custom field type 00000### in this template

the template file is embedded in the .fmwt below, hope 2018.1 version will open on your end.

 

I am unsure if it was the template file or exposing the fme_type did the trick. But i hope together they solve your problem and i hope it scales up to a larger directory of files for you.

*Another workaround is perhaps not use the FeatureReader and just a normal xls reader for a directory of somepath\\ *.xlsx and single merged feature type as the output port. I think the use of the user param in the reader disconnects some of the fme magic meta attributes. and causing excel to be peskier than it normally is. (which is very & always)

dynamic_excel_zeroes.fmwt

Hi @scyphers,

 

thank you for the detailed answer. Exposing fme_type resulted in an error on my side "....No DEF line could be found for feature type " but using the template worked. Unfortunately this is not an option in my workflow, unless it is somehow possible to write attributes dynamically to a template.

Reply