Overview
Application Scenario
Excel spreadsheets (such as financial statements, sales data, and transcripts) often contain multiple sheets. You want to use FineDataLink to read data from a multi-sheet Excel file.
Implementation Method
1. Add a sheet to the source Excel file and output all sheet names to it using code.
2. Use a Parameter Assignment operator to assign names of sheets containing business data in the above figure (exclude the first row of data) to a parameter as values.
3. Use the parameter in a Loop Container node and a Data Synchronization node to iterate through all sheets to fetch data.
Notes
Ensure that:
All sheets have the same column headers.
Microsoft Excel is of 97 and later versions.
Procedure
Scenario Description
You can download the example Excel file, which contains three sheets. Sales Data.xls
You want to use FineDataLink to read data from all sheets for easy data processing.
Generating An Index Sheet Containing Sheet Names
The File Input operator and the File Input function in the Data Synchronization node allow you to read data from a certain sheet, where you can reference parameters when entering the sheet name. Therefore, you can iterate through all sheets for data fetching by outputting the sheet name as a parameter and using it in the Loop Container node and the File Input function.
1. Add a sheet (Sheet 4 in this example) to store all sheet names and place it in front of all other sheets.
2. Choose Developer > View Code and enter the following code.

function batchRetrieveSheetNames() {
let i = 1; // Define a variable i to specify the number of the row that stores the sheet
name, starting from Row 1.
let tsh = Sheets.Item('Sheet 4'); // Define Sheet 4 as tsh.
for (let sh of Sheets) { // Iterate through all sheets.
tsh.Cells.Item(i, 1).Value2 = sh.Name; // Write the sheet name into Column A of Sheet 4.
i++; // Increment the row number by one with each loop, preventing overwriting of written
content. }
}
3. Run the code to output all sheet names to Sheet 4. The data of Sheet 4 after execution is shown in the following figure.
4. Save the table.
Outputting the Sheet Name as a Parameter
You need to assign sheet names in Sheet 4 of the Sales Data table as values to a parameter to allow reading data from all sheets in subsequent steps.
Preparation Before Reading File Data
Configure the data connection to the local server directory or the FTP/SFTP server where the Excel file is stored.
The data connection Local Server Directory is used in the example of this document as the Sales Data table is stored on the local server.
Outputting the Sheet Name as a Parameter
1. Create a scheduled task in FineDataLink, drag a Parameter Assignment node onto the page, read data from Sheet 4 of Sales Data, and output the sheet name as a parameter.
Set Sheet Name to Sheet 4, set the start row to be read to 1, and select First Row As Field Name. In this way, the first row of data is not regarded as the parameter value. (The value of the first row is Sheet 4, which is not business data and should not be read in subsequent steps).
Click Data Preview, as shown in the following figure.
2. Click the Output Parameter tab, output data in all rows of the first column as parameter values, and set Parameter Name to name. You are advised to set a default parameter value in Debug Value to facilitate effect viewing at subsequent nodes where the parameter is referenced. The debug value does not participate in actual execution.
Configuring the Loop Container Node
Since there are multiple sheets and data from only one sheet can be read per fetch, you need to use a Loop Container node to iterate through all sheets.
Add a Loop Container node, set Loop Type to For-Each Loop, and select the name parameter in Object.
Reading Data from Sheets
This example uses the File Input function in the Data Synchronization node to illustrate how to read data from multiple sheets of a single Excel file.
If you need to process the fetched data further, you are advised to drag a Data Transformation node into the Loop Container node, where you can use the File Input operator to read data and other operators to process data.
1. Drag a Data Synchronization node into the Loop Container node and reference the name parameter in Sheet Name to read data from sheets.
Click Data Preview to view the fetched data.
2. Configure the target table on the Data Destination and Mapping tab page.
3. Set Write Method to Write Data into Target Table Directly.
Effect Display
1. The task page after successful execution is shown in the following figure.
2. Data in the target table is shown in the following figure.
Others
1. You can click the Publish button to publish the task to Production Mode.
2. For details about examples of reading file data in different scenarios, see File Input.