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.
Source Data
The source data is a Microsoft Excel spreadsheet with public art installations in downtown Vancouver.
Step-by-step Instructions
In this exercise, you will add a new column using the AttributeManager to keep track of the person who last reviewed the asset.
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 were 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, the data would be appended to the bottom each time the workspace ran, resulting in duplicate 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 the 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 the Run button on the top toolbar, or by selecting Run > Run Workspace from 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 mini toolbar and clicking on the View Written Data button.
The data will open in Data Preview, where you will see the newly added column.
Preventing Schema Lock
Since the source dataset was small, FME could read all the data before dropping the table. If this were a larger dataset or a database, a schema lock may occur because FME is still trying to read from the database while dropping the table. To prevent this, add a FeatureHolder transformer after the reader to hold all features until FME finishes reading.
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.