How to add and modify a file data source - Quick BI

Quick BI enables the uploading of local Excel and CSV files to the database, serving as file data sources for business analysis. This topic describes the upload entry points and guides you through creating and modifying a file data source.

Limits

File type	Limit description
Excel	By default, the system recognizes the first row of the uploaded file as the table header row and starts recognizing the data to be uploaded from the second row. You can also adjust the title row on the Data Preview page according to your actual situation. Supports parsing and uploading up to 5 Sheets. If you need to upload content from more than 5 Sheets, split it into multiple Excel files for upload. A single Sheet cannot exceed 100 columns. The recommended file size is no more than 50 MB. For example, if a file has 98 columns and 1 million rows with a size of 80 MB, you need to split it into multiple files and upload them as appended rows.
CSV	A file can contain a maximum of 100 columns. The recommended file size is no more than 50 MB. For example, if a file has 98 columns and 1 million rows with a size of 80 MB, you need to split it into multiple files and upload them as appended rows.

Precautions

Consider the following when uploading a file:

For CSV files, converting to UTF-8 format is recommended.
CSV files in UTF-8 format are accurately recognized, while those in GBK and GB2312 formats may not be, leading to garbled characters in the uploaded files.
Google Chrome is the recommended browser for file uploads.
The data type of a column is inferred from the first 100 rows of the uploaded file:
- If the first 100 rows contain numbers, the column is classified as NUMERIC.
- If the rows contain strings, the column is classified as STRING.
Columns of type NUMERIC cannot contain strings, while STRING columns can include numeric values.

Start the Configuration

Log on to the Quick BI console and navigate to the local data upload page using any of the following methods:

Upload a local file on the Data Sources page
Ensure you have access control list permissions for the target data source and use a data source that supports file uploads. For more information, see Data Source Function Item List.
Upload a local file on the dataset editing page

Upload a File

In the Upload File interface, upload and configure the local file to be used as a data source. Supported file types include Excel and CSV files. This example demonstrates uploading an company_sales_record_en.xlsx Excel file.

Note

A maximum of 5 Sheets can be uploaded from an Excel file. If more Sheets are needed, split the content into multiple Excel files for upload.
For CSV files, it is advisable to use the UTF-8 format.

On the File Upload page, click or drag the file to the designated area. Once uploaded, the page will automatically redirect to the Data Preview page.

The Data Preview page allows you to preview and adjust the following information. p894539

Display content	Configuration content	Configuration operation
Area ① The number of Sheets and titles of the uploaded file	Switch Sheets	Click the tab to switch between Sheets and preview their data. The displayed data (i.e., Area ④) will update accordingly.
	Rename Sheet	Double-click the Sheet name to rename it, reflecting its business relevance. The default name is taken from the local file. Note Sheet names cannot include special characters or spaces at the beginning or end.
	Modify Sheet selection status	Click the icon to toggle the selection status of a Sheet. Unselected Sheets will not be uploaded. Note At least one Sheet must be selected to complete the upload.
	Sheet shortcut operations	Use the icon to access Sheet shortcuts, such as selecting only the current Sheet or all Sheets. Select only the current Sheet: Only the current Sheet is selected, and after upload, only its data will be available. Select all Sheets: All Sheets in the tab bar are selected, and their data will be included after upload. Exclude the current Sheet: The current Sheet is deselected and its data will not be included after upload.
Area ② The name of the uploaded file in different systems	Display name	The display name of the uploaded file in Quick BI. The system automatically recognizes the file name, which you can customize as needed. Note The display name cannot include special characters or spaces at the beginning or end.
	Physical table name	The name of the table created in the database after file upload. You can customize this name to suit your needs. Note The table name must be 1 to 150 characters long and can only include letters, digits, and underscores (_).
Area ③ The title row of the uploaded file	Title row	Modify the title row here and click Refresh to apply changes. Note The input number must be an integer greater than 0. Ensure no empty fields are in the title row to avoid errors.
Area ④ The data content of the uploaded file	Data Preview	Under the Data Preview tab, you can preview the data content and carry out various operations. Modify Field Name: To change the field name, click on the input box and enter the new name. Modify Field Type: Click the field type icon to choose a new type from the drop-down menu. Options include text, numeric, and date.
Area ④ The data content of the uploaded file	Field details	Configure File Field Name, Database Field Name, and Field Type under the Field Details tab. The database field name and type will be used to generate the database table after the file upload, while the file column name, which is the header of the uploaded file, will serve as the field comment in the database table. Once the file is uploaded to the database, it is stored as a database table. The database field names and types correspond to those used to create the table, whereas the file column names serve as the header names of the uploaded file and will be used as comments for the table fields.

Click the Confirm and Upload button at the bottom of the page. A pop-up window will display the upload progress for each Sheet.
Once the file upload is complete, select Return to Data Source List to view the newly uploaded file data source.

Modify Uploaded Files

If business changes generate new data files, you can append, replace, or delete files to maintain continuous and long-term business data tracking and analysis.

Click the icon next to the target file to access the Modify File Upload interface, where you can perform various operations.

Configuration content		Configuration operation
Display name		The display name of the current file in the Quick BI system, which supports custom modification. Note The display name cannot contain special characters and cannot have spaces at the beginning or end.
Data Preview	Display data	Switch the file you want to view in the Append History panel
Data Preview	Modify field	Modify field name: Click the field name input box to modify the field name. Modify field type: Click the field type icon and select the appropriate field type from the drop-down list. Supported types include text, numeric, and date. Note Fields can only be modified when viewing the file as Overall Data.
Field details	Modify field	Modify field name: Click the file column name input box to modify the field name. Modify field type: Click the field type drop-down selection box and select the appropriate field type from the drop-down list. Supported types include text, numeric, and date. Delete field: Click the icon in the target field operation column to delete the field. Note Fields can only be modified when viewing the file as Overall Data.
Add field		Click the Add Field button at the top right of the table. In the Add Field pop-up window, maintain the information of the new field and click Confirm to complete the addition. Note Fields can only be added when viewing the file as Overall Data. Field display name: The display name of the field to be added on the Quick BI platform. Physical field name: The name of the field to be added in the database physical table. Field type: Supports text, numeric, and date.
Append history	View append history	The Append History panel displays the appended files in a list form. You can view the corresponding append content by clicking the file card.
	Append file	Due to business growth, a new business data file is generated. You can append data in the new file to the table that corresponds to the existing file data source. For more information, see Append File.
	Replace file	New business data needs to replace original business data due to business changes. You can replace the original files with new files. For more information, see Replace File.
	Delete file	If a business data file contains dirty data, you can delete the file with dirty data from the upload file list corresponding to the table. For more information, see Delete File.

Append File

To ensure the real-time completeness of business data, append new data to the original file content. Follow these steps:

Navigate to Workbench > Data Sources. In the Upload File list, select the target file by clicking its name or the icon.
On the Modify File Upload page, click the Append button.
In the Append File panel, click or drag the file into the designated area.
Note
- Only single Sheet files are supported for appending. If the file has multiple Sheets, select the appropriate Sheet on the preview page.
- The newly appended file can differ in type from the original, but the field names, data types, and order must match. For instance, appending data from an Excel Sheet to an existing CSV file requires consistency in these aspects.
- Ensure the field columns in the new file match those in the original file. Add new fields to the overall data before uploading if necessary.
Post-upload, the appended file will appear in the append history panel.
(Optional) To replace an appended file, click the Append HistoryReplace file icon and follow the prompts for replacement.

Replace File

To maintain data accuracy, replace parts of the original file with new data while keeping other data intact. Here's how:

On the Modify File Upload page, within the Append History panel, select the desired file for replacement and click the icon on the card, or use the Replace Data button located at the top right of the data table.
On the Replace File page, click or drag the new file into the designated area.
Note
- Only single Sheet files are supported for replacement. If the file has multiple Sheets, select the appropriate Sheet on the preview page.
- The new file can differ in type from the original, but the field names, data types, and order must match. For example, replacing a CSV file with data from an Excel Sheet requires consistency in these aspects.
- Ensure the field columns in the new file match those in the original file. Add new fields to the overall data before uploading if necessary.
Click Confirm and Upload. The replaced file will then be visible in the append history panel.

Delete File

To remove a business data file, whether it's no longer needed or contains dirty data, select and delete it from the Append History panel in Modify File Upload.

Sample local files

Download the following CSV file for practice:Sales data examples.csv
Download the following Excel file for practice:Sales data examples.xlsx.

The table below describes the fields in the sample local files.

Field name	Field type	Field description
order_id	varchar	The ID of the order.
report_date	datetime	The date on which the order is generated.
customer_name	varchar	Customer name
order_level	varchar	The level of the order.
order_number	double	The number of orders.
order_amt	double	Order amount
back_point	double	The discount.
shipping_type	varchar	The transportation method.
profit_amt	double	The amount of the profit.
price	double	The unit price.
shipping_cost	double	Transportation cost
area	varchar	Geographical area
province	varchar	Province
city	varchar	The city.
product_type	varchar	Type
product_sub_type	varchar	Product subtype
product_name	varchar	Product name
product_box	varchar	The packaging of the product.
shipping_date	datetime	Shipping date

FAQ

1. What should I do if the field content of the uploaded CSV file is garbled?

Quick BI can decode CSV files in UTF-8 format. While it can often decode files in GBK or GB2312 formats, there are cases where it cannot. In such instances, convert the files to UTF-8 format.

Open a CSV file with Notepad.
Select Save As.
Click the drop-down arrow next to Encoding.
From the Encoding list, select UTF-8.