Icon

Read all sheets from an Excel file

<p><strong>Read all sheets from an Excel file in a loop</strong></p><p>This workflow demonstrates how to read all sheets from an Excel (<em>.xlsx</em>) file using the <em>Excel Reader</em> node together with the <em>Read Excel Sheet Names</em> node in a loop.</p><p>To read all sheets from an <em>.xlsx</em> file, first add the <em>Read Excel Sheet Names</em> node to the workspace and chose the appropriate <em>.xlsx</em> file from the file system in the configuration dialog. The node lists all sheet names from the selected <em>.xlsx</em> file together with the file path in a data table.</p><p>This table is used to feed the <em>Table Row to Variable Loop Start</em> node which takes one row from the data table in each loop and creates a flow variable from it.</p><p>In the loop, the created flow variables are fed into the <em>Excel Reader</em> node:</p><ul><li><p>The Path variable to specify the file location</p></li><li><p>The Sheet variable to specify which sheet to access.</p></li></ul><p>The <em>Loop End</em> node collects all the sheets by appending the rows.</p>

Read all sheets from an Excel file in a loop


This workflow demonstrates how to read all sheets from an Excel (.xlsx) file using the Excel Reader node together with the Read Excel Sheet Names node in a loop.

To read all sheets from an .xlsx file, first add the Read Excel Sheet Names node to the workspace and chose the appropriate .xlsx file from the file system in the configuration dialog. The node lists all sheet names from the selected .xlsx file together with the file path in a data table.

This table is used to feed the Table Row to Variable Loop Start node which takes one row from the data table in each loop and creates a flow variable from it. In the loop, the created flow variables are fed into the Excel Reader node. The Path variable to specify the file location and the Sheet variable to specify which sheet to access.

The Loop End node collects all the sheets by appending the rows.

Access the Excel file and list all sheet names

Read and concatenate data of each sheet

Create variablesfrom the rows
Table Row to Variable Loop Start
Collect dataof different sheets
Loop End
Read sheet names fromStores_highest_sales.xlsx
Read Excel Sheet Names
Set flow variable to overwritefile path and sheet name
Excel Reader

Nodes

Extensions

Links