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.

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.

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.

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.

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

You can also use Parameter Assignment to set parameters.

Note:

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

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.

Note:

The maximum file size for manual uploading is 100 MB.

2. Folder Reading

Use it to read multiple files of the same format in a folder.

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.

Note:

Excel files in a folder should have the same fields. CSV files in a folder should have the same separator.

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.) For details about parameters set in FineDataLink, see How to Configure and Use Parameters. 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. For details about parameters set in FineDataLink, see How to Configure and Use Parameters. 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 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.

For details about parameters set in FineDataLink, see How to Configure and Use Parameters.

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

2. Manual Acquisition:

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

CSV File Reading

For details about File Source, Read Mode, First Row As Field Name, and Output Field, see the "Excel File Reading" section of this document.

File Type

When File Type is set to CSV, files of CSV-like types can be read.

Supported file extensions include csv, CSV, txt, TXT, tsv, log, dt (a mixed format of CSV and XML), and dbf.

Filename Extension

This option appears when you select Read File as the Read Mode. The details are as follows:

• The username 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)/DBF is supported.

Column Separator

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 specify or customize the separator.

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 system

LF: Unix, Linux, and other systems

CR: Early Mac OS system

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

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.

Start Row to Read

Specify the start row for reading in the CSV file.

• This is a required field. 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

Use it to read file 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.

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 the File Input operator is used in a scheduled task of the FineDataLink project with File Type set to CSV and Read Mode set to Read Folder.

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.