In this tutorial we will demonstrate how to transpose a table in FME.
Transposing a table, also known as a table rotation,
involves switching the columns of a table into rows
in most cases without any data manipulation or summarization.
Place as Excel reader on the canvas, and set the source dataset.
Add an AttributeExploder transformer to the workspace.
The AttributeExploder uses the attribute values of a row,
and creates a separate feature for each attribute
as a name/value pair.
Open the parameters to ensure that the ‘Exploding Type’ is set to ‘Features’.
Next, we will use a Tester transformer to filter out any unwanted attributes.
This step is necessary because the exploder will expose attributes that are specific to the Excel format,
and not relevant for the transpose.
To remove these attributes, we will test for where
attribute name (_attr_name) ‘begins with’: Fme_
attribute name (_attr_name) ‘begins with’: Multi_reader_
and, attribute name (_attr_name) ‘begins with’: xlsx_
An Aggregator will be used to recombine the name/value pairs into records.
Connect an Aggregator transformer to the tester fail port.
Set the ‘Group By’ to _attr_name,
and ‘mode’ to ‘attributes only’.
Set the accumulation mode to ‘merge incoming attributes’.
We will also need to generate a list from the aggregator,
so select the checkbox, and add in a new list name.
The output from the aggregator is a new feature for each column in the original table,
with a list attribute that contains the various cell names.
We can use an AttributeCreator to read attribute values back into the column positions
which will be output to Excel.
The simplest approach to accomplish this
is to use an AttributeCreator transformer
and create new column letters for A through E;
assigning an attribute name and attribute value to each letter.
We will use a Counter and a Sorter transformer to provide the correct sequencing for the table’s rows.
Add the Counter transformer.
Open its parameters,
note that the ‘_count’ attribute will be used to store the count values,
and values will start at ‘0’.
Add a Sorter transformer,
and set its sorting attributes to attribute: ‘_count’, alphabetic, descending order.
Finally, add the Excel writer.
Make sure to set its sheet definition to manual,
as we will be making some changes to the writer’s parameters.
Since we would like the first data row to also be our column name,
we will modify the format parameters.
Set ‘output field name’ = ‘no’,
and ‘use attribute names as column positions’ = ‘ yes’.
We will also manually input the names of the columns
by going to the user attributes tab,
and typing in the letters A through E.
Run the workspace.
The end result is a transpose of the original table.