Reading Files Based on Time Increments

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.

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.

Note:

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

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. 

Note: 

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.

Note:

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

Note: 

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. 

Note:

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

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. /

Note:

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.)

Note:

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. 

Note:

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