Previous Section: Processing Spreadsheets
Introduction
In this exercise, we'll show how to process multiple CSV files in FME. We'll modify an existing FME workspace translating data from a single CSV file to Tableau. The workspace has been set up to process business license data from a single CSV file. Data validation and cleanup is performed before the data is written to Tableau format. But, there are also a number of other CSV files containing business license data from previous years. We will set up FME to handle all of these files. Then, we will demonstrate 2 options for writing out to Tableau:
Option 1: Write all data to a single Tableau file. We will add a new field to the Tableau file which holds the year the business license data was collected.
Option 2: Write data to multiple Tableau files. A Tableau file will be created for each CSV file that is read.
Downloads
Source data: pastyears.zip
Starting workspace: exercise2-startingworkspace.fmwt
Completed workspace: exercise2-multiplesheets.fmwt
Step-by-Step Instructions
1. Open up the previous exercise and remove DataCleanup.xlsx writers
We will be using the workspace from the previous exercise How to prepare Data for Tableau with FME (Processing Spreadsheets). If you haven't completed the previous exercise and would like to, click on the link to go to the exercise. If you are only interested in this exercise, download the starting template. In the previous exercise, we already created an excel file to store all our values that need to be cleaned up, delete both DataCleanup.xlsx writers.
2. Set up CSV reader to read all of the files from a folder
In the Navigator pane, expand the CSV reader. Double-click on the source CSV file parameter, then open the advanced browser. Click on "Select Multiple Folders/Files..." Navigate to the folder containing the four CSV files, and select it. Read the files with a .csv extension from that folder. Remove the .gz file and the .txt file, and the previous .csv file, we are only interested in .csv files from the PastYears folder.
Datasets within the PastYears folder:
- 2012business_licences.csv
- 2013business_licences.csv
- 2014business_licences.csv
- 2015business_licences.csv
Change the Source CSV file(s) in the the Navigator
Select Multiple Folders/Files... then click on the PastYears folder to add it
3. Modify source feature type
Now that we've set up the reader, the next step is to set up the existing source feature type on the canvas to handle all the files read by the CSV Reader. Open the Writer Feature Type properties. It was originally set up to read the single business license file. Checking the Merge Feature Type option allows this feature type to process all of the CSV files that are read. We'll use the default wildcard option the Merge Filter and Filter Type. It's important to note that when we turn the Merge Feature Type, FME automatically exposes an attribute called "fme_feature_type". Each feature read is tagged with this attribute, which holds the name of the file each feature was read from. We'll make use of this attribute shortly. The name of the reader will have changed from business_licenses to <All>
Open up the properties of the writer, enable Merge Feature Type and accept the defaults
4. Inspect data
Confirm that FME has been set up to process all of the CSV files within that folder. Right-click on the source feature type and inspect the data. Confirm that all 4 files in that folder were indeed read. By further inspecting a single feature, we see that the name of the file (or feature type) is stored with the feature.
5. Create geometry points with the VertexCreator transformer
Connect a VertexCreator transformer to the Reader. This will create points with our Latitude and Longitude attributes. For mode, ensure that "Add Point" is selected and then change your X Value to read the Longitude attribute and your Y Value to read the Latitude attribute, then click ok.
Add a VertexCreator and set the X and Y Value
Option 1: Write to Single Tableau File
If we ran this workspace now, all of the data would be written to a single Tableau file. In that case, we should create a new attribute to store the year the business license data was collected using a SubStringExtractor.
1. Add SubStringExtractor
Place a SubstringExtractor between the AttributeManager and the writer feature type. Set it up to extract the first 4 characters of the fme_feature_type attribute, which is in effect the year. Store the year value in a new attribute called YearCollected.
The destination Tableau schema should be updated with the new attribute.
We only want the year from the fme_feature_type, set the Start Index to 0 and End to 3
2. Run the workspace
A single Tableau file is created, with a new attribute YearCollected. View this file in Tableau to ensure the YearCollected field is populated
View the BusinessLicenses.tde in Tableau to ensure the YearCollected field is populated
Option 2: Write Data to Multiple Tableau Files
It is easy to create a separate Tableau file for every year of business license data.
1. Disable SubstringExtractor
If you added the SubstringExtractor in Option 1, disable it for Option 2, by right clicking on the transformer and clicking "Disable"
2. Modify destination feature type
Open the properties of the destination schema (Writer). Click on the drop-down next to Table Name, and select fme_feature_type. We are instructing FME to use the value of this attribute for the output table name, which means, that for every unique value found, a separate file will be created. We know that fme_feature_type holds the name of the file each feature was read from, and since we are reading 4 CSV files, we expect 4 tableau files to be created.
Open up the .TDE writer and change the Table Name to the attribute fme_feature_type to create 4 files
3. Run the workspace
Navigate to the Output folder to confirm the 4 tableau files were created. If running this translation multiple times to completion. In the Writer properties, under Table Settings > General, for Table Handling: Drop and Create.
4 Tableau Files in Windows Explorer
