Function Description of File Input

Version

Application Scenario

You want to read data from Excel and CSV files on the local FineDataLink server or in the shared directory to build the data warehouse.

You want to use FineDataLink to upload Excel and CSV files to the local FineDataLink server.

Function Description

FineDataLink provides a File Input function that can be used to read file data, as shown in the following figure.

Note:

You can assign data connection permission to control the local server directories accessible to each person, ensuring data security.

• To read data from local Excel and CSV files in FineDataLink or upload these files to 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.

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

1. The number of read data displayed in the frontend log does not include the header row read by the File Input operator.

For example, for an Excel file containing one header row and 999,999 data rows (one million rows in total), the number of read data rows displayed in the frontend log is 999,999 after you read the file using the File Input operator.

2. You can reference parameters when configuring File Address, Folder Address, File Filtering, and Sheet Name of the File Input operator. For details about how to use parameters, see Parameter Configuration and Use. For specific examples of using the File Input operator together with parameters, see the "Application Example" section of this document.

Excel File Reading

File Source

The option in the drop-down list will only appear if you have previously configured a data connection to the local server directory or the FTP/SFTP server and have permission to use that data connection.

File Type

Reading Excel files is supported. When you set File Type to Excel:

• Supported Excel file extensions include .xls, .xlsx, .XLS, and .XLSX.

• Microsoft Excel should be of 97 or later versions.

Read Mode

Choose between Read File and Read Folder.

1. File Reading

• Use it to read a single file.

• File Address: Select the file to be read. You can use parameters set in FineDataLink. For details, see How to Configure and Use Parameters. If the File Source is Local Server Directory, enter the path relative to /webroot/WEB-INF/assets/local_files of the FineDataLink project, for example, /home/ftpuser/test/${time}. xlsx. (The /home path is in the /webroot/WEB-INF/assets/local_files path of the FineDataLink project.)

Note:

Paste the complete file address or manually enter it in File Address, and press Enter to save it.

• When File Source is set to Local Server Directory and Read Mode is set to Read File, you can manually upload the file to the directory on the local FineDataLink server on which you have permission.  The name of the uploaded file cannot contain ? *:"<>\/| and cannot start with a space. The maximum file size is 100 MB.

2. Folder Reading

The operator reads files of the same format within the selected folder and merges file data. Fields in Excel files in the folder must be identical. The separator of CSV files in the folder must be the same.

Note:

1. If the folder contains files of multiple formats and you only want to read files of one type, you can add a condition to filter files whose names contain CSV or Excel. If no condition is set, all types of files in the folder will be read by default. 

2. Files in the folder are read concurrently by the File Input operator. At most 20 files are read concurrently.

For example, there are two files test1 and test2 in the folder. If you select Read Folder, the files will be concatenated row-wise, as shown in the following figure.

For details, see Scanning a Batch of Files for Data Synchronization.

Setting Item	Description
Folder Address	Select the folder containing the files to be read. You can select the folder or manually enter the folder address. (You can use the parameter, where you need to select the parameter after entering it.) Note: Paste the complete file address or manually enter it in File Address, and press Enter to save it.
Read Subfolder	If Read Subfolder is not selected, files in the subfolders will be skipped during reading. If it is selected, files in the subfolders will also be read.
File Filtering	You can configure filtering conditions for files to be read in the folder. You can filter files by fileName and lastModifiedTime. Filtering through parameters is supported. A usage example of lastModifiedTime: A folder contains Table A and Table B 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.) Note: 1. When you filter files using lastModifiedTime, the precision is limited to minutes, not seconds. 2. In FineDataLink 4.1.1 and later versions, the conditional judgment logic has been standardized. For details about each operator, see Condition Judgment Logic Description.

Sheet Name

Enter the name of the Sheet to be read. If it is empty, the first sheet will be read. You can use parameters in Sheet Name.

Read Range

Specify the start and end rows/columns in the Excel file for reading.

The values must be positive integers.

First Row As Field Name

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

1. There are two methods to acquire output fields, as described in the following table.

Acquisition Method	Description
Automatic Acquisition	Data types are obtained automatically through parsing.
Manual Acquisition	You can adjust the data type. The name and data type of the output field are displayed in the Manual Acquisition area. You can set the type to varchar, int, long, float, double, date, and timestamp. Note: When reading the CSV file, you cannot set the type to date and timestamp. You can add a field and delete the added field. However, existing fields cannot be deleted or reordered. Note: The name of the added field cannot be empty, cannot contain spaces, and cannot be the same as another valid field name. When the first N rows of the added field are empty, the corresponding field value is NULL.

2. Starting from FineDataLink V4.2.6.1, you can add built-in fields, including fileName (whose value is the file name), filePath (whose value is the file path), and lastModifiedTime (whose value is the file modification time) in Output Field regardless of the acquisition method.

Note:

You can rename built-in fields, with a 30-character limit. The name must not duplicate any existing valid field names.

The configured built-in fields will be displayed on the Data Preview tab page and participate in actual execution. The following figure shows the preview effect.

3. If the file/folder timestamp (the lastModifiedTime value) retrieved via FTP is eight hours later than the actual server time or incorrect, you need to modify the configuration file. This adjustment will affect the time-based filtering configured in File Filtering when you set Read Mode to Read Folder.

