How to read data from all the worksheets in an Excel file

If you require to read data from all the worksheets in an Excel file, you can use the Excel Raw reader step!

NB: Please note that the Excel Raw reader step also returns the sheet name from the Excel file.

Illustrated in the below image is a flow where the Excel Raw reader step is used to read the data from the Excel file, the filter step to filter for a specific sheet in the Excel file and a buffer destination step to store the data in.

The below image is the configuration of how you want the data to get returned. Leave the Worksheet field blank if you want to return all the data from all the sheets. The Smart format is selected as the Raw Format in this example.

For more information on the Raw Formats, please scroll down this article to the additional information.

image

The output for the Smart Raw Format is shown in the below page.

Illustrated in the image below is the code that is used in the Filter step to filter the first sheet from the Excel file. A Calculator step is added after the Filter step to test and view the data that gets returned after the Filter Step

image

The below image is how the values in the buffer will look and it also stores the sheet names.

Additional information:

If “Label” is selected as the Raw Format in the Excel Raw reader step, the data will be returned as per the below image.

If “2D Array” is selected as the Raw Format in the Excel Raw reader step, the data will be returned as illustrated in the below images.

image