City councillors have voted to amend noise control laws and residents living in affected areas must be informed of these changes.

You have been recommended by your manager to take on the task of finding all affected addresses. There's a tight deadline, and at least three city councillors are standing watching you work. The pressure is on, and it's up to you to deliver!

This exercise is the first part of the project. You know that the address database for the city is stored in an Esri Geodatabase whose schema matches the Local Government Information Model PostalAddress table.

However, you are told that the software used to carry out automated bulk mailings requires addresses stored in an Excel spreadsheet using a completely different schema.

So, your first task is to create a workspace that converts addresses from Geodatabase to Excel, mapping the schema at the same time.

1) Open a blank workspace
As usual, the first task is to familiarize yourself with the data. To do this open a blank workspace and ensure that Visual Preview is open: View > Windows > Visual Preview.

Open your computer's file explorer and browse to the dataset:

C:\FMEData2019\Data\Addresses\Addresses.gdb

Then drag and drop the Addresses.gdb in the Visual Preview window:

Once the Geodatabase has been dropped onto Visual Preview, a Select Dataset to View dialog will appear. Ensure that the Format is Esri Geodatabase (File Geodb Open API) and then click OK. Then ensure that the Table View is open and the Table is showing PostalAddress. For more space you can close the Graphics View as we are only interested in the table:

2) Add Creator and FeatureReader Transformers
Now that you are familiar with the source data, we can add the data to the workspace.

We can choose to read the source data using either a reader or a FeatureReader transformer. The FeatureReader will allow us to build in a spatial filter so - because we believe this project may need some filtering - we'll use a FeatureReader transformer with a Creator to create a feature to trigger it.

So place a Creator transformer and connect it to a FeatureReader:

Inspect the FeatureReader parameters and set up the parameters as follows:

3) Add an Excel Writer
Now let's add a writer to write the output data. There currently seems no benefit or need to use a FeatureWriter, so select Writers > Add Writer from the menu bar and use the following:

Setting 'Import from Dataset' will let us import an Excel spreadsheet to use as a guide. Click OK to add the writer.

4) Import Feature Types
At this point you are prompted to select the dataset to import a schema definition from. The two fields should be set up with the same values as the writer. Set the Dataset parameter as follows:

This file is our guide/template. Click OK to accept the values. The new feature type will be created to match the chosen Excel schema.

5) Add an AttributeManager Transformer
Now we can start to map the schema from the reader (FeatureReader) to the writer. As you'll have noticed, the two do not currently match up very well.

So, place an AttributeManager connected between the FeatureReader:PostalAddress output port and the PostalAddress writer feature type.

Its parameters will look like this:

Firstly let's clear up the reader schema by removing some of the unwanted attributes.

Click on the following attributes and either press the Delete key or click the - button on the dialog to remove them:

• OBJECTID
• GlobalID
• INTSTATE
• INTPSTLCD
• REPRESENT
• STATUS
• LASTUPDATE
• LASTEDITOR

6) Rename Attributes
Several source attributes can be written to the output as they are, but do need renaming first.

In the AttributeManager rename the following:

• PSTLCITY to City
• PSTLPROV to Province
• POSTALCODE to PostalCode
• COUNTRY to Country

If the AttributeManager is connected to the writer feature type, then you should be able to select the Output Attribute field from a drop-down list instead of typing it in.

7) Create an Attribute (Provider)
Two attributes on the output (Provider and UpdateDate) are new and cannot be copied from the source data. They must be created.

In the AttributeManager create the new attribute "Provider." Because the attribute exists on the output schema, you can again select it from the drop-down list.

Set a fixed value such as your own organization name, "Safe Software," or "City of Interopolis."

8) Create an Attribute (UpdateDate)
Now create the new attribute "UpdateDate". Rather than hard-coding a value, click on the drop-down arrow in the Attribute Value field and choose Open Text Editor.

In the text editor locate the Date/Time Function called DateTimeNow and double-click to place it in the editor:

By default it creates a datetime in ISO syntax, which is fine for us, so click OK to accept this.

9) Create an Attribute (Owners)
Notice that there is an Owners field on the output, but there are two owner fields (OWNERNM1 and OWNERNM2) on the input. So to set the output attribute we need to concatenate the two input fields. That's simple enough.

Still in the AttributeManager parameters dialog, create a new attribute called Owners. In the Attribute Value column for this attribute, click the drop-down arrow and choose Open Text Editor.

In the text editor open the FME Feature Attributes section on the left-hand menu. Locate the OWNERNM1 and OWNERNM2 attributes and double-click them in turn to add them to the editor window. In the editor window, click between the two attributes and press the spacebar to add a space character.

