How to sort tabular data in FME?

Tabular data in FME can be sorted both in FME Data Inspector and in FME Workbench. In FME Workbench it can be done by using the Sorter function, which is similar to the SQL ORDER BY command. Sorting data in the FME Inspector manually orders columns in the table view. It does not impact the original dataset. However, in FME Workbench, we can sort the data, and save them in this order.

Data

We will work on the Esri Geodatabase (File Geodb Open API), in which there are polygons representing the quarters in Wrocław. The SHAPE file with the polygons can be downloaded from here.

Part 1 – Sorting Data Using the FME Data Inspector

In the FME Data Inspector you can sort data in ascending or descending, Alphabetical or Numeric order.

Sort Columns Ascending

In the Table View section of the Data Inspector, right-click on the “NAZWAOSIED” column header to sort by alphabetical ascending order.

In the Table View section of the Data Inspector, right-click on the “OBJECTID” column header to sort by numeric ascending order.

Sort Columns Descending

In the Table View section of the Data Inspector, right-click on the “NAZWAOSIED” column header to sort by alphabetical descending order

In the Table View section of the Data Inspector, right-click on the “OBJECTID” column header to sort by numeric descending order.

Clear All Sorting

Right-click on any of the column headers, then select “Clear All Sorting”. This will remove all sorting that has been applied to the table and return the dataset to its original order.

Part 2 – Sorting Data using the Sorter

In this part we will sort the quarters in Wrocław alphabetically ascending (A to Z).

Start FME Workbench

If it isn’t open already, launch FME Workbench.

Generate Workspace

Select the option to Generate Workspace. Alternatively, you can use the shortcut Ctrl+G.

Set the Reader Format

In the Generate Workspace Dialog, set the reader format to Esri Geodatabase (File Geodb Open API).

Set the Writer Format

Set the Writer Format also to Esri Geodatabase (File Geodb Open API), and indicate the same Geodatabase, because you will update it.

Set the Feature Operation and Table Handling

After the workspace is generated, double-click the Writer Feature Type to open the Writer Feature Type Parameters dialog. In the General section, set the Table Name to GraniceOsiedli_posortowane. In the Table section, ensure that if such table already exists it will be dropped, and created again, so set the Feature Operation to Insert and the Table Handling to Drop and Create.

If you want to learn more about table handling options, we invite you to read our other article on databases.

Update Attributes

Switch to the User Attributes Tab in the Writer Parameters dialog, set the Attribute Definition to Automatic. Thanks to this, FME Workbench automatically defines the list of attributes based on input data and changes that were made in workspace (such as renaming, deleting).

Add a Sorter

Add a Sorter to the canvas and double-click it to open the transformer parameters dialog. In the Sort By section, click on the empty cell under the Attribute Column and select the “NAZWAOSIED” attribute from the drop-down list, Alpha/Num set to Alphabetic, and Order to Ascending.

Run The Workspace

After running the workspace, your table will be sorted by the defined ordering methods. You can view the new table by selecting the writer and clicking the Inspect button.