Using the File Input Operator to Read a Single File

Function Description of File Input describes the basic functions of the File Input operator, which can be used to read data from an Excel or CSV file on the local FineDataLink server and the FTP/SFTP server. Reading data from Excel or CSV files in the same format in batches is also supported.

This document uses two simple examples to explain how to read data from a single file using the File Input operator.

You can download the example data: 2024-08-06.xlsx,CSV File.rar

Configuring the FTP/SFTP Data Connection

Configure the FTP/SFTP data connection, which will be used in the File Input operator to fetch data from the FTP/SFTP server. For details, see FTP/SFTP Data Connection.

If the FTP/SFTP data connection has already been configured, check whether you have permission to use the data connection.

Reading Data from an Excel File

This section describes two usage scenarios, differentiated by whether a parameter is used in the File Input operator.

Note:

For details about each setting item, see Function Description of File Input.

File Address Without a Parameter

1. Create a scheduled task, drag a Data Transformation node onto the page, and enter the Data Transformation editing page.

2. Add a File Input operator and configure it.

• File Source: Select the FTP/SFTP data connection that connects the server where the file to be read is located.

• File Type: Select the type of the file to be read. In this example, select Excel.

Note:

1. When Excel is selected as File Type, the supported extensions are .xls, .xlsx, .XLS, and .XLSX.

2. Microsoft Excel 97 and later versions are supported.

• Read Mode: Choose between Read File and Read Folder. Read File can be used to read a single file only, while Read Folder can be used to read files from a folder. For details, see Function Description of File Input.

• File Address: Select the file to be read. Using parameters is supported, for example, /home/ftpuser/test/${time}.xlsx. You can also use Parameter Assignment to set parameters.

• Sheet Name: Fill in the name of the sheet to be read. If it is empty, the first sheet will be read.

• Row/Column To Be Read: Set the range of rows or columns to be read.

• First Row As Field: If it is selected, the first row of the parsed data will be taken as the field name. If it is not selected, the first row of content will be parsed as data.

• Output Field: Choose between Automatic Acquisition and Manual Acquisition. If you choose Automatic Acquisition, the data type of the field is read based on the parsing result, and the output field is not displayed in the Output Field area. For details about Manual Acquisition, see Function Description of File Input.

3. Click Data Preview, as shown in the following figure.

File Address with a Parameter

This section is applicable to scenarios where the Excel file is named according to the date. For example, for an instance executed on 2024-08-07, its data table name is 2024-08-06.

1. Click Parameter List, add a time parameter, and set the value to yyyy-mm-dd-1, which returns the date one day before the ${cyctime} value. You can use this parameter when entering the file address in the File Input operator to read data from the example table.

2. Enter the file address (for example, /approot1/f-fhts1118/FDL/${time}.xlsx) in the File Input operator, and press Enter, as shown in the following figure.

3. Click Data Preview to read the data from the file 2024/08/06.xlsx, as shown in the following figure. 

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

1. Enter the editing page of the Data Transformation node, drag a File Input operator onto the page, and configure the File Input operator.

• Column Separator: Set the separator, by which data is separated into multiple columns. The set separator must be consistent with the actual separator in the file. Otherwise, the file cannot be parsed correctly. You can specify or customize the separator.

Note: When selecting Custom, you can customize the separator. Multiple characters, including Chinese characters, are supported.

• Line Separator: Specify the line separator in the CSV file.

• Encoding: Specify the encoding for the CSV file. In this example, UTF-8 is selected.

• Text Qualifier: It marks the start and end of a column field to prevent the special character in the field value from being recognized as a separator, thus affecting the CSV file parsing.

• Start Row To Read: Specify the start row for reading in the CSV file. The actual reading starts from the set row.

3. Click Data Preview, as shown in the following figure.

Outputting Data

Drag a DB Table Output operator onto the page and configure the operator, as shown in the following figure. 

Set Write Method to Write Data into Target Table Directly.

You can download the example data in  "Example One: Reading Data from a Remote FTP/SFTP Server" section of this document.

Configuring a Data Connection to the Local Server Directory

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

• If the data connection has already been configured, check whether you have permission to use the data connection.

Reading a File

1. Create a scheduled task, drag a Data Transformation node onto the page, and enter the Data Transformation editing page.

2. Add a File Input operator and configure it, as shown in the following figure.

Different from reading data from a file on an FTP/SFTP server, you can upload the file in File Address when File Source is set to Local Server Directory,.

Outputting Data

Drag a DB Table Output operator onto the page and configure the operator, as shown in the following figure. 

Set Write Method to Write Data into Target Table Directly.