Sorting Tabular or Graphical Data

Introduction

To sort tabular data in FME, the Sorter transformer is required. The Sorter reorders the output data; sorting can be done numerically or alphabetically.  

Step-by-step Instructions

In this scenario, we want to sort the data by multiple columns. First, we want the table sorted alphabetically by the maintainer field and then numerically by the fountainid field in descending order.

Source
The source data is a Microsoft Excel spreadsheet containing information about drinking fountains in the City of Vancouver. 

 
1. Create a New Workspace
Open FME Workbench and create a blank workspace. 

 
2. Add a Microsoft Excel Reader
Add a Microsoft Excel reader to the canvas by clicking on the Reader button on the top menu bar or by going to Readers > Add Reader. In the Add Reader dialog, select Microsoft Excel as the Format, then for Dataset browse to the DrinkingFountains.xlsx dataset which is available for download from the Files section on this article. Then click OK to finish adding the reader.

 
3. View Data
Data can be viewed and sorted in Visual Preview or FME Data Inspector, but any sorting is only temporary and within the view session. Before we make changes to the actual data, let's see how we want to sort the data. Click on the DrinkingFountains reader feature type to open the popup menu, then click on View Source Data to open the data in Visual Preview. 

 
In Visual Preview (or FME Data Inspector), data can be sorted by clicking on the attribute (column) names, which will sort Naturally.  To be more specific with the sort type, right-click on the attribute name and select the desired sort option. 

 
4. Sort Data
Since we want the data to be sorted in the output, we need to use a Sorter transformer. Click on the DrinkingFountains reader feature type to select it. Then add a Sorter to the canvas by typing “Sorter” to bring up the list of FME Transformers in the Quick Add Search. Select the Sorter from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it. 



 
Double-click on the Sorter to open the parameters. In the parameters, click on the box below Attribute to show the drop-down arrow, then select the maintainer attribute. Set Alpha/Num to Alphabetic then set the Order to Ascending. Next, add the fountainid and sort by Numeric Descending. 

 
5. Run Workspace
Connect an Inspector transformer to the Sorter output port. 

 
Run the workspace by clicking on the Run button on the top toolbar, or by using Run > Run Workspace on the top menu bar. 

 
After running the workspace, the data will be sorted alphabetically by maintainer (Engineering first), then by fountainid. This sorted data can be written out as desired. 

Data Attribution

Data used in this tutorial originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.