Files
-
- 20 KB
- Download
Introduction
To sort tabular data in FME, the Sorter transformer is required. The Sorter reorders the output data; sorting can be numerical or alphabetical.
Data Source
The source data is a Microsoft Excel spreadsheet containing information about drinking fountains in the City of Vancouver.
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.
To follow along with this tutorial, please download the data from the article's Files section.
1. Create a New Workspace
Open FME Workbench and create a blank workspace.
2. Add a Microsoft Excel Reader
Add a 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, set the following:
- Format: Microsoft Excel
- Dataset: DrinkingFountains.xlsx
Then click OK to finish adding the reader.
3. View Data
Data can be viewed and sorted in Data Preview or the FME Data Inspector, but any sorting is temporary and applies only within the current view session. Before we make changes to the actual data, let's decide how we want to sort it. Click on the DrinkingFountains reader feature type to open the mini toolbar, then click on View Source Data to open the data in Data Preview.
In Data Preview, 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 the Run button on the top toolbar, or by selecting Run > Run Workspace from 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
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.