Files
Introduction
Simply writing data into a new table in a database is not much more complex than writing to a file format. There are several parameters for each feature type (table) that allow users to control write access to that table. The main thing to consider is whether or not the table already exists in the database, if it does do you want to erase it first and recreate it or update what is already there? For more information on the database operations available in FME, please see the Feature Operation documentation.
In this article, we will read data from Microsoft Excel and then write it out to PostGIS. In part 1, we will drop and create the table since it already exists in our database. In part 2, we will truncate the data.
Source Data
The table that we will be working with is from the PostGIS training database. Please see Creating a PostgreSQL/PostGIS Training Database with FME before you begin to create your own version of this database to work with.
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.
The initial public.West_End table has four attributes (columns): name, title, longitude, and latitude. As well, there are only 14 rows (records).
Step-by-step Instructions
Part 1: Drop and Create
1. Add a Microsoft Excel Reader
Start FME Workbench and begin with a blank canvas. Add a Microsoft Excel reader to the canvas and browse to the PublicArt.xlsx file that is available from the Files section of this article. Set the Coord. System to LL84 and then click the Parameters button.
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.
In the Parameters, we only need to read in the West End Excel spreadsheet. Uncheck all of the neighborhoods except West End. Next, double-check to ensure that the Longitude and Latitude attributes have the Type x_coordinate and y_coordinate, respectively. Depending on your version of FME, this may or may not set automatically. If it is not set, click the drop-down to switch the Type to x_coordinate or y_coordinate (you may need to set the Attribute Definition to Manual).
Having the Type set to x_coordinate or y_coordinate tells the reader to create points from the Excel data as it reads the file. Click OK twice to add the reader.
2. Inspect the Data
Now, let’s inspect the data to ensure the reader created the points. Click the reader feature type to open the mini toolbar, then select View Source Data to open Data Preview (formerly Visual Preview).
There should be 14 different pieces of public art, and there should also be points in the Geometry view.
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.
3. Add PostGIS Writer
Now, from the Writers menu, click Add Writer. For Format, select PostGIS, and for Connection, select the FMETraining connection created in Creating a PostgreSQL/PostGIS Training Database with FME.
For Table Definition, select Copy from Reader, so that the source schema is used to define the PostGIS table, then click OK to add the writer.
4. Define Table
Once the writer is added to the canvas, double-click on the writer feature type to open the parameters.
Leave the Table Name as West_End. Set the Table Qualifier to public.
The Table qualifier is the same as the Schema for Table Listing in the PostGIS Reader parameters. It is specified per database table, as one could write to multiple schemas using the same database writer.
Next, we need to define how the table is to be handled. Since this table was included when the FMETraining database was created in Creating a PostgreSQL/PostGIS Training Database with FME, we need to either update the data or recreate the data. For this example, let’s recreate.
Set the Feature Operation to Insert, then set the Table Handling to Drop and Create. This will drop the existing public.West_End table, then create it again using the data from the Microsoft Excel spreadsheet.
If we weren’t sure whether this table already existed and were ok with appending more data if it did, we would use Create If Needed. Now, if we wanted to remove the old data, but were confident that the table schema was correct, we could use Truncate Existing, which will be covered in Part 2.
5. Map Attributes
The reader and writer feature types must now be connected. Connect the West End reader feature type to the public.West_End writer feature type. If you click the drop-down arrow next to each, you will notice that the arrows are yellow and red, which means that the attributes are not correctly mapped. This is due to how PostGIS/PostgreSQL handles attributes: everything is lowercase, whereas the attributes from the Excel file are in Title Case.
To quickly correct this, right-click on the connection line and select Auto Connect Attributes. If you were planning to modify the attributes or further transform your data before writing it out, it is recommended that you use the Replace Link with AttributeManager option instead.
6. Run Workspace and Inspect Output
Save and run the workspace. Then click on the public.WestEnd_PublicArt writer feature type to open the mini toolbar, then click the View Written Data button.
Dropping and creating the table is a quick solution if you are not concerned about losing data. The data from the Microsoft Excel spreadsheet very closely matched the data already in the database, so you will see minimal change. To really see a change, complete Part 2, then re-run Part 1 to reset the database table.
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.
Part 2: Truncate a Table
Truncating a table removes all of the rows from the table, but leaves the attributes (columns). This is important to preserve any relationships between other tables.
1. Continue In Part 1 Workspace
Continuing in the workspace from Part 1, add a Tester transformer between the reader and writer feature types.
In the Tester parameters, set the test to Name Attribute is Missing. We want to replace the entire table in the database with only the art pieces that don’t have a name.
2. Update PostGIS Writer Parameters
This database table will be used for a project to identify names for various art pieces across the City of Vancouver. Since we know this table already exists, and we only want to change the data, we will use Truncate Existing.
Double-click on the public.West_End writer feature type to open the parameters. Then change the Table Handling to Truncate Existing.
3. Run the Workspace
Run the workspace, then inspect the output in Data Preview. The attribute names should still be the same, but there is only one record.
If you want to try truncating again. You can remove the Tester and rerun the workspace with the same parameters set for the writer.
Data Attribution
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.