How to read data from multiple Excel worksheets from one file

If you require to read data from multiple worksheets from one Excel file, follow the below instructions:

Illustrated below is an example of a flow where the SFTP Server source step is used and a Parallel control flow is used to read the data from each worksheet in the Excel file.

  1. Add a Pass Through step as a reader step.

  2. Add a source step where you can pull the file from such as a SFTP Server, Relay Base File System, Google Drive, Amazon S3 Bucket, etc.

  3. Add a Parallel control flow.

  4. Add a sub-flow on the Parallel flow by clicking the highlighted icon in the below image. The number of sub-flows you will add is the number of Excel worksheets that you want to read from.

image

  1. In each sub-flow, add an Excel Reader step. Change the configuration in each Excel reader step by inputting the Worksheet number as highlighted in the below image. eg. The 1st sub-flow will reference worksheet 0. The 2nd sub-flow will reference worksheet 1. The 3rd sub-flow will reference worksheet 2.