Tutorial: Getting Started with Excel

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2020.1

Introduction

Why is reading or writing Microsoft Excel more complex than other formats? Because Excel has no limits on its data structure. Most tabular-based formats have a single data type per column; but in Excel the data type can be set on each cell, making it a very flexible format for use with FME.

 

Why use FME to process Excel Data?

You might use FME to process data because you need to:

  • Process large datasets

  • Create spatial data from columns that contain X/Y coordinates
  • Derive data from other data sources, expressions or conditional values
  • Merge sheets from different Excel files, or combine data from other sources
  • Generate Excel-based reports and data summaries
  • Automate your workflow


Terminology

FME provides read and write access to Excel spreadsheets, which are also known as workbooks. Excel spreadsheets may contain multiple worksheets, and each worksheet may optionally contain specifically named cell ranges called ‘Named Ranges’.

There are a few important definitions to be aware of before we begin working with Excel in the FME Environment. Use the following table to familiarize yourself with how FME represents the various data elements found in Excel:

Excel Term FME Term / Representation
Spreadsheet & Workbook Spreadsheet & Workbook (dataset)
Worksheet Feature Type
Column, in a worksheet or named range Attribute
Named Range Feature Type
Row Feature

The most important reference source when working with Excel data in FME is the Microsoft Excel Reader and Writer documentation page. The Excel writer documentation provides usage notes and in-depth details on functionality and parameters.

 

Articles

Viewing and Inspecting Excel Data

Learn the basics of Viewing and Inspecting Excel data through FME with Visual Preview. 

 

Excel Reader Parameters | Converting Excel to CSV

In this tutorial, you will learn how to read in multiple worksheets from an Excel workbook, join the data together based on a single value, add a column with a calculated value and then write out to CSV. 
 

Excel Writer Parameters | Converting Excel to Excel

In this tutorial, you will learn how to read in additional worksheets from the same workbook from the previous tutorial, add additional data, and then write out the data back to the same Excel workbook. 
 

Using a Template File when Writing Excel Data

In this tutorial, you will learn how to use an Excel workbook as a template file to update a workbook with pivot tables, charts, and summary tables with new data.  
 

Writing Images to Microsoft Excel Spreadsheets

In this tutorial, you will learn how to write images into an Excel spreadsheet.
 

How to Convert Microsoft Excel to Esri Shapefile

In this tutorial, you will learn how to convert a Microsoft Excel spreadsheet that contains latitude and longitude values into an Esri Shapefile. 
 

How to Convert JSON to Microsoft Excel

In this tutorial, you will learn how to read JSON from a live data stream, then write the data out to Excel. 
 

Converting RSS Feed Returns from XML to Microsoft Excel

In this tutorial, you will learn how to read XML from an RSS feed, then write the data out to Excel. 
 

Dynamically Read Multiple Excel Files

In this tutorial, you will learn how to read multiple Excel files with differing sheet names dynamically.

 

Resources

[Webinar] 5 Ways FME Can Improve Your Excel Data
[Webinar] Tips for Automating Excel Data Processing Tasks with FME
[eBook] Guide to Excel and FME
[Blog]  Excel Tasks You did not Know You Could Do Automatically

 

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.