Reading Data from a Multi-Sheet Excel File

  • Last update: December 27, 2024
  • 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. 

    iconNote:
    Modify Sheet 4 on the fourth line of the code to the name of the added sheet during actual use.
    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.


    附件列表


    主题: Data Development - Scheduled Task
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    8s后關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy