This document provides a guide to processing Excel data with FME. It discusses why FME is useful for working with large Excel datasets across multiple sheets. It covers getting started by adding an Excel reader, integrating data from multiple worksheets, and manipulating Excel data structures. Specific functions covered include filtering, sorting, adding/removing columns, and running VLOOKUPs. The guide demonstrates how to construct Excel files within FME and write transformed data back to Excel.
This document provides a guide to processing Excel data with FME. It discusses why FME is useful for working with large Excel datasets across multiple sheets. It covers getting started by adding an Excel reader, integrating data from multiple worksheets, and manipulating Excel data structures. Specific functions covered include filtering, sorting, adding/removing columns, and running VLOOKUPs. The guide demonstrates how to construct Excel files within FME and write transformed data back to Excel.
This document provides a guide to processing Excel data with FME. It discusses why FME is useful for working with large Excel datasets across multiple sheets. It covers getting started by adding an Excel reader, integrating data from multiple worksheets, and manipulating Excel data structures. Specific functions covered include filtering, sorting, adding/removing columns, and running VLOOKUPs. The guide demonstrates how to construct Excel files within FME and write transformed data back to Excel.
This document provides a guide to processing Excel data with FME. It discusses why FME is useful for working with large Excel datasets across multiple sheets. It covers getting started by adding an Excel reader, integrating data from multiple worksheets, and manipulating Excel data structures. Specific functions covered include filtering, sorting, adding/removing columns, and running VLOOKUPs. The guide demonstrates how to construct Excel files within FME and write transformed data back to Excel.
• Statistics • Pivot tables Why Process Excel Data with FME? Excel is everywhere in most organizations. Everyone is comfortable using it, everyone has it installed and most applications can import and export XLS data. It’s relatively straightforward to use and if you’re only working with a small amount of data across one or a few worksheets, there’s no need to bring FME into the process.
FME is an asset in situations where you’re
working with LARGE amounts of data across multiple sheets, and many manual tasks are required. Copying, pasting and entering functions by hand carries the risk of introducing error and can be time consuming. FME’s automated workflows can execute the same functions as Excel - filtering, sorting, conditional testing - to protect the data’s integrity and save you FME's integration platform makes time. it simple to connect hundreds of systems, transform data in unlimited ways, and automate workflows. A Note on Language in FME versus Excel When working in FME and reading its documentation keep these equivalencies in mind: Feature Type = Worksheet or Named Range Attribute = Column Feature = Row Dataset = Excel File
Opening Excel Files
Use the “Add Reader” tool to open Excel files with FME. In the “Add Reader” dialog, select Microsoft Excel as the format and locate the desired file. After this open the Parameters dialog to select sheets to import and to inspect the data.
GETTING STARTED WITH
EXCEL IN FME 4 5 Select the worksheets to be imported in the “Sheets to Read” section. When you highlight a worksheet you get a preview of how the data looks, and can inspect and set data types in the “Atributes” section. You can also set the reader to recognize and preserve formulas and hyperlinks.
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.
Constructing an Excel spreadsheet within FME Workbench
After the Writer has been added to the workspace, open up its dialog. Columns are created in the “User Attributes” section. Set “Attribute Definition” to “Manual” and enter the names of the columns as you’d like them to appear in the final spreadsheet and indicate the type of data. These columns will be populated when the workflow is run with values from Attributes with the same name. Working with Attributes (columns) will be discussed further later on.
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”.
This tool categorizes data into output ports based on whether it
found a match between the Requestor and Suppliers: “Merged”, “UnmergedRequestor”, “UsedSupplier”, and “UnusedSupplier”. This comes in handy for other tasks like Vlookups, but when merging worksheets you want all the data carried over, so connect all four output ports to the Excel Writer, or whichever transformer tool comes next in the workflow.
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
Example: Using functions
Removing, Renaming & Reordering Columns
The AttributeManager can also be used to remove, rename and
reorder columns.
Example: Remove Example: Rename
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.
Example: Transposing tables
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.
At this point you’ll need to use a Tester to filter out FME
format attributes. Refer to the Knowledge article and example workspace linked at the end of this section for full details.
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, ...
These crucial settings tell the transformer to create a row in a
new table for each column (‘attr_name’) from the original table. It will then merge all the attributes together to form the new table. Check “Generate List” to tell transformer to create a list (column) for each value associated to a given feature. Give the list a name and set “Add To List” to “All Attributes”.
To clean up the table (eliminate duplicate information or FME
format jargon) use the AttributeCreator. Create a new column for “_attr_name” and each associated listname{#}._attr_ value. In FME if you assign letters as attribute names when working with Excel, the writer will assume they are referring to column designations. In the writer tool manually enter the attribute names as “A, B, C …”.
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.
Once you’ve added an AttributePivoter to your workspace
and connected the data to its input port, set the parameters in the dialog including what attribute (column) to analyze, and for what type of statistic. Add a writer and connect both the “Data” and “Summary” output ports from the AttributePivoter. In the writer dialog, set the format to Excel and in the “User Attributes” section set the “Attribute Definition” parameter to “Dynamic”. The schema is automatically created for you based on the pivot output.
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.
