Getting Started with FME Form: Working with Table/Tabular Data

Introduction 

In this article, you'll discover how to create a workspace in FME Workbench, the authoring application that accompanies FME Form, specifically emphasizing utilizing tabular data. Please follow along with the step-by-step instructions to learn how to read data from a Microsoft Excel workbook that includes employee information, add a transformer to filter for active employees, and then write the filtered data to a new worksheet in the same workbook.


If you regularly use databases instead of spreadsheets, the same example can be completed by reading from a PostgreSQL database. See Getting Started with FME Form: Working with Databases for the instructions.
 

FME Desktop was rebranded to FME Form in 2023. FME 2024.0 features a new interface.  This tutorial can be used with any version of FME. The concepts are the same, but the screenshot appearances may differ. 

Source Data

The source dataset is a Microsoft Excel workbook containing information about employees.

 

Step-by-step Instructions

1. Start a Blank Workspace
Start FME Workbench, then click the Blank Workspace button.


The FME Workbench canvas is where the workspace will be built using readers, writers, and transformers. 


2. Add an Excel Reader
On the top menu bar, click Readers, then select Add Reader. A reader is an object in a workspace that reads a source dataset. 


In the popup Add Reader dialog, type in Microsoft Excel as the Format. Then click the ellipsis [...] button next to Dataset and browse to the EmployeeRecord.xlsx sample dataset. Other parameters are available, but you can leave them as default for this example. Click OK to add the reader to the workspace. 


A reader feature type object has been added to the canvas and represents the Employees worksheet. Readers create a feature type for each table or layer in the source dataset you wish to read. In this case, the Excel workbook only had one worksheet, so there is a single feature type.


The Navigator is where readers can be found. The A) reader represents the dataset as a whole, such as the entire EmployeeRecord.xlsx workbook. The B) reader feature types represent each part of the dataset, in this case, the worksheets. 


3. Add a Transformer
Transformers are the building blocks within a workspace to manipulate data, and each has a specific function. We will use a Tester transformer to filter the data. 
Click on a blank space on the canvas, and start typing Tester to bring up the Quick Add dialog. The Quick Add dialog is where readers, writers, and transformers can be added. Double-click on the Tester under FME Transformers to add it to the workspace. 


To access the data from within the transformer, it needs to be connected to the workflow. 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.




4. 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 = Active will be created. 

Double-click anywhere on the Tester transformer to open the parameters. Click in the red box below 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 in the Right Value box, the Operator changed to = (equals); this is the default operator. The Operator can be changed by clicking on the =, which will open a drop-down menu. The conditional test has been set up. Click OK to confirm the Tester parameters. 


5. Run Workspace
We have authored the workspace to read the data and filter for the Active employees, but we still need to run the workspace. On the top toolbar (ribbon), click the green Run button. Clicking Run will trigger the workspace to read and process the data through any transformers. 


After clicking Run, a Translation Parameter Value dialog will pop up; this is where any user-defined parameters can be modified. We do not need to make any changes at this time; click Run to continue.  


6. View Output Data in Visual Preview
After initiating the workspace to run, data will flow through the workspace. The numbers on the connection lines represent the number of features it is transporting, and the number on each output port is the number of features that is output. The Translation Log will also open; this can be ignored 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. 


Note: In FME 2023.2 or prior, the inspect icon is a green magnifying glass. 

Visual Preview is the data viewer within FME Workbench. There is also an external application called FME Data Inspector that allows users to view data. In the Visual Preview window, you will see the Tester_Passed table containing all of the employees with an Active status. Inspecting the Failed output port would display all of the inactive employees. 



7. Add a Writer
The data viewed in Visual Preview is currently only contained within FME Workbench. We need to write the data out to store it for use later or for use within a different application. This is accomplished with a writer. 

On the top menu bar, click on Writers to open the drop-down menu, then select Add Writer.


In the Add Writer popup dialog, select Microsoft Excel as the Format. Then for Dataset, click on the ellipsis and browse to the EmployeeRecord.xlsx dataset. This is the same dataset that we read. FME can read and write the same file and create new files in any supported format. Ensure that Sheet Definition is set to Automatic. Click OK to finish adding the writer to the canvas. 


Note: For FME 2023.2 and prior, the Sheet Definition should be set to Copy from Reader for this example, and the writer feature type will be Employees until it is updated to Active. To update the Sheet Name, double-click on the writer feature type to open the parameters, then change the Sheet Name to Active. In FME 2024.0 and newer, the default is Automatic. 

8. Modify the Writer  
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, type Active for the Sheet Name, then click OK. 




Click and drag from the arrow on the Tester Passed output port to the red arrow on the Active writer feature type to connect it.



9. 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 on the Run button again to run the entire workspace. The Translation Parameter Values dialog will reappear since we added a writer; click on Run. 

Single-click on the Active writer feature type to open the popup menu. Click on the folder icon to browse to the folder containing the dataset. 


In your File Browser, double-click on EmployeeRecord.xlsx to open the dataset in Microsoft Excel and view the newly added Active worksheet. If you do not have Microsoft Excel installed, the dataset will open in FME Data Inspector. 


Note that the default parameters in the Microsoft Excel writer are set to insert data every time the workspace is run. If the workspace were run a second time, this would result in a duplicate copy of the active employees being added to the newly created Active worksheet. To learn more about the Microsoft Excel writer, see Excel Writer Parameters | Converting Excel to Excel. 

 

Continue Your FME Journey  

• Getting Started with FME Form: Working with Databases
• 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 Excel
• Tutorial: Getting Started with CSV
• Tutorial: Getting Started with Apache Parquet

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?

• Tutorial: Getting Started with FME Flow
• FME Flow Authoring - FME Academy

Have a specific question?

• Reach out to the FME Community 

Data Attribution

The data used in this example was randomly generated with datagenerator.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.