Modify the vsftpd.conf configuration file in /etc/vsftpd by adding the following line at the bottom:

use_localtime=YES

Restart the FTP service.  

service vsftpd restart

CSV File Reading

You need to configure the following content if setting File Type to CSV.

For details about File Source, Read Mode, First Row As Field Name, and Output Field, see the "Excel File Reading" section of this document. The following table describes other setting items.

Setting Item	Description
File Type	This operator can read CSV files when you set File Type to CSV. Supported file extensions include .csv, .CSV, .txt, .TXT, .tsv, .log, and .dt (a mixed format of CSV and XML).
Filename Extension	This option appears when you set Read Mode to Read File. The details are as follows: The filename extension is case-insensitive. Files in File Address with the specified extension will be read, and files of other formats will be skipped. You can enter multiple filename extensions, which should be separated by comma. Reading files of CSV-like types, such as TXT/TSV/LOG/DT (a mixed format of CSV and XML), is supported. Starting from FineDataLink V4.2.6.1, this setting item is no longer mandatory.
Column Separator	You can specify the separator to split data 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 select the separator from Comma (,), Tab (\t), Semicolon (;), Vertical Bar (|), Space, ASCII Character, or select Custom to customize one. You can customize the separator. Multiple characters, including Chinese characters, are supported. You can also enter the decimal ASCII code to specify an ASCII character as a special column separator. The supported decimal ASCII codes range from 0 to 32. The values of Text Qualifier, Column Separator, and Line Separator must be distinct.
Line Separator	You can specify the line separator of the CSV file. CR + LF: Windows systems LF: Unix, Linux, and other systems CR: Early Mac OS systems The values of Text Qualifier, Column Separator, and Line Separator must be distinct.
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. You can select the text qualifier from Double Quotes, Single Quote, and ASCII Character. (The supported decimal ASCII codes range from 0 to 32.) The values of Text Qualifier, Column Separator, and Line Separator must be distinct.
Encoding	You can specify the encoding for the CSV file. Supported encoding types include GBK, BIG5, ISO-8859-1, UTF-8, UTF-16, EUC_JP, EUC_KR, CP850, and GB2312. Note: You must select the correct file encoding format to prevent data corruption during reading.
Start Row To Read	It is used to specify the start row for reading in the CSV file. It must be set. The default value is 1. You are not allowed to enter a number smaller than 1. The actual reading starts from the set row. This configuration takes effect on all files to be read during folder reading. If you select First Row As Field Name, the reading will start from the set start row, and data on this row will be taken as the field name.

Custom

You can choose Custom to read files of types such as XML and JSON.

By default, the data is obtained as text data, with the content of a file per line. The column name is column by default. You can parse data using XML Parsing and JSON Parsing operators, as shown in the following figure.

Note:

For details about File Source, Read Mode, and File Address, see the "Excel File Reading" section of this document.

Filename Extension

This option appears when you set Read Mode to Read File.

• The username is case-insensitive.

• You can enter multiple filename extensions, which should be separated by comma.

• Duplicate filename extensions are not allowed.

Encoding

Supported encoding types include GBK, BIG5, ISO-8859-1, UTF-8, UTF-16, EUC_JP, EUC_KR, CP850, and GB2312.

Data Preview

The Data Preview page is shown in the following figure.

The details are as follows:

• By default, a maximum of 20 rows of data is displayed on the Data Preview page. Although the displayed fields are a union of fields of the first 5000 rows of data and column names on the first row (if the first row is specified as the field name), all the data will be output.

• If the first row contains merged cells, these cells will be split and named in the format of Field name + Number. For example, if the first-row cell containing the Name field is the merging result of three cells, the fields after splitting will be named Name, Name1, and Name2.

• If the first row of the CSV file contains fields with the same name, these fields will be renamed in the format of Field Name + Number. For example. If there are two Name fields, these fields will be renamed Name1 and Name2.

• If First Row As Field Name is not selected, the fields will be named sequentially in the format of column + Number. For example, column, column1, and column2.

Assume that you have used a File Input operator in a scheduled task with File Type set to CSV and Read Mode set to Read Folder:

• If you have set a file filtering condition using fileName and then upgraded FineDataLink to V4.1.4 or later versions, filtering conditions using fileName applying to files whose names contain .txt, .TXT, .csv, or .CSV will be added automatically and form a Logical And relationship with the original filtering condition.

• If you have not set a file filtering condition using fileName, the filtering condition using fileName you set in FineDataLink of V4.1.4 or later versions will apply to files whose names contain  .txt, .TXT, .csv, or .CSV.

The correspondence between the cell format and the data type of the field after parsing is described in the following table.

1. CSV field type issues

The content in a CSV file can only be output as string and numeric data. Ensure the precision is preserved when the data type of the output field is numeric. Time-type data, such as 2022-11-10, will be parsed as string data. Timestamp-type data will be parsed as numeric data.

2. Merged cell reading issues

The merged cell will be split, and the value will be copied and pasted to each small cell (the cells generated after the splitting) for reading.

3. Empty fields

If a header field contains empty values, the empty values will be filled in as column + number.

If a numeric field contains empty values, NULL will be filled in.

4. Reading multiple sheets of an Excel file is not supported.

5. The filename containing wildcard characters is not supported.

6. Reading password-protected Excel files is not supported.