Converting Tabular (Non-Spatial) and Spatial Data to CSV

Introduction

Translating your data to Comma Separated Value (CSV) allows you to distribute your dataset to a multitude of users and customers. The reason you may want to use this data format is the versatility due to the way tabular data emulates a database, as well as being written in plain text format that is readable by many applications.
The CSV writer is easy to set up, as there are few parameters to change. You may choose to overwrite an existing CSV, and you may include a header line with the field names. Choosing not to include the header line may be useful for appending the dataset to an existing database if you know ahead of time that the field names will align properly.
This article will take a Microsoft Excel file containing building permit application information for the City of Calgary, and join it with an Esri shapefile dataset with latitude and longitude information. Joining these two datasets based on the address will add spatial coordinates to the building permit dataset, which will allow us to view where the construction will be on a map. 
 

Step-by-step Instructions

1. Add an Excel Reader
Open up a blank workspace in FME Workbench and add an Excel Reader. Browse to the Building_Permits.xls file, which is a tabular file containing data about building permits acquired in the City of Calgary.  


This file doesn’t contain any longitude or latitude attributes, so we can ignore the parameters. Click OK to add the reader. 
 
2. Find Building Permits Issued in 2020
The Building_Permits dataset contains all of the permits that have been applied for since 1999, but we are only interested in permits for this year. Add a Tester to the canvas and connect it to the reader feature type. In the parameters, set the test to:  

AppliedDate Begins With 2020

The AppliedDate attribute is written in YYYY/MM/DD notation, so we can use Begins With to filter by year. 


3. Add Shapefile Data
With our data filtered, we need to add in the shapefile address points data. Add an Esri Shapefile reader to the canvas and browse to the ParcelAddress.shp file. There are no parameters to set, so click OK.


4. Change String Case
If we inspect the OriginalAddress and ADDRESS attributes in both the Excel file and the shapefile, respectively, you will notice that the OriginalAddress is in uppercase, where ADDRESS is in lowercase. In order for us to join the datasets based on these attributes, the case will need to match. We will change ADDRESS to uppercase.  Add a StringCaseChanger to the canvas and connect it to the ParcelAddress reader feature type. In the parameters, for Change select All Attributes, then for Case Change select UPPERCASE. 


5. Join Datasets
With our Building_Permits dataset filtered to only 2020 and our ParcelAddress dataset in uppercase, we can now join our data. Add a FeatureJoiner to the canvas. Connect the Left input port to the Passed output port on the Tester, and the Right input port to the StringCaseChanger. 


In the FeatureJoiner parameters, set the Join Mode to Inner, then set the Left attribute to OriginalAddress, and the Right to ADDRESS. Click OK. If you run the workspace, you should have 17,811 features coming out of the Joined port. 


6. Clean Up Attributes
There are a lot of attributes in both datasets that we do not need. Add an AttributeKeeper to the canvas and connect it to the Joined output port on the FeatureJoiner. In the parameters, select the following attributes to keep:

• ADDRESS
• AppliedDate
• CompletedDate
• IssuedDate
• Latitude
• Longitude
• PermitType
• WorkClass

7. Write Out to CSV
Our data is now ready to be written out to CSV. Add a CSV writer to the canvas, and browse to a folder to save the file. Set the CSV File Definition to Automatic, and then click OK. In the Feature Type dialog, set the CSV File Name to PermitLocations, then click OK. 

In FME 2021.1 and newer the date fields will be written out as date data types. In older versions of FME, these fields will be written out as strings. 

​​​​​8. Run the Workspace
Finally, run the workspace and inspect the output. The final dataset should contain the Building Permit data but have spatial points associated with each permit. 

Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.

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.