Files
Introduction
In this article, you'll discover how to create a workspace in FME Workbench, the authoring application accompanying FME Form, specifically emphasizing the utilization of data from a database. Please follow the step-by-step instructions to create a database connection, read data from a PostgreSQL table containing employee information, add a transformer to filter for active employees, and write the filtered data to a Microsoft Excel workbook.
If you regularly use spreadsheets instead of databases, you can complete the same example by reading from a Microsoft Excel spreadsheet. See Getting Started with FME Form: Working with Table/Tabular Data for the instructions.
A rebranded interface was introduced in FME 2024.0. This tutorial can be used with any version of FME. The concepts are the same, but the screenshot appearances may differ.
In FME 2025.1, the toolbar menu has been organized. Readers and writers can now be found under the Build menu. Additionally, an Add button has been added to the canvas, where sources, destinations, and transformers can be selected.
Source Data
The source data is a table and contains a list of employees from the Safe Software-provided training PostgreSQL database.
Step-by-step Instructions
1. Start a Blank Workspace
Start FME Workbench, then click the New button.
The FME Workbench canvas is where the workspace will be built using readers, writers, and transformers.
2. Add a PostgreSQL Reader and Create a Database Connection
First, we need to add a source dataset. A reader is an object in a workspace that reads a source dataset. There are a few ways to accomplish this.
| Click on the Reader button on the toolbar | |
| On the top menu bar, click Build > Readers > Add Reader | |
| In FME 2025.1+, you can click on the canvas to add a data source. |
In the Add Reader dialog, set the following fields:
- Format: PostgreSQL
- Connection: click the dropdown arrow to select a connection or Add Database Connection
Selecting Add Database Connection will open another dialog
In the Add PostgreSQL Connection dialog, enter the following connection parameters:
- Name: Training PostgreSQL
-
Host:
postgis.train.safe.com - Port: 5432
- Database: fmedata
- Username: fmedata
- Password: fmedata
FME 2025.0 introduces new options for sharing database and web connections. See the documentation for details.
Click Test. Once you receive the "Successfully connected to database" message, click Close, then click Save to save the connection parameters.
3. PostgreSQL Reader Parameters
With the database connection created, we can now access the database tables. Still in the Add Reader dialog, click on Parameters.
Now we need to select the table containing employee information. This is nested under Public and Employees. Click the ellipsis beside Tables in the Constraints box. Click OK three times to finish adding the reader to the workspace.
If you cannot find the Employee table in the Safe Software-provided PostgreSQL training database, double-check that the Format in the reader is set to PostgreSQL and not PostGIS. The Employee table does not contain any geometry and cannot be read in with the PostGIS reader.
A reader feature type object has been added to the canvas, representing the Employees table. Readers create a feature type for each table or layer in the source dataset you wish to read.
The Navigator is where readers can be found.
a) This represents the dataset or database, such as the entire PostgreSQL Public database
b) The feature types represent each part of the dataset, in this case, the Employees table
4. Add a Transformer
Transformers are the building blocks within a workspace to manipulate data, and each has a specific function. You can add a transformer in multiple ways, similar to readers:
| Click anywhere on the blank canvas and start typing the transformer name to access the quick add menu | |
| Click on the Transformer button on the toolbar | |
| On the top menu bar, click Build > Transformers > Add Transformer | |
| In FME 2025.1+, you can click on the green + on the canvas and select Transformer |
We will use a Tester transformer to filter the data. Type Tester in the add transformer window. Double-click on the Tester transformer to add it to your workspace.
The data and the transformer need to be connected to access the data from within the transformer. Click and drag from the arrow on the Employees reader feature type to the red arrow of the Tester transformer. This connection line sends data from the feature type into the transformer for processing.
5. Modify Transformer Parameters
The Tester transformer filters data based on conditional statements. To filter for the employees who are still active at the company, a conditional statement of Status = Inactive will be created.
Double-click anywhere on the Tester transformer to open the parameters. Click on the red box below the Left Value in the Tester parameters to expose the selection options.
After clicking in the box, an ellipsis button and a down (drop-down) arrow will appear. Click on the down arrow to open the drop-down menu, hover your mouse over Attribute Value to expand the menu, then click on Status. Status is the attribute where the Active and Inactive values are stored.
Next, click in the box under Right Value; the selection options will appear, but can be ignored, as we will manually enter a value. In the Right Value box, type Active. Notice that after clicking the Right Value box, the Operator changed to = (equals); this is the default operator. The Operator can be changed by clicking the =, which will open a drop-down menu. The conditional test has been set up. Click OK to confirm the Tester parameters.
-
Clause 1:
- Left Value: Status
- Operator: =
- Right Value: Inactive
6. Run Workspace
We have authored the workspace to read the data and filter for Inactive employees, but we still need to run the workspace. On the top toolbar (ribbon), click the green Run button. Ensure that Enable Data Caching is checked. Clicking Run will trigger the workspace to read and process the data through transformers.
After clicking Run, a Translation Parameter Value dialog will open; this is where any user-defined parameters can be modified. We do not need to make any changes now; click Run to continue.
7. View Output Data in Visual Preview
After the workspace is initiated, data will flow through it. The numbers on the connection lines indicate the number of features it transports, and the numbers on each output port indicate the number of features it outputs.
The Translation Log will also open; you can ignore it for now. A feature in FME is a single row in a table. Click on the green eye icon on the Tester Passed output port to open the data in the Visual Preview window. To learn more about viewing data in FME, see Data Inspection in FME.
In FME 2023.2 or prior, the inspect icon is a green magnifying glass.
Data Preview (formerly Visual Preview) is the data viewer within FME Workbench. There is also an external application, FME Data Inspector, that allows users to view data. In the Visual Preview window, you will see the Tester_Passed table, which contains all employees with an Inactive status. Inspecting the Failed output port would display all of the Active employees.
8. Add a Writer
Now, the data needs to go to a destination. The data displayed in Data Preview (formerly Visual Preview) is currently stored only in FME Workbench, so we need to write it to a file for later use or for use in a different application. This is accomplished with a writer.
As with readers, there are multiple ways to add a writer to the canvas.
| Click on the Writer button on the toolbar | |
| On the top menu bar, click Build, then select Add Writer. | |
| In FME 2025.1+, you can click on the green + on the canvas and select Destination |
In the Add Writer dialog, set the following fields:
- Format: Microsoft Excel
-
Dataset: <local path>\EmployeeRecord.xlsx
- Click on the ellipsis and browse to the EmployeeRecord.xlsx dataset
- Sheet Definition: Automatic…
Click OK to finish adding the writer to the canvas.
The default Sheet Definition parameter for FME 2023.2 and prior is Copy from Reader. Before clicking OK, change the Sheet Definition to Automatic. Then continue to the next step.
9. Modify Writer Feature Type Parameters
The Sheet Definition was set to Automatic, which means the schema will be copied from the workflow when we connect the writer feature type. We can modify the name before we connect it. In the Feature Type dialog set:
- Sheet Name: Inactive
Click OK.
Click and drag from the arrow on the Tester Passed output port to the red arrow on the Inactive writer feature type to connect it.
10. Run the Workspace and View Output
The final step is to run the entire workspace. Running the workspace with a writer connected will write the data out to disk. Click the Run button to run the entire workspace; click Run in the Translation Parameters dialog.
To quickly navigate to the folder containing the EmployeeRecords.xlsx dataset, single-click on the Inactive writer feature type to open the mini toolbar, then click on the folder icon.
In the file explorer, open EmployeeRecords.xlsx in Microsoft Excel. If you do not have Microsoft Excel installed, the dataset will open in FME Data Inspector.
The default parameters in the Microsoft Excel writer are set to insert data each time the workspace runs. If the workspace were run again, a duplicate copy of the inactive employees would be added. To learn more about the Microsoft Excel writer, see Excel Writer Parameters | Converting Excel to Excel.
Additional Resources
Continue Your FME Journey
- Getting Started with FME Form: Working with Table/Tabular Data
- Getting Started with FME Form: Working with Spatial Data
- Getting Started with FME Form: Working with Web Data
Want to learn about a specific format?
- Tutorial: Getting Started with PostGIS and PostgreSQL
- Tutorial: Getting Started with Microsoft SQL Server
- Tutorial: Getting Started with Oracle
Want to continue learning FME Form basics?
- The FME Academy is a guided, on-demand way to learn the entire FME Platform, and it's FREE!
Interested in self-serve and automation?
Have a specific question?
- Reach out to the FME Community
Data Attribution
The data used in this example was randomly generated with generatedata.com. This dataset is a work of fiction. Names, dates, places, and incidents are randomly generated. Any resemblance to actual locales or persons, living or dead, is entirely coincidental.