Quick BI allows you to upload local Excel and CSV files to the database as file data sources to meet your business analysis requirements. This topic describes the entry points for uploading a file and how to create and modify a file data source.
Scenarios
You can create a data source by using a local file and perform joint analysis with online data sources.
Limits
File type | Limits |
Excel |
|
CSV |
|
Notes
When you upload a file, take note of the following points:
The supported field types include text, numeric, and date. When configuring fields, you can select these types or allow the system to automatically identify them based on the data content during upload. You can also modify field types.
When you upload a CSV file, we recommend that you convert the CSV file to UTF-8 format.
The encoding of CSV files in the UTF-8 format can be accurately identified. The encoding of CSV files in the GBK and GB2312 formats may not be intelligently identified. As a result, the files that are uploaded contain garbled characters.
We recommend that you use Google Chrome to upload files.
In the file that is uploaded, the data type of a column is determined based on the values in the first 100 rows:
If the values in the first 100 rows are numbers, the data type of the column is NUMERIC.
If the values in a row are strings, the data type of the column is STRING.
If the data type of a column is NUMERIC, the column values cannot be strings. If the data type of a column is STRING, the column values can be numeric values.
Preparations
Go to the Instances Page
Log on to the Quick BI console and use one of the following methods to upload local data.
Upload a local file on the Data Sources page
You need to have read and write permissions on the target data source and use a data source that supports file uploads. See Data Source Function Item List to view data sources that support file uploads.
Upload a local file on the dataset editing page
Upload a File
On the Upload File page, upload and configure a local file to be used as a data source. The supported file types include Excel and CSV files. This example uses the Sales data examples .xlsx Excel file.
When you upload an Excel file, a maximum of five sheets can be uploaded. If you need to upload more than five sheets, split them into multiple Excel files.
When you upload a CSV file, we recommend that you use the UTF-8 format.
On the File Upload page, click or drag the file to the upload area. After the upload is complete, you will automatically be redirected to the Preview Data page.
On the Preview Data page, you can preview the following information and make adjustments.
Display content
Configuration content
Configuration operation
Area ①
Number of sheets and titles in the uploaded file
Switch sheets
Click a tab to switch between sheets to preview different sheet content. The data content (Area ④) will adjust accordingly as you switch tabs.
Rename sheet
Double-click a sheet name to rename the current sheet based on actual business meaning. By default, the sheet name from the local file is used.
NoteSheet names cannot contain special characters, and cannot have spaces at the beginning or end.
Modify sheet selection status
Click the
icon before a sheet name to adjust its selection status. Sheets are selected by default. When you deselect a sheet, it will not be uploaded.NoteAt least one sheet must be selected, otherwise you cannot complete the file upload operation.
Sheet quick operations
Click the
icon on the right side of the tab bar to select quick operations for sheets from the dropdown list.
Select only current sheet: Only the current sheet is selected, meaning that after the file is uploaded, only the data content of the current sheet will be included.
Select all sheets: Select all sheets shown in the tab bar, meaning that after the file is uploaded, the data content of all sheets will be included.
Exclude current sheet: Deselect the current sheet, meaning that after the file is uploaded, the data content of the current sheet will not be included.
Area ②
File name in different systems
Display name
The display name of the uploaded file in the Quick BI system. When uploading a file, the system automatically identifies the file name and uses it as the display name in the Quick BI system. You can customize it based on your requirements.
NoteDisplay names cannot contain special characters, and cannot have spaces at the beginning or end.
Physical table name
The name of the table automatically created in the database after the file is uploaded. You can customize the physical table name based on your requirements.
NoteThe table name must be 1 to 150 characters in length, and can only contain letters, digits, and underscores (_).
Area ③
Header row of the uploaded file
Header row
You can modify the header row of the current sheet here. Click the Refresh button to apply the changes.
NoteThe input number must be a positive integer greater than 0.
Ensure there are no empty fields in the header row, otherwise an error will occur.
Area ④
Data content of the uploaded file
Data preview
Preview the data content in the Data Preview tab and perform the following operations.
Modify field name: Click the field name input box to modify the field name.
Modify field type: Click the field type icon and select an appropriate field type from the dropdown list. Supported types include text, numeric, and date.
Field details
Configure File field name, Database field name, and Field type in the Field Details tab.
After the file is uploaded to the database, it is stored as a database table. The database field name and field type become the field name and type of the generated database table, while the file column name becomes the field comment of the database table.
Click the Confirm and Upload button at the bottom of the page. In the prompt dialog box, you can view the upload progress of each sheet.
After the file upload is complete, you can Return to Data Source List to view the uploaded file data source.
Modify Uploaded Files
After uploading the first business data file, if new business data files are generated due to business changes, you can append or replace new files, or delete old files that are no longer needed, to meet the requirements for continuous and long-term tracking and analysis of business data.
Click the 
icon to the right of the target file to enter the Modify File Upload interface, where you can perform the following operations.
Configuration content | Configuration operation | |
Display name | The current display name of the file in the Quick BI system. You can customize it. Note Display names cannot contain special characters, and cannot have spaces at the beginning or end. | |
Data preview | Display data | Switch between files you want to view in the Append History panel |
Modify fields |
| |
Field details | Modify fields |
|
Add field | Click the Add Field button in the upper right corner of the table. In the Add Field dialog box, maintain the information of the new field and click OK to complete the addition. Note Adding fields is only supported when viewing Overall Data.
| |
Append history | View append history | The Append History panel displays appended files in a list format. You can click a file card to view the corresponding appended content. |
Append file | A new business data file is generated due to business growth. You can append the new file to the table corresponding to the existing file data source. For specific operations, 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 specific operations, 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 specific operations, see Delete file. | |
icon in the operation column of the target field to delete the field.
Append File
When new business data is generated based on the original file content, you can continue to append file content to ensure the real-time nature and completeness of business data. The specific operation instructions are as follows.
Log on to the Workspace > Data Sources page, find the target file in the Upload File list, and click the file name (③) or the
icon (④) in the operation column.
On the Modify File Upload page that appears, click the Append button on the right.
In the Append File panel, click or drag the file to be appended to the red box area to upload.
On the Data Preview page, switch to the sheet tab that you want to append to the original file (①). You can modify the Header Row (②) and Field Type (③) as needed, and then click Next (④).
In the Field Matching step, match fields between the appended file and the original file. The system matches fields with the same name by default. You can manually modify the matching relationship. For fields with different names, you can also manually drag them from the Fields to Match area to the Field Matching area.
NoteEnsure that the field types of the original file and the appended file match, otherwise an error will occur. The matching rules are as follows:
If the original field is Text: It can match with any field type.
If the original field is Numeric: It can only match with Numeric type.
If the original field is Date: It can only match with Date type.
If the appended file has more fields than the original file, you can click Add Field to create relevant fields in the original file for matching. If the extra fields are not needed, you can proceed to the next step.
If the appended file has fewer fields than the original file, the extra fields in the original file can remain unmatched. You can proceed directly to the next step.
If the appended file has duplicate fields, the system will try to match fields with the same name in order by default. In this case, you need to manually adjust the matching relationship in the Field Matching step to ensure that each original table field matches only one corresponding field in the appended file. For duplicate fields, you can choose one of them for matching based on your business requirements.
Click Confirm and Upload. After successful upload, you can view the appended file in the append history panel.
(Optional) If you need to replace an already appended file, click the Append history
Replace file 
icon to the right of the file to be replaced in the panel to enter the interface for related operations.
Replace File
When part of the data in the original file needs to be replaced while other data remains valid, you can replace the new data in the existing data source to maintain data accuracy. The specific operation instructions are as follows.
In the Append History panel on the Modify File Upload page, select the file to be replaced, click the
icon in the card, or click the Replace Data button in the upper right corner of the data table.
On the Replace File page, click or drag the new file to the red box area to upload.
NoteOnly single-sheet file replacement is supported. If your uploaded file has multiple sheets, please select the corresponding sheet on the preview page and upload it.
On the Data Preview page, switch to the sheet tab that you want to replace the original file with (①). You can modify the Header Row (②) and Field Type (③) as needed, and then click Next (④).
In the Field Matching step, match fields between the replacement file and the original file. The system matches fields with the same name by default. You can manually modify the matching relationship. For fields with different names, you can also manually drag them from the Fields to Match area to the Field Matching area.
NoteEnsure that the field types of the original file and the replacement file match, otherwise an error will occur. The matching rules are as follows:
If the original field is Text: It can match with any field type.
If the original field is Numeric: It can only match with Numeric type.
If the original field is Date: It can only match with Date type.
If the replacement file has more fields than the original file, you can click Add Field to create relevant fields in the original file for matching. If the extra fields are not needed, you can proceed to the next step.
If the replacement file has fewer fields than the original file, the extra fields in the original file can remain unmatched. After replacement, the extra fields will still be retained in the overall data. You can view them in the Field Details of the overall data.
If the replacement file has duplicate fields, the system will try to match fields with the same name in order by default. In this case, you need to manually adjust the matching relationship in the Field Matching step to ensure that each original table field matches only one corresponding field in the appended file. For duplicate fields, you can choose one of them for matching based on your business requirements.
Click Confirm and Upload. After successful upload, you can view the replaced file in the append history panel.
Delete File
When you no longer need to use a business data file, or if the file contains dirty data, you can select and delete the corresponding file in the Append History panel of the Modify File Upload page.
Sample Local Files
You can download the following CSV file for learning: Sales data examples.csv.
You can download the following Excel file for learning: Sales data examples .xlsx.
The following table 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 | The name of the customer. |
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 | Transportation method |
profit_amt | double | The amount of the profit. |
price | double | Unit price |
shipping_cost | double | Transportation cost |
area | varchar | Area |
province | varchar | The province. |
city | varchar | The city. |
product_type | varchar | The service type. |
product_sub_type | varchar | The subtype of the product. |
product_name | varchar | Product name |
product_box | varchar | The packaging of the product. |
shipping_date | datetime | Shipping date |
FAQ
1. The field content of the uploaded CSV file contains garbled characters. What should I do?
Quick BI can decode CSV files in the UTF-8 format. In most cases, Quick BI can also decode CSV files in the GBK or GB2312 format. In specific cases, Quick BI cannot decode these files. Therefore, you must convert these files to the UTF-8 format.
Open a CSV file using Notepad.
Select Save As.
Click the dropdown arrow for Encoding.
From the Encoding list, select UTF-8.
2. An error "Field types do not match" occurs when appending a file. What should I do?
Reason 1: There is an obvious inconsistency in field types between the source table and the append table. You need to manually modify the data type of one of the tables or re-match the fields.
Reason 2: If an Excel field in the append table has empty data, Quick BI will default to identifying this field as text type. If the corresponding field in the original table is numeric type, an error "Field types do not match" will occur. You can add any numeric value to the empty field column in the Excel table to be appended, and then the upload will succeed.
3. File upload fails with the error "Excel file parsing failed," or the data is inaccurate after uploading?
Reason: The format or content of the uploaded file does not meet the upload requirements. For such issues, check against the file upload notes:
Solution:
Check 1: Verify if the file type is CSV or Excel. If it's Excel, check if the format is xls or xlsx, and whether the uploaded file meets the content mentioned in Notes.
Check 2: Confirm if the uploaded file format and size meet the standards described in Limits.
4. Cannot find the upload file button or the button is grayed out and cannot be selected?
Reason 1: Uploading files requires permission to create a file data source. Your user type needs to be a developer and you need to have workspace developer or higher permissions, or have been granted collaborative authorization to use/edit the data source, to have the permission and entry point for uploading file data sources.
Reason 2: No read and write permissions for the target data source. Please ensure that the database account used for Quick BI has read and write permissions for that database. Without read and write permissions, uploads will not be possible. Contact the database administrator to grant the appropriate permissions.
5. After appending a file/adding new fields, the new fields are not displayed in the dataset?
Reason: The original table did not have these fields. After adding new fields, they are not selected in the dataset by default. You need to manually check them in the model configuration interface of the dataset editing page to display them.
6. After clicking upload file, CSV files cannot be displayed when selecting files?
Reason: When clicking the upload file button in a Windows environment, by default only local xlsx format files are displayed when selecting files. You need to select "All Files" in the file type options to find CSV format files.