Dynamically Read Multiple Excel Files

Introduction

Unlike some other formats in FME, where you can set the reader to read in features as a single feature type, the Microsoft Excel reader requires additional steps to ensure that all files & sheets are read dynamically. 

By default, the Microsoft Excel reader will scan the schema of the first file & its sheets. This can be seen when going to the Parameters when adding a reader and viewing the Sheets to Read section. If these parameters are not adjusted, then sheets with different names and schemas will not be read dynamically. 


To read in multiple Excel files with different sheet names, users will need to change the Sheets to Read parameter using the steps below.
 

Step-By-Step Instructions

1. Add a Microsoft Excel Reader
Add a reader to a blank workspace and configure the parameters as follows:

• Format: Microsoft Excel
• Dataset: Select multiple files or a folder containing files you want to read
• Workflow Options: Single Merged Feature Type

2. Edit the Microsoft Excel Reader Parameters
Click on the “Parameters…” button to open the Microsoft Excel Reader parameters. When opening the parameters, FME will scan the schema of the first file you selected or the first file in the selected folder. This is reflected in the Sheets to Read section and the schema preview.

Under the Sheets to Read section, click on the Select drop-down button and click None. You should see the check marks beside all the scanned sheets disappear. This is expected. 


Click OK to close the parameters. Click OK again to add the reader to your workspace. You are now ready to read in all your Excel files and sheets dynamically.
 

Additional Information

If you want to learn more about dynamic workflows in FME, how they operate, and how to configure them, then please take a look at our tutorial series on dynamic workflows. 
https://community.safe.com/s/article/dynamic-workflow-tutorial-introduction

For more information on the Microsoft Excel Reader, consult the FME documentation.
https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/xlsx/XLSX_reader.htm