Solved

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


Badge +10

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

icon

Best answer by markatsafe 25 July 2018, 23:26

View original

9 replies

Badge +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.

Badge +10

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?

 

Badge +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

Userlevel 2
Badge +17

@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
Userlevel 2
Badge +17

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

 

 

Badge +10

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

Userlevel 2
Badge +17

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.

 

 

Badge +10
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

 

 

Badge +2

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