|Exercise 2.1||Basic Workspace Creation|
|Data||3-1-1 case location details (XLS hosted on the web)|
|Overall Goal||Create a workspace to translate XLS to CSV|
|Demonstrates||Basic workspace creation with FME Workbench|
Congratulations! You have just landed a job as a technical analyst in the Digital and Contact Centre Services department of your local city. You will be working to manage and analyze the data they collect from their 3-1-1 Contact Centre. 3-1-1 is a local phone number used widely across cities in the United States and Canada for citizens to call in and request services. Most cities also collect this data online now as well.
On your first day, you've been asked to do a simple file format translation. The 3-1-1 case data is currently stored as XLS files, one for each past month. These files are stored on the city's Open Data Portal. To host this public data in a format anyone can access, your manager wants you to translate the 12 XLS files from 2017 into a single CSV file covering the entire year.
We’ve outlined all of the actions you need to take, though FME's interface is so intuitive you should be able to carry out the exercise without the need for these step-by-step instructions.
1) Start FME Workbench
If it is not open already, start FME Workbench by selecting it from the Windows start menu. You’ll find it under Start > FME Desktop 2019.0 > FME Workbench 2019.0.
If Workbench is already open, click on the Start tab above the main canvas.
2) Select Generate Workspace
In the Create Workspace section of the Start page, select the option to Generate (Workspace). Alternatively, you can use the shortcut Ctrl+G.
3) Define Translation
The Generate Workspace tool opens up a dialog in which to define the translation to be carried out. Fill in the fields in this dialog as follows:
|Reader Format||Microsoft Excel|
Note: You can access the data locally at C:\FMEData2019\Resources\311\CaseLocationsDetails_2017_XLS.zip instead
|Writer Format||CSV (Comma Separated Value)|
The dialog will look like this:
Remember, you can set a format by typing its name, by selecting it from the drop-down list, or by choosing “More Formats” and selecting the format from the full table of formats. Note that the writer needs only a folder and not a specific file name. Also notice that we are reading data directly from the web via a URL.
You may click the Parameters buttons in the Generate Workspace dialog to check the reader/writer parameters, but the defaults are ok for this exercise. For now, you can also ignore the Coordinate System setting and the Workflow Options.
4) Generate and Examine Workspace
Click OK to close the Generate Workspace dialog. FME Workbench generates a new workspace on the canvas:
You can expand the list of attributes by clicking the arrow icon on each object:
Feature types with expanded attribute lists look like this:
5) Examine Bookmarks
Now that you've expanded your feature type, you might notice it no longer fits in the brown rectangle it was formerly contained in, labelled Reader Feature Types. This is a bookmark. Two were automatically created with Generate Workspace, but to follow FME best practice, we recommend using these to enclose sections of your workspace. Objects contained within them are grouped in the Navigator window and can be moved together on the canvas.
Bookmarks act like paragraphs for your workspaces, separating them into sections to keep them organized and help other users understand what each section does.
We'll use bookmarks later in the course, but for now let's just resize this one to contain the entire feature type. You can simply mouse over the bottom right corner until you see an expander arrow, and then click and drag to change the bookmark size to encompass the feature type:
Try moving the bookmark by clicking where it says "Reader Feature Types" and dragging. You'll notice the objects move with the bookmark.
Now your reader feature type should look like this:
Repeat the process with your writer feature type and move it closer to your reader feature type. Your workspace should now look like this:
6) Save Workspace
Save the workspace. We will be using it in a later exercise. Remember there is a toolbar save button, and on the menu, there is File > Save As.
|FME Lizard says...|
|When a translation is run immediately without adjustment it's known as a "Quick Translation." With FME, the output from a quick translation is as close to the source data in structure and meaning as possible, given the capabilities of the output format.|
7) Run Workspace
Run the workspace by clicking the run button on the toolbar, or by using Run > Run Translation on the menu bar. The workspace runs and the Translation Log window reports a successful translation:
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Feature output statistics for `CSV2' writer using keyword `CSV2_1': =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Written =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- CA18COV1 - Service Request Loca 97345 ============================================================================== Total Features Written 97345 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Read Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- CA18COV1 - Service Request Loca 97345 ============================================================================== Total Features Read 97345 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Written Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- CA18COV1 - Service Request Loca 97345 ============================================================================== Total Features Written 97345 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Translation was SUCCESSFUL with 0 warning(s) (97345 feature(s) output) FME Session Duration: 9.6 seconds. (CPU: 7.9s user, 1.1s system) END - ProcessID: 21560, peak process memory usage: 98484 kB, current process memory usage: 50760 kB Translation was SUCCESSFUL
8) Locate Output
Locate the written data in Windows Explorer to prove that it's been written as expected (do not forget the Open Containing Folder button from Exercise 1):
For now, let's use Notepad to ensure the data looks as we would expect. Right-click on CA18COV1 - Service Request Loca.csv and choose Open With > Notepad (or your preferred text editor). If you look at the top row, you should see the attribute names we saw under the writer feature type:
In the next exercise, we’ll cover how to inspect the data using FME Data Inspector.
By completing this exercise, you have learned how to: