FME
FME
FME
6 7
Set style elements for fonts and cells in a column by clicking the
corresponding space under “Formatting”. CREATING EXCEL FILES
FME lets you construct worksheets within Workbench or write data
to an existing Excel template. In each case you begin by adding an
Excel writer using the “Add Writer” tool. Set the format to Microsoft
Excel and specify where the data will be written to and what the name
of the file will be. In the case of writing data to a template, select the
template file as the destination.
8 9
Writing to an Excel Template
In the case of writing data to a template, you have an Excel file
whose data needs to be refreshed from time to time to reflect
changes. This is useful when generating reports that summarize
raw data into meaningful charts and tables. The sheet that
holds the definition of the layout of results is designated as the
template in the FME Writer’s Parameter dialog. Set “Overwrite
Existing File” to “Yes” so that the data is overwritten and not
appended.
INTEGRATING DATA
FROM MULTIPLE
WORKSHEETS
Appending Multiple Worksheets with the Same Structure
Appending worksheets is a common task with Excel data,
especially for projects that involve analyzing data collected at
different times, by different people, or in different places.
10
In the FeatureMerger dialog, indicate which field the sheets
should be joined on and set “Process Duplicate Suppliers” to
“Yes”.
12 13
Running a Vlookup with FME
Vlookup is a handy function in Excel that allows you to search a The lookup value between Requestor and Suppliers is indicated
list for an item then return an associated value when a match with the “Join On” parameter in the FeatureMerger dialog. In this
is found. For example, in the NoSQL web page example used situation you’re only interested in matches between two worksheets
in this guide, target keyword data is returned by searching a so only the “Merged” output port of the FeatureMerger is connected
separate worksheet using the common URL column. In Excel, to the Excel Writer (or next transformer in the workflow). The
this is accomplished by entering the vlookup function and FeatureMerger will return all associated values for a match. Specify
parameters into a cell. In FME, the same result is achieved using which values are to be written when you configure the column
the FeatureMerger. names in the Excel Writer dialog.
14 15
MANIPULATING EXCEL
DATA & STRUCTURES
Adding Columns & Assigning Values
You may want to add columns while working with Excel data in
Example: Assigning a constant value
FME. For instance, if you’re merging multiple worksheets and
you wish to add a unique identifier like a date or location to each,
or perhaps you want to derive values based on the data in other In this scenario you’ll need to use a separate AttributeManager
columns. In FME this is accomplished with the AttributeManager. for each worksheet imported into the Workspace.
You can either assign a constant value to a new column or derive
values using functions or conditional statements.
16 17
Example: Using conditional statements.
18
20 21
Filtering
Easy enough in Excel - just use the filter tool. But what if you’re
interested in a subset of data that is spread across many
worksheets? FME can be used to merge these worksheets then
filter down to only the data you need using the TestFilter. This
tool filters rows using test conditions that direct data that pass
to one or more output ports, and those that fail to another. It is
worth noting that FME’s Tester can accomplish a similar result
using a simple pass/fail test.
Example: Reorder
22
Restructuring Tables
Sorting Sometimes you may need to restructure how the data is
FME has a Sorter transformer that enables you to sort rows in a presented in a table. For instance you may want to transpose
very similar way as Excel. Specify the column (attribute) to sort the data or consolidate data from several columns into one.
by and indicate whether the sort is alphabetical or numeric, FME’s AttributeExploder along with some of its other tools help
ascending or decreasing. You can also sort by more than one you accomplish these types of tasks. The AttributeExploder
column. takes a dataset and creates a row for each column / value pair,
essentially breaking it down into its tiniest pieces. Once this is
done you can combine the data back together in a way that fits
your needs.
From this:
To this:
24
Connect your data to an AttributeExploder and leave When you investigate what is coming out of the Tester in the
everything as the default setting. FME Data Inspector you’ll see a long table that consists of two
columns: one for the attribute (column) names and one for the
paired value.
26 27
To combine these bits of data back together into your new Taking a look at what is coming out of the Aggregator in the Data
transposed tables, use the Aggregator. In the dialog, set Inspector you’ll see a table where the former column names are
“Group By” to “_attr_name”, “Mode” to “Attributes Only” and now listed in rows. Values are listed in columns the transformer
“Accumulation Mode” to “Merge Incoming Attributes”. has created using the List Name that you indicated following this
pattern: listname{0}._attr_value, listname{1}._attr_value, ...
28 29
A N A LY T I C S
&
R E P O RT I N G
Excel is great for analyzing and reporting on
data, but once again, there are situations
where FME’s built-in tools provide advantages
with respect to data integrity and efficiency.
Statistics
Calculating statistics in Excel is relatively
straightforward; however it involves manually
constructing tables, entering functions and named
ranges. And you’ll have to carry out this task each
time you’d like to update it with new data. This can
be tedious in addition to threatening the quality of
the data.
30 31
In FME, the
StatisticsCalculator
tool generates
a customized
statistical report
that automatically
refreshes with new
data each time the
workflow is run.
32 33
Add a StatisticsCalculator and a Writer to the workspace. In the Writer dialog in the “User Attributes” section, set
Connect the output of this data to the StatisticsCalculator “Attribute Definition” to “Automatic”. FME will automatically
and the “Summary” port of the StatisticsCalculator to the name your columns according to the format:
Writer. In the StatisticsCalculator dialog, choose the columns “Attribute._statistic”.
to analyze with the “Attributes to Analyze” function. If you are
analyzing more than one attribute, select “For All Results” for
the “Prepend Output Attribute Names” parameter. This is to
ensure that results for the same type of statistic calculated from
different attributes can be distinguished.
Once the names of all possible attributes are listed using the
Automatic setting, you can switch to manual and customize the
statistics you’d like in your report by deleting the ones you don’t
need.
34
Pivot Tables
Pivot Tables are one of Excel’s most useful capabilities,
enabling users to analyze data by “pivoting” it around one field.
In many cases it makes sense to generate pivot reports using
Excel; however, if you are using FME to process Excel data, it is
useful to know that the AttributePivoter generates “pivot-like”
reports.
36 37
Both FME and Excel have their individual strengths. Excel
is a great platform for laying out, organizing and analyzing
data, and it is easily shared with others. FME allows you to
process data with automated, hands-free workflows. Used
in conjunction, the result is Excel data that is formatted
exactly how you need, quickly, and without manual
interference that can harm data’s integrity.