City councilors 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 councilors 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) Inspect Data
As usual, the first task is to familiarize yourself with the data. To do this open the following dataset within the FME Data Inspector:

The table that is to be translated is called "PostalAddress." The important thing here is not how the data looks in the graphic display, but more what attributes exist in the Table View window.

2) Add Creator/FeatureReader
Now that you are familiar with the source data, start FME Workbench and begin with an empty 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 parameter. Set up the paarameters as follows:

Ensure Run with Feature Caching is turned on and run the workspace. Inspect the FeatureReader:PostalAddress output cache to confirm that all addresses are being read correctly.

3) Add 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 menubar 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 AttributeManager
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 two.

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 press the - button to remove them:

• OBJECTID
• GlobalID
• OWNERNM1
• OWNERNM2
• 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 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 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.

Click OK again to close the AttributeManager dialog.

9) Run Transformer
Run the workspace by clicking on the AttributeManager transformer and selecting Run to This:

This will run the transformer by itself, using previous caches, but not writing any output (which we don't need yet).

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

10) Add AttributeSplitter
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"). Therefore we'll have to split the source attribute up to match the Writer schema.

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).

11) Rename 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 and inspect the AttributeManager:Output cache to ensure the number is being copied.

12) Construct Attribute
The final step is to recreate the Street attribute, without it being prefixed by the address number.

View the AttributeManager parameters again. This time in the Output Attribute field creates a new attribute called Street by selecting it from the list.

Unlike the Number field, we want to create this value by concatenating several list elements. So click the drop-down arrow in the Attribute Value field and choose Open Text Editor.

Locate _list{} in the FME Feature Attributes menu and carry out the following steps:

• Double-click _list{} and when prompted select element 1. Click OK
• Press the spacebar to enter a <space> character
• Double-click _list{} and when prompted select element 2. Click OK.
• Press the spacebar to enter a <space> character
• Double-click _list{} and when prompted select element 3. Click OK.

The dialog will now look like this:

In this way we will have concatenated all parts of the street name back together, for example:

"W"+"17th"+"St" becomes "W 17th St"

We're assuming that no street name has more than three parts to it, but that's reasonable for our example.

13) Run Workspace
Save the workspace. Turn off feature caching and then run the workspace.

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, because we've been running the workspace multiple times!

Open the containing folder to check the output has been written. Inspect the data in the FME Data Inspector. The output (in the Table View window) should look like this: