How to Prepare Data for Tableau with FME (Processing Spreadsheets)

Introduction

In this tutorial, we'll process a single CSV file, validate and clean the data, and load it into Tableau.

Video

This video was recorded in an earlier version of FME. The interface differs, but the information and steps are still correct.

Step-by-Step Instructions

Part 1: Simple Translation

We will start by creating a simple translation, reviewing the results in Tableau, and then returning to FME to perform data validation and cleanup.

1. Create a New workspace.

Start FME Workbench and select the New option under Create Workspace.

2. Set up CSV Reader.

As of FME 2025.2, the Coordinate System parameter is now configured within the Parameters dialog of each reader/writer format. For more information, including details about the change and affected transformers, please see Coordinate System Parameter Location Change.

• Click the Add Reader icon in the Toolbar and add a reader with the following parameters:

  Format: CSV (Comma Separated Value)

  Dataset: /business_licenses.csv

  Click the Parameters button to set the coordinate system:

  Feature Type Name(s): From File Name(s)

  Attribute Definition: Manual

  Name	Type
  Longitude	x_coordinate
  Latitude	y_coordinate

  Create Point Geometry from Attributes: Enabled

  Coordinate System: LL84

  

  Click OK to add the reader to the workspace. This tells FME to create a point for each record with a latitude and longitude value.

  When the CSV source data is added to the canvas, click on the arrow to see the full attribute list.

• Click the Add Reader icon in the Toolbar and add a reader with the following parameters:

  Format: CSV (Comma Separated Value)

  Dataset: /business_licenses.csv

  Coordinate System: LL84

  Click the Parameters button and set the feature type name:

  Feature Type Name(s): From File Name(s)

  Before adding the reader to the canvas, confirm that the Longitude and Latitude attributes are set to x_coordinate and y_coordinate, respectively. If they are not, change the Attribute Definition to Manual, then manually set. This will tell FME to display each row in the CSV as a point - for more information, see "Using the Reader" parameters in the Converting from CSV Data article.

  

  Click OK to add the reader to the workspace. This tells FME to create a point for each record with a latitude and longitude value.

  When the CSV source data is added to the canvas, click on the arrow to see the full attribute list.

3. Confirm points are created as a CSV file is read

Once the feature type has been added to the canvas, select the business_licenses feature type and click the run to this button. This will run the translation to the selected object and generate a feature cache, which can be inspected in the data preview (formerly visual preview) window or the Data Inspector by clicking on the green feature cache icon. We can see that we do, in fact, have points.

4. Add Tableau Writer

From the Writers menu, select Add Writer or use the Quick Add Menu, start typing Tableau, and select the Tableau Hyper format. For the Dataset, specify a directory in which to write the .hyper file. Click OK to add the Tableau writer to the workspace. Then connect the Reader to the Writer.

5. Modify Writer Feature Type Properties

Open the properties of the Writer. We can now specify the name of the table we would like to write to. Call it BusinessLicenses.

6. Run Workspace

You may have noticed a number of blue warnings go by in the log file. These are related to problems with the CSV data, which we will soon fix with FME.

7. (Optional) Examine the Output Hyper file in Tableau

In Tableau, we can see that all the columns match those we read from the CSV file using FME. Notice that the column data types have all been automatically set by FME based on a best guess of the data type in each column. We can see that the LicenseRSN has correctly been set to a Number type, and BusinessName is a String.

Part 2: Data Validation and Cleanup

Let's take a closer look at the business license data.

Here is a list of tasks we will accomplish with FME. We have already completed the first one in our current workspace. We will modify the workspace to complete the remaining tasks.

• Read the Excel file and create points
• Filter out records that don't have latitude/longitude values
• Ensure PostalCode has a value; extract first 3 characters
• Set up a conditional value to handle empty FeePaid values
• Create a new BusinessDisplayName field; populate from existing fields

1. Filter out Records That Don't Have Latitude/Longitude Values

You may recall that some records in the CSV file lacked latitude and longitude values. Since FME could not create points for them, we want to filter them out; a GeometryFilter will help us do so.

Click the CSV Reader Feature Type, then start typing "GeometryFilter" and press Enter. Click Enter again to enter the Transformer Parameters. Select "Point" for the "Geometry Types to Filter". Click Ok.

