Adding a Column to a Table

Introduction

Adding new column(s) to an existing table can be accomplished by using either the AttributeCreator, AttributeManager, or any transformer that creates an attribute.

Step-by-step Instructions

In this exercise, you will add a new column using the AttributeManager because you want to keep track of the person who last reviewed the asset. 
 

Source Data

The source data is a Microsoft Excel spreadsheet with public art installations in downtown Vancouver. 

1. Generate Workspace
In FME Workbench, generate a new workspace. 


In the Generate Dialog, set the Reader Format to Microsoft Excel then browse to the Downtown_PublicArt.xlsx dataset that is available in the Files section of this article. There are no parameters to set. 
Next, set the Writer Format to Microsoft Excel, then browse to the exact same Downtown_PublicArt.xlsx dataset, as we will be dropping the table and recreating it. Click OK to finish generating the workspace. 



 
2. Set the Writer and Drop Mode
After the workspace is generated, double-click on the Downtown writer feature type to open the parameters. 
In the parameters, confirm that the Writer Mode is set to Insert. Now expand the Drop/Truncate section and change the Drop Existing Sheet/Named Range to Yes. This will drop the old existing sheet, then re-create it with any new schema changes we make. If this was a database writer, we would change the Table Handling Parameter to Drop and Create. For more information on Writer Feature Type Parameters such as Table Handling, see the Documentation. 

 
If Drop Existing Sheet/Named Range was set to No, every time the workspace is run, the data would be appended to the bottom, resulting in duplicated records. 
 
3. Update Attributes
Still in the writer feature type parameters, switch to the User Attributes tab, then set the Attribute Definition to Automatic. Click OK to close the dialog. 
Switching the Attribute Definition to Automatic will automatically update the writer to include any attribute changes we make in the workspace, such as adding a new attribute (column).  For more information on User Attributes and Attribute Definitions, see the Documentation.

 
4. Create a New Column with an AttributeManager
Now we need to add an AttributeManager to create a new attribute (column). Click on the connection line between the reader and writer feature types to select it. Then add an AttributeManager to the canvas by typing “AttributeManager” to bring up the list of FME Transformers in the Quick Add Search. Select the AttributeManager from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it. 


 
Double-click on the AttributeManager to open the parameters. In the parameters, in the last row under Output Attribute where it says <Add new attribute>, type in Reviewer. Next, for Attribute Value for the new attribute type in your name. Then click OK to close the AttributeManager. 


5. Run Workspace
Confirm the new Reviewer attribute (column) will be added to the table by clicking on the arrow on the writer feature type to expand the attributes. There should now be the reviewer attribute. 


Run the workspace by clicking on the Run button on the top toolbar, or by using Run > Run Workspace on the top menu bar. 

 
After running the workspace, the table will have a Reviewer column with your name. You can view the new table by selecting the writer feature type to open the popup menu and clicking on the View Written Data button.  


The data will open in Visual Preview, where you will see the newly added column. 

 

Preventing Schema Lock

Since the source dataset is small, FME was able to read in all the data, prior to dropping the table. If this was a larger dataset or a database, a schema lock may occur as FME is still trying to read from the database and drop the table at the same time. To prevent this, add in a FeatureHolder transformer after the reader to hold all of the features until FME has finished reading. 

Data Attribution

Data used in this tutorial originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.