Hi,
I am picking up some JSON from a feature reader and outputting to Excel. It works fine but I would like to order the Excel output so that some header fields are always returned first, e,g, ID, Name, Country
The trouble is that this JSON is dynamic and sometimes doesnt include the attribute 'Country' if it hasn't been populated. This means when the JSON gets brought in and the first record doesnt contain country then it would look structure like so:
ID, NAME, FieldA, Country
Is there any way I can partially define the schema whilst leaving the rest dynamic?
Thank you
Oliver
If you are using the Dynamic mode on the Excel writer, you should define the static attributes on the Excel writer feature type, and then the dynamic ones will be appended to those.
If you are using the Dynamic mode on the Excel writer, you should define the static attributes on the Excel writer feature type, and then the dynamic ones will be appended to those.
@MarkAtSafe
I switched to dynamic mode, however the excel writer wrote the dynamic columns first and the static columns after the dynamic ones - any ideas why?
@olivermorris You are correct. The 'fixed' part of the schema is appended to the dynamic schema. I think this is probably not what most people would want. I think you'd want the fixed part of the schema first, followed by the dynamic part of the data (which is what you want).
To work around this I've cobbled together some python that will append the dynamic schema to a 'fixed schema - someone with better python skills could probably improve on this. There is a ListAppender on the FME Hub, but this requires you to enter the list elements you want to append, which I don't think would work in a dynamic example.
I've attached an example (FME 2018.0): dynamicexcelwriter.fmwt
@olivermorris You are correct. The 'fixed' part of the schema is appended to the dynamic schema. I think this is probably not what most people would want. I think you'd want the fixed part of the schema first, followed by the dynamic part of the data (which is what you want).
To work around this I've cobbled together some python that will append the dynamic schema to a 'fixed schema - someone with better python skills could probably improve on this. There is a ListAppender on the FME Hub, but this requires you to enter the list elements you want to append, which I don't think would work in a dynamic example.
I've attached an example (FME 2018.0): dynamicexcelwriter.fmwt
A minor change. If there could be a case where the schema definition derived from the source dataset would contain the fixed attribute names, avoid duplicating them.
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
_type = feature.getAttribute('_attribute{}.fme_data_type')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
attrtype = feature.getAttribute('attribute{}.fme_data_type')
for n, t in zip(attrname, attrtype):
if n not in _name:
_name.append(n)
_type.append(t)
feature.setAttribute('attribute{}.name',_name)
feature.setAttribute('attribute{}.fme_data_type',_type)
Vote up this Idea ;-)
Introduce New Transformer to Modify Schema Easily
@olivermorris You are correct. The 'fixed' part of the schema is appended to the dynamic schema. I think this is probably not what most people would want. I think you'd want the fixed part of the schema first, followed by the dynamic part of the data (which is what you want).
To work around this I've cobbled together some python that will append the dynamic schema to a 'fixed schema - someone with better python skills could probably improve on this. There is a ListAppender on the FME Hub, but this requires you to enter the list elements you want to append, which I don't think would work in a dynamic example.
I've attached an example (FME 2018.0): dynamicexcelwriter.fmwt
A possible way is to force FME generic data type for all attributes in the schema definition to a numeric type, such as "fme_real64".
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
for n in attrname:
if n not in _name:
_name.append(n)
feature.setAttribute('attribute{}.name',_name)
feature.setAttribute('attribute{}.fme_data_type', >'fme_real64'] * len(_name))
Note that this solution can be applied to only the case where the destination data format accepts a character string even if the column data type definition is numeric.
Hi Takashi,
I tried that unfortunately I got the error:
XLSX Writer: No DEF line could be found for feature type 'Output'. If you are using dynamic schemas, ensure that the fme_feature_type attribute exists on the incoming feature and corresponds to a valid feature type definition
My code is:
import fmeobjects
import fme
# Process a list and append another list
def processFeature(feature):
pass
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
## _type = feature.getAttribute('_attribute{}.fme_data_type')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
## attrtype = feature.getAttribute('attribute{}.fme_data_type')
##for n, t in zip(attrname, attrtype):
## if n not in _name:
## _name.append(n)
## _type.append(t)
for n in attrname:
if n not in _name:
_name.append(n)
feature.setAttribute('attribute{}.name',_name)
##feature.setAttribute('attribute{}.fme_data_type',_type)
feature.setAttribute('attribute{}.fme_data_type', r'fme_real64'] * len(_name))
# end of ProcessList def
Can you let me know where I am going wrong, the table contains a mix of text and numeric fields.
Thank you
Hi Takashi,
I tried that unfortunately I got the error:
XLSX Writer: No DEF line could be found for feature type 'Output'. If you are using dynamic schemas, ensure that the fme_feature_type attribute exists on the incoming feature and corresponds to a valid feature type definition
My code is:
import fmeobjects
import fme
# Process a list and append another list
def processFeature(feature):
pass
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
## _type = feature.getAttribute('_attribute{}.fme_data_type')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
## attrtype = feature.getAttribute('attribute{}.fme_data_type')
##for n, t in zip(attrname, attrtype):
## if n not in _name:
## _name.append(n)
## _type.append(t)
for n in attrname:
if n not in _name:
_name.append(n)
feature.setAttribute('attribute{}.name',_name)
##feature.setAttribute('attribute{}.fme_data_type',_type)
feature.setAttribute('attribute{}.fme_data_type', r'fme_real64'] * len(_name))
# end of ProcessList def
Can you let me know where I am going wrong, the table contains a mix of text and numeric fields.
Thank you
Please check if the destination feature type name (Sheet Name), the Schema Sources and the Schema Definition Name in the FeatureWriter are configured correctly.
Please check if the destination feature type name (Sheet Name), the Schema Sources and the Schema Definition Name in the FeatureWriter are configured correctly.
Thanks for the continues support.
This produces output (but with the wrong data types)
import fmeobjects
import fme
# Process a list and append another list
def processFeature(feature):
pass
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
_type = feature.getAttribute('_attribute{}.fme_data_type')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
attrtype = feature.getAttribute('attribute{}.fme_data_type')
for n, t in zip(attrname, attrtype):
if n not in _name:
_name.append(n)
_type.append(t)
feature.setAttribute('attribute{}.name',_name)
feature.setAttribute('attribute{}.fme_data_type',_type)
# end of ProcessList def
This amended version fails:
import fmeobjects
import fme
# Process a list and append another list
def processFeature(feature):
pass
def ProcessList(feature):
print("starts here")
# fixed schema lists
_name = feature.getAttribute('_attribute{}.name')
# dynamic schema lists
attrname = feature.getAttribute('attribute{}.name')
for n in attrname:
if n not in _name:
_name.append(n)
feature.setAttribute('attribute{}.name',_name)
feature.setAttribute('attribute{}.fme_data_type', i'fme_real64'] * len(_name))
# end of ProcessList def
No changes to the feature writer between switching the code was made.
Thank you
I've revised my workspace to remove the attribute{}.native_data_type which might be confusing things. But I'm pretty sure that the fme_data_type does line up with the correct attributes.
Revised workspace: dynamicexcelwriter.fmwt
By default, the dynamic writer will use 'fme_data_type'