@Value(OWNERNM1) @Value(OWNERNM2)

You can either ignore the trailing space generated on records without a second owner (it won't affect the output's usefulness) or use a TrimRight function to remove it:

@TrimRight(@Value(OWNERNM1) @Value(OWNERNM2))

Click OK to close the editor dialog.

10) Remove Attributes
Now that we have the Owners field, we can remove the attributes OWNERNM1 and OWNERNM2. However... the order of operations is now important. Owners MUST be created in the parameters dialog before OWNERNM1 and OWNERNM2 are removed.

To do so, click the newly created Owners field. Use the up-arrow button (^) on the dialog to move it up above OWNERNM1.

Then remove OWNERNM1 and OWNERNM2. The dialog will now look like this:

Click OK to close the AttributeManager parameters.

11) Run To the AttributeManager
It's about time to run the workspace to make sure everything we've done so far produces the correct result. Ensure feature caching is turned on, then run the workspace by clicking on the AttributeManager transformer and selecting Run to This:

This will run the translation up to this transformer, but not writing any output (which we don't need yet).

Inspect the AttributeManager:Output cache to confirm that the procedure worked as expected:

12) Add an AttributeSplitter Transformer
Looking at the output schema there are two fields for Number and Street (for example "3305" and "W 10th Av"). However, the source schema condenses that information into one field with <space> characters separating the fields ("3305 W 10th Av"). So to set the output attributes we need to split the single input field.

Insert an AttributeSplitter transformer. Insert it before the AttributeManager - then if there are any actions to carry out on the split attributes we can use the same AttributeManager transformer.

View the AttributeSplitter parameters. Set PSTLADDRESS as the attribute to split and enter a space character into the Delimiter parameter. Notice that a list name is set in the List Name parameter (we'll use that list shortly):

Click OK to close the dialog. If you run the workspace now and inspect the cache, you'll see the address as a list attribute in the Feature Information window:

_list{0} (encoded: utf-8) 3305
_list{1} (encoded: utf-8) W
_list{2} (encoded: utf-8) 10th
_list{3} (encoded: utf-8) Av

Remember a list attribute is one that can store multiple values under a single name (_list).

13) Copy List Attribute
Now let's handle the Number field in the output. Go back to the AttributeManager parameters.

Notice that there is now an entry for the list attribute called _list{}. However, this is just the list attribute "in general" - it isn't showing each element (value) in the list.

What we need to do is create a new attribute and copy the list element we want into it. So, in the Output Attribute field create a new attribute called Number by selecting it from the drop-down list.

For the Attribute Value field click the drop-down arrow and select Attribute Value > _list{}.

You will now be prompted to select the element in the list. Ensure it is set to zero (0) and click OK.

Click Apply/OK to confirm the changes. Run the workspace to the AttributeManager transformer and inspect the AttributeManager:Output cache to ensure the number is being copied.

14) Construct Attribute
The final step is to recreate the Street attribute, without it being prefixed by the address number. One method would be to use the text editor to concatenate _list{1}, _list{2}, and _list{3} just as we did with owner names. However, with lists we can use a special ListConcatenator transformer; it just needs a preliminary step.

So, open the AttributeManager parameters again. This time create a new attribute called _list{0}, click the drop-down arrow for its value and select the option to set it to null:

This empties the first list element so that the rest can be easily concatenated together. To do so, place a ListConcatenator transformer after the AttributeManager.

Open the parameters dialog. Select _list{} as the list to concatenate. Set the Separator Character to a space. Set the Destination Attribute to Street (to match the output schema), and set Drop Empty and Null Elements to Yes:

Click OK to accept these changes and close the dialog. Run the workspace to this point and examine the output. You will find that we concatenated all parts of the street name back together.

15) Set the Event Field
There's one final attribute to set. It could be done in the AttributeManager, but to demonstrate a different method, open the Feature Type dialog for the Excel writer.

Click on the User Attributes tab and set a fixed value for the Event field:

Close the dialog and all output attributes should now be showing a green arrow, except for Event, which has a pink arrow to show it is being set in the feature type itself.

Save the workspace and then run it to create the output dataset.

WARNING

The Excel writer has a parameter called Overwrite Existing File, which by default is set to No. This is probably a good time to change the parameter to Yes, if you have been running the entire workspace each time.

Click on the PostalAddress writer feature type to open the popup menu then click on the View Written Data button to inspect the data in Visual Preview. The output (in the Table View) should look like this: