Data Synchronization - Server Dataset

Version

Application Scenario

You want to read data from Excel and CSV files.

The Data Synchronization node of FineDataLink allows using server datasets as data sources to read data from Excel and CSV files.

Function Description

You can create a file dataset under System Management > Data Connection > Server Dataset.

Note:

1. The uploaded Excel file is stored in the \webapps\webroot\WEB-INF\reportlets\excel path in the FineDataLink installation directory.

2. You can also use the File Input operator to read file data after configuring the data connection to the local server directory or the FTP/SFTP server.

You can read data from the file dataset using the Data Synchronization node.

This section uses the Excel dataset as an example. You can also create the dataset using TXT and XML files.

You can download the example data. Contract Fact Table Data.xlsx

Creating an Excel Dataset

1. Create a file dataset, as shown in the following figure.

2. Name the dataset Contract Fact Table Data, set File Source to Excel, and click Local File to upload the Excel file, as shown in the following figure.

3. Click the Save button in the upper right corner. The Excel dataset is created successfully, as shown in the following figure.

Configuring the Data Synchronization Node

1. Create a scheduled task and drag a Data Synchronization node onto the page.

2. Select the Contract Fact Table Data dataset as the data source, as shown in the following figure.

Click Data Preview to view the fetched Excel data, as shown in the following figure.

3. Click the Data Destination and Mapping tab, select the Contract table in the SQLite database as the target table, and keep the default field mapping relationship, which can be viewed or modified in the Field Mapping area, as shown in the following figure.

4. Set Write Method to Write Data into Target Table Directly to write all the data into the target table, as shown in the following figure.

Effect Display

1. Click Run in the upper right corner, as shown in the following figure.

2. The data in the Contract table after successful execution is shown in the following figure.

You can use parameters in File Source to define the file path read by the created file dataset.

Setting the Parameter

Set a parameter named today in a scheduled task to pass the current date, as shown in the following figure.

Creating an Excel Dataset

Store the Excel file on a server and obtain the file URL.

Note:

Ensure that the FineDataLink project can access this file successfully, otherwise the call will fail.

2. Log in to FineDataLink and follow the steps to create a file dataset.

3. Name the dataset Data, set File Source to URL, and enter the file storage path that contains the today parameter, such as http://192.168.5.175:8081/${today}. xlsx, as shown in the following figure.

In this case, if the current date is 2022-09-27, the URL is http://192.168.5.175:8081/2022-09-27.xlsx.

Note

If the URL contains Chinese characters, encode it first for normal access.

4. Set Parameter Type to Date, enter the default value, and click Preview, as shown in the following figure.

5. Click Save.

Configuring the Data Synchronization Node

The steps are the same as those in Example One.

The File Input operator in the Data Transformation node allows you to:

• Read data from Excel or CSV files of the same format in batches.

• Customize the types of fields output by Excel and CSV files.

• Fetch CSV file data from Row N+1.

• Read data from TXT files.

• Enter the filename extension in a case-insensitive manner.

• Read data from JSON and XML files.

• Read data from CSV-like files when File Type is set to CSV, such as TSV, LOG, and DT (mixed CSV and XML)/DBF files.