Reading Files Based on Time Increments

  • Last update: January 14, 2025
  • Overview

    Application Scenario

    The file on the FTP server is updated automatically at irregular intervals, where the data is stored in a new file after each update. You want the system to read all data from the file and store it in a database table automatically.

    Implementation Method

    You can use parameters in the File Input operator to read incremental data and set a scheduling plan to synchronize data to the database table daily and automatically.

    Prerequisite

    1. To read data from local Excel and CSV files in FineDataLink, you must have created a data connection to the local server directory, placed the files to be read in the /webroot/WEB-INF/assets/local_files path of the FineDataLink project, and had permission to use the data connection. For details, see Data Connection to Local Server Directory.

    2. To read data from Excel and CSV files on the remote FTP/SFTP server, you must have configured a data connection to the FTP/SFTP server and had permission to use the data connection. For details, see FTP/SFTP Data Connection.

    iconNote:
    This document takes reading data from the file on the local FineDataLink server as an example.

    Example One: Determining the File Update Status Based on File Modification Time

    This example is applicable to scenarios where the name of the file in the folder does not include the file adding time. Data is updated by adding a file instead of updating data in the original file.

    You can download the example data. CSV File.zip

    Setting the Parameter

    1. Create a scheduled task.

    2. Click Parameter List, add a time parameter, and set its value to yyyy-mm-dd to obtain the value of ${cyctime} (a parameter passing task execution time) in the yyyy-mm-dd format. 

    iconNote: 
    The ${cyctime} parameter is used to pass the time of each execution of the scheduled task set in Schedule Configuration, in the yyyy-mm-dd hh:mm:ss format.

    Read Data from the File in the Folder

    1. Add a Data Transformation node, and enter the Data Transformation editing page.

    2. Add a File Input operator, select the folder address, and set the file filtering condition in File Filtering to ${cyctime}<lastModifiedTime to filter the new file.

    If the task is scheduled to run at 11 PM every night, the files after filtering are those added from 12 AM to 11 PM on the current day. (Therefore, you must ensure no more files are added from 11 PM to 12 AM every night.)

    The file modification time parameter lastModifiedTime is described as follows.

    • A folder contains Table A and Table B which were added yesterday. Today, Table C is added to this folder, and the data in Table B is updated. In File Filtering, if you set lastModifiedTime to today's date, Table C and Table B will be read.  (All data in Table B will be read, not only the data updated today.) 

    • When you filter files using lastModifiedTime, the precision is limited to minutes, not seconds.

    iconNote:
    For details about the specific setting items of the File Input operator, see Function Description of File Input.

    iconNote: 
    The steps to add the filtering condition are shown in the following figure.

    3. Click Data Preview. Data in the new file has been read. 


    Outputting Data

    1. Add a DB Output operator and configure it to output new data to a database table, as shown in the following figure. 

     

    2. Set Write Method to Write Data into Target Table Directly, which is used for writing all data into the target table.

    3. Click Publish in the upper right corner to publish the task to Production Mode.

    Timed Execution

    Click Scheduling Plan and configure a plan for automatic task execution. 

    iconNote:
    In the following figure, the task is scheduled to be executed at 23:55:55 daily, you must ensure that no file is added to the folder from 23:55:55 to 24:00:00.

    Result Display

    Example Two: Determining the File Update Status Based on the File Name

    This example is applicable to scenarios where the name of the file in the folder contains the file adding time. Data is updated by adding a file instead of updating data in the original file.

    You can download the example data. 2025-01-14Inventory.xls

    This example shows how to execute a task on 2025-01-014 to read data from the file added on 2025-01-14. 

    Setting the Parameter

    1. Create a scheduled task.

    2. Click Parameter List, add a time parameter, and set its value to yyyy-mm-dd to obtain the value of ${cyctime} (a parameter passing task execution time) in the yyyy-mm-dd format. This parameter is used to filter the file added on the task execution day. /

    iconNote:
    The ${cyctime} parameter is used to pass the time of each execution of the scheduled task set in Schedule Configuration, in the yyyy-mm-dd hh:mm:ss format.

    Reading Data from Files in a Folder

    1. Add a Data Transformation node, and enter the Data Transformation editing page.

    2. Add a File Input operator, select the folder containing the files to be read, and use the time parameter when setting the filtering condition in File Filtering to filter the file whose name contains the parameter value.

    If the task is scheduled to run at 11 PM every night, the files to be read after filtering are those added from 12 AM to 11 PM on the current day. (Therefore, you must ensure no more files are added from 11 PM to 12 AM every night.)

    iconNote:
    For details about the specific setting items of the File Input operator, see Function Description of File Input.

    3. Click Data Preview. Data in the new file has been read. 

    Outputting Data

    1. Add a DB Output operator and configure it to output new data to a database table, as shown in the following figure. 

    2. Set Write Method to Write Data into Target Table Directly, which is used for writing all data into the target table. 

    3. Click Publish in the upper right corner to publish the task to Production Mode.

    Timed Execution

    Click Scheduling Plan and configure a plan for automatic task execution. 

    iconNote:
    In the following figure, the task is scheduled to be executed at 23:55:55 daily, you must ensure that no file is added to the folder from 23:55:55 to 24:00:00.

    Result Display

    附件列表


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

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

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

    不再提示

    7s后關閉

    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