Files
Introduction
Translating data to CSV (Comma Separated Value) format allows you to distribute datasets widely. CSV is a versatile format because:
It emulates database-style tabular structures.
It is plain text, readable by many applications.
It requires minimal setup in FME.
The CSV writer includes simple options, such as overwriting existing files and toggling header lines. Omitting headers can be useful if appending to a database that already expects known field names.
In this example, we will join:
A Microsoft Excel file (Building_Permits.xls) containing building permit applications for the City of Calgary.
An Esri Shapefile (ParcelAddress_SW.shp) containing address points with spatial coordinates in the south west quadrant of Calgary
By joining these datasets on address, we will add latitude and longitude to the building permits, enabling us to visualize construction locations on a map.
Step-by-step Instructions
1. Add an Excel Reader
Open a blank workspace in FME Workbench and add an Excel reader.
Format: Microsoft Excel
Dataset: Building_Permits.xls
This dataset contains building permit applications. Since it does not include longitude or latitude attributes, no parameters need adjusting. Click OK to add the reader.
2. Find Building Permits Issued in 2020
The dataset contains permits from 2012 onwards. We only want those issued in 2020.
Add a Tester transformer and connect it to the Excel reader. In the parameters, set:
Left Value: AppliedDate
Operator: Begins With
Right Value: 2020
Since dates are formatted YYYY/MM/DD, filtering with Begins With selects all 2020 permits.
3. Add Shapefile Data
Next, add the address data:
Format: Esri Shapefile
Dataset: ParcelAddress_SW.shp
Click OK to confirm.
4. Change String Case
The OriginalAddress attribute in the Excel file is uppercase, while the shapefile’s ADDRESS field is lowercase. To join them, the case must match.
Add a StringCaseChanger transformer, connect it to the shapefile reader feature type, and set:
Change: All Attributes
Case Change: UPPERCASE
5. Join Datasets
Now join the permits to their spatial addresses.
Add a FeatureJoiner transformer. Connect the Tester Passed ouput port to the Left input port and the StringCaseChanger to the Right.
In the parameters:
Join Mode: Inner
-
Join On:
Left: OriginalAddress
Right: ADDRESS
6. Clean Up Attributes
Many attributes are unnecessary. Add an AttributeKeeper transformer to the Joined port of the FeatureJoiner.
Keep only these attributes:
- ADDRESS
- AppliedDate
- CompletedDate
- IssuedDate
- Latitude
- Longitude
- PermitType
- WorkClass
7. Write Out to CSV
Add a CSV writer to save the output.
Format: CSV (Comma Separated Value)
Dataset: Choose a folder location
CSV File Definition: Automatic
In the Feature Type dialog, set:
CSV File Name: PermitLocations_SW
8. Run the Workspace
Click the green Run button.
After translation, inspect the output file. It should contain building permit records joined with latitude and longitude values, ready for mapping.
Data Attribution
The data used here originates from data made available by the City of Calgary. It contains information licensed under the Open Government Licence – City of Calgary.