2. Run Workspace with Feature Caching Enabled

Run the workspace to generate feature caches on the GeometryFilter. If it's not already enabled, you can turn feature caching on in the Run submenu > Enable Feature Caching

We can confirm that 731 of our 10,000 records lacked latitude or longitude values.

3. Write Records with no Latitude/Longitude to the "Data Cleanup" Excel file

Before we continue processing the points, let's write these records to a "Data Cleanup" file so they can be fixed. We will write them out to Excel.

From the Writers menu, add an Excel writer. Write it to the output folder and name the file DataCleanup.xlsx. Connect the new writer feature type up to the unfiltered port of the GeometryFilter, then open up the Writer's properties and change the sheet name to "Missing Latitude Longitude".

Now that we have dealt with the missing values, let's continue processing the points.

4. Ensure PostalCode has Values: AttributeValidator

Place an AttributeValidator on the canvas and connect it to the GeometryFilter. Open its properties and select PostalCode for the Attribute to Validate. The validation rule is that PostalCode MUST have a value. Also, verify that some string fields contain string values and that numeric fields contain numeric values.

5. Write Records that Fail Validation to a New Sheet in the "Data Cleanup" Excel File

Again, before continuing to process the valid data, write the data that fails validation out to a different sheet in the "Data Cleanup" Excel file already set up. Right-click on the canvas and select "Insert Writer Feature Type". Call the new sheet "Failed Validation". Connect it to the Failed port of the AttributeValidator.

6. Extract First 3 Characters of the PostalCode: SubStringExtractor

Now that we know that the "records output" from the AttributeValidator all have a value for Postal Code, let's extract the first 3 characters. We do this because Tableau uses the first 3 characters of the postal code to automatically map the areas.

Place a SubstringExtractor on the canvas, connect it to the Passed port of the AttributeValidator, and configure it to extract the first 3 characters from the PostalCodeAttribute. We will call the resulting attribute PostCodeTrimmed.

FME indexes the first value as 0, then counts from there. As you can see in the table below, if we only want the first 3 characters of the postal code, we would use 0-2. So in the SubstringExtractor, our Start Index = 0 and our End Index = 2.

7. Set up Conditional Attribute Values for FeePaid: AttributeManager

The AttributeManager is a transformer that enables many attribute manipulations, including setting conditional values. Add the AttributeManager after the SubstringExtractor

If the FeePaid attribute is empty, set the value to 0, otherwise, leave it as is.

Create a new attribute called BusinessDisplayName. Set its value to BusinessTradeName, but only if BusinessTradeName has a value; otherwise, set it to BusinessName.

There is also an attribute we don't need in our final output; let's remove _fme_validation_message.

Connect the AttributeManager output to the Writer feature type.

Your workspace should now look like the image below.

8. Modify Writer Feature Type Properties

The attributes we removed just now are still in the output schema and are now red. The original attribute schema was a copy of the source schema. It has changed, as a result of our data transformation, but we may update it to reflect what we have done.

Open the properties of the destination feature type, go to the User Attributes tab, and click on Automatic for Attribute Definition. The attribute schema reflects the changes we have made.

Since we want to overwrite the Tableau file we initially wrote, open the properties again and change the Table Handling to Drop and Create.

9. Run Workspace and Confirm .hyper File Exists

Let's run our final workspace! Click on the Run button. Confirm the .hyper file and DataCleanup.xlsx files were created using Windows Explorer.

10. (Optional) Examine the .hyper File in Tableau

In the "Data Source" view, notice all of the records that we have imported from the CSV file. We can also see that the PostCodeTrimmed attribute, which we created in our workspace, is present alongside the other fields imported from the CSV file.

Now that our data is imported into Tableau, we can begin creating Data Views to explore it.

For example, we can create a simple map view to see the data points overlaid on a map. Create a new sheet, then double-click on geometry to see the individual points. Finally, color them by status to get an overall view of which businesses currently have active licenses.

We could also use the postal code field to show our data and create a new sheet. Add PostalCodeTrimmed, from "Dimensions", and color them by the unique count of business licenses in the area.