In FME, you can add a new column to an already existing table in the database using the AttributeCreator or AttributeManager with the FeatureHolder, which will ensure that the operation was carried out correctly. Let us guide you in this process!
The purpose of the task will be to add a new column using the AttributeManager. It can be used to hold information when the data set was last updated. Use the DateTimeStamper to fill in the new attribute with today’s date. Alternatively, you can also count the value for it based on other attributes or leave it blank.
Data
We will work on a file geodatabase in which there are address points from Warsaw. The SHAPE file with address points can be downloaded from https://data.gov.pl/.
Exercise
1. Start FME Workbench
If it isn’t open already, launch FME Workbench.
2. Generate Workspace
Select the option to Generate Workspace. Alternatively, you can use the shortcut Ctrl+G.
3. Set the Reader Format
In the Generate Workspace Dialog, set the reader format to Esri Geodatabase (File Geodb Open API).
4. Set the Writer Format
Set the Writer Format also to Esri Geodatabase (File Geodb Open API), and indicate the same Geodatabase, because you will update it.
5. Set the Feature Operation and Table Handling
After the workspace is generated, double-click the Writer Feature Type to open the Writer Feature Type Parameters dialog.
In the General section, set the Table Name to adresy_m_st__Warszawa_DATA.
In the Table section, ensure that if such table already exists it will be dropped, and created again, so set the Feature Operation to Insert and the Table Handling to Drop and Create.
If you want to learn more about table handling options, read our other article on databases.
6. Update Attributes
Switch to the User Attributes Tab in the Writer Parameters dialog, set the Attribute Definition to Automatic. Thanks to this, FME Workbench automatically defines the list of attributes based on input data and changes that were made in the canvas (script) such as renaming, deleting.
7. Close Source Table using a FeatureHolder
To avoid schema lock, the existing table must be closed before it is removed. This problem can be solved using the FeatureHolder, which reads and stores all processed objects until they are all converted and then share them in their original order.
Add a FeatureHolder to the canvas, and connect Reader to it.
8. Create a New Column with AttributeManager
Place the AttributeManager after the FeatureHolder. The AttributeManager will be used to add a new column to the table.
Open the transformer parameters dialog and click the last row of the Output Attribute column “Add new Attribute” to create a new column. Set the new attribute name to “DATA”.
9. Add Today’s Date
Add a DateTimeStamper transformer after the AttributeManager, and connect the DateTimeStamper: Output port to the writer.
Open the transformer parameters and set the Result Attribute to “DATA” – this will set the value of the field to the current date in the column that you just created in the AttributeManager.
10. Run Workspace
After running the translation, your table will have a date column with today’s date. You can view the new table by selecting the writer and clicking the Inspect button.
Result
Transformers:
- AttributeManager- Allows creating, renaming, copying and deleting attributes. It is possible to set values for new and existing attributes using any combination of constant values retrieved from attributes, conditional expressions or formulas.
- DateTimeStamper- Adds a timestamp to a feature as a new attribute.
- FeatureHolder- Stores incoming features until they have all arrived, and then releases them in their original order.
You might be also interested in…
FME Server as an Enterprise Integration Platform? Absolutely!
How to do a bulk database update with FME?
Source: https://knowledge.safe.com/
