Skip to main content
Solved

Dynamic JSON feature reader to Excel Feature writer, manage attribute ordering

oliver.morris
Contributor
Forum|alt.badge.img+12

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

Best answer by markatsafe

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'

View original
Did this help you find an answer to your question?

9 replies

M
Forum|alt.badge.img+2

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.

oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 23, 2018
markatsafe wrote:

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?

 

M
Forum|alt.badge.img+2

@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

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • July 24, 2018
markatsafe wrote:

@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
takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • July 24, 2018
markatsafe wrote:

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

 

 

oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 24, 2018

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', ['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

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • July 24, 2018
oliver.morris wrote:

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', ['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.

 

 

oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 24, 2018
takashi wrote:
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', ['fme_real64'] * len(_name))
 
# end of ProcessList def  

 

No changes to the feature writer between switching the code was made.

 

 

Thank you

 

 

M
Forum|alt.badge.img+2
  • markatsafe
  • Best Answer
  • July 25, 2018

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'

Reply


Helpful Members This Week

Community Stats

30,942
Posts
117,252
Replies
38,742
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.