Excel Reader Parameters | Converting Excel to CSV

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2020.1

Introduction

FME can read Microsoft Excel spreadsheet data, and then filter, organize, and integrate it with other formats to make the most of your data assets. In this exercise, we’ll introduce the basic steps for how to read from a Microsoft Excel workbook using FME, add a new column of calculated values, then write out to CSV. 
 
The workbook we will be using contains monthly precipitation amounts for the city of Vancouver for the years 2018 - 2020, as well as the average precipitation amounts. We’d like to read in only the 2020 and Average worksheets, then calculate the percentage of precipitation compared to the average. After creating the new column, we will write out to CSV. 
 

Video

The video was recorded using FME 2016. Please note that the data and interface have changed, but the concepts are still the same. 


Step-by-Step Instructions

1. Add an Excel Reader
Open a blank workspace in FME Workbench. From the top menu, select Reader to add a new reader onto the canvas. 


In the Add Reader dialog, for the Format, type in Microsoft Excel. For Dataset, click on the ellipsis, then browse to the Precipitation.xlsx file that can be downloaded from the Files section of this article (on the top-right corner). Then click on the Parameters button. 


2. Modify the Reader Parameters
In the Parameters dialog, Open the reader parameters to set how the spreadsheet will be read into the workspace. From the Microsoft Excel Parameters dialog, you can decide what worksheets will be read in, preview the data, as well as change the data type. 
Our workbook contains four worksheets, we are only interested in 2020 and Average. Un-check 2018 and 2019. 
The parameters dialog provides a quick preview of how the worksheets will be read in up to a maximum of 100 rows. The preview for 2020 looks good, but we should confirm the preview for Average. Click on Average under Sheets to Read to load the preview. It looks like there is a row of text before our column names start on row 2, we can easily remove this. 


In the Sheets to Read section change the Field Names Row to 2 for Average, the Cell Range should automatically adjust to 3: and the Preview should update. 

Perfect, now our data is ready to be read in. Click OK twice to add the reader to the canvas. Since we are reading in two Excel worksheets, two reader feature types will appear on the canvas. This will allow us to work with these worksheets individually if we wish, or we can merge them together, which we will do in the next step.
 
For more information about Microsoft Excel reader parameters, please see the documentation.

3. Join Reader Feature Types (Worksheets)
We would like to calculate the percentage of precipitation in 2020 compared to the average. Since the data is coming from two reader feature types (worksheets), we will need to merge them together. Both datasets have a month attribute (column), so we will use that to join. Add a FeatureJoiner to the canvas and connect the 2020 reader feature type to the Left input port, and the Average to the Right. 


In the parameters, set the Left and Right to Month, then click OK. 


4. Calculate the Percentage
With our attributes merged together, we can now perform equations. Add an ExpressionEvaluator to the canvas and connect it to the Joined output port on the FeatureJoiner. In the parameters, change the Evaluator Mode to Create New Attribute, then change the New Attribute name to Percent of Average. Next,  enter in the following Arithmetic Expression:

(@Value(Precipitation)/@Value(Average Precipitation))*100

The @Value() is the attribute name. For more information on the ExpressionEvaluator, see the documentation.


5. Round Percentage Attributes
If we were to inspect our data now, we will see that the newly created attribute has multiple digits past the decimal point, we want to simplify this and round to two digits. We could have done this within the ExpressionEvaluator using the @ceil() or @round() function, but instead, we will use the AttributeRounder transformer to make it more apparent what we are doing in our workspace. Add an AttributeRounder to the canvas and connect it to the ExpressionEvaluator. In the parameters, set the Attributes to Round to Percent of Average, then set the Decimal Places to 2. 


6. Add Percent % Sign (optional)
One final step before we write out to CSV is adding in the percent sign %. This is 100% aesthetic and can be skipped if desired. Note that it will change the data type from number to string. To do this we will need to concatenate our attribute values with the percent sign. Add a StringConcatenator to the canvas and connect it to the AttributeRounder. In the parameters, change the Expression Results to Overwrite Attributes, then select Percent of Average as the Attribute to Overwrite. 
Next, for the String Parts, on the first line set the String Type to Attribute and then select Percent of Average as the String Value. On the next line set the String Type to Constant and type in % as the String Value. The StringContatenator can be used to combine multiple attribute values with constants. 


7. Write to CSV
With the new attribute (column) created we can now write out to CSV. Add a CSV writer to the canvas and browse to a folder for the dataset. Change the CSV File Definition to Automatic and then click OK. 

 
 
Change the CSV File Name to 2020Precipitation, then click OK. Connect the new writer feature type to the StringConcatenator. Run the workspace and view the results. You can either view the CSV structure in a text editor or view it in table form in Visual Preview. 


Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.