Quick BI lets you upload local Excel and CSV files to a database. You can use these files as data sources for business analysis. This topic describes the entry points for uploading files and explains how to create and modify a file data source.
Scenarios
You can create a data source from a local file and analyze it together with online data sources.
Limits
File type | Limits |
Excel |
|
CSV |
|
Notes
Note the following when you upload a file:
The supported field types are text, numeric, and date. When you configure fields, you can select these types or let the system automatically identify them based on the data content. You can also modify the field types.
When you upload a CSV file, convert the file to the UTF-8 format.
Quick BI accurately identifies the encoding of CSV files in UTF-8 format. However, the encoding of CSV files in GBK and GB2312 formats may not be identified automatically. This can result in garbled text.
Use Google Chrome to upload files.
In an uploaded file, the data type of a column is determined by the values in the first 100 rows:
If the first 100 rows contain only numbers, the system identifies the column as numeric.
If any of the first 100 rows contains a string, the system identifies the column as a string.
Numeric fields are not compatible with string data. String fields are compatible with numeric data.
Preparations
Entry Points
Log on to the Quick BI console and use one of the following methods to upload a local file.
Upload a local file from the Data Sources page:
You must have read and write permissions for the target data source, and the data source must support file uploads. For more information about which data sources support file uploads, see Data Source Function Item List.
Upload a local file from the dataset editing page:
Upload a file
On the Upload File page, you can upload and configure a local file to use as a data source. The supported file types include Excel and CSV. This topic uses the Sales data examples .xlsx Excel file as an example.
When you upload an Excel file, you can upload a maximum of five sheets. To upload more than five sheets, split the content into multiple Excel files.
When you upload a CSV file, use the UTF-8 format.
On the File Upload page, click or drag the file to the upload area. After the upload is complete, the Preview Data page appears.
On the Preview Data page, you can preview the following information and make adjustments.
Displayed content
Configuration
Operation
Area ①
Sheets and titles in the uploaded file
Switch sheets
Click a tab to switch sheets and preview the data. The data in Area ④ changes when you switch tabs.
Rename a sheet
Double-click a sheet name to rename it based on your business needs. By default, Quick BI uses the sheet name from the local file.
NoteSheet names cannot contain special characters or leading or trailing spaces.
Change the selection status of a sheet
Click the
icon next to a sheet name to change its selection status. By default, all sheets are selected. If you clear the check box for a sheet, that sheet is not uploaded.NoteYou must select at least one sheet to upload the file.
Sheet shortcuts
Click the
icon on the right side of the tab bar and select a shortcut from the drop-down list.
Select only current sheet: Only the current sheet is selected. After the file is uploaded, it contains only the data from the current sheet.
Select all sheets: All sheets in the tab bar are selected. After the file is uploaded, it contains the data from all sheets.
Exclude current sheet: The current sheet is deselected. After the file is uploaded, it does not contain the data from the current sheet.
Area ②
File names
Display Name
The display name of the uploaded file in Quick BI. When you upload a file, the system automatically uses the file name as the display name. You can change the name as needed.
NoteDisplay names cannot contain special characters or leading or trailing spaces.
Physical Table Name
The name of the table that is automatically created in the database after the file is uploaded. You can change the physical table name as needed.
NoteA physical table name can contain only letters, digits, and underscores (_). The name must be no more than 150 characters in length.
Area ③
Header row
Header Row
You can change the header row for the current sheet here.
NoteThe number must be an integer greater than 0.
Make sure the header row does not contain empty fields. Otherwise, an error occurs.
Area ④
Data content of the uploaded file
Data Preview
On the Data Preview tab, preview the data of the uploaded file, including field names, field types, and specific data. During the upload, the system automatically identifies field types and reads field names from the file. If the automatically detected types and names do not meet your expectations, you can modify them as needed.
Modify a field name: Click the field name text box to change the field name.
Modify a field type: Click the field type icon and select a field type from the drop-down list. The supported types are text, numeric, and date.
NoteIf you change a field type to one that does not match the data content, such as changing a name field to a date type, the data is displayed as "-".
If a file contains data that does not match the field type, that data is converted to a null value during upload. A pop-up window prompts you to confirm the data content.
Field Details
On the Field Details tab, configure the File Field Name, Database Field Name, and Field Type.
After the file is uploaded, it is stored as a database table. The database field name and field type are used as the field name and type for the generated database table. The file column name becomes the field comment for the database table.
Click Confirm and Upload at the bottom of the page. A dialog box appears showing the upload progress for each sheet.
If the upload fails, you can click Back (①) in the error dialog box to return to the Preview Data step and adjust details, such as field types and sheet names. You can also click Re-upload (②) to return to the File Upload step and upload a corrected file. Alternatively, you can click the error message (③) to view the cause of the error and its trace ID.After the file is uploaded, click Return to Data Source List to view the uploaded file data source.
Modify an Uploaded File
After you upload a business data file, you may need to update it due to business changes. You can append data, replace the file, or delete old files to support continuous analysis.
Click the 
icon to the right of the target file to go to the Modify File Upload page. On this page, you can perform the following operations.
Configuration | Operation | |
Display Name | The display name of the file in Quick BI. You can change this name. Note Display names cannot contain special characters or leading or trailing spaces. | |
Data Preview | Display data | In the Append History panel, switch between files to view their data. |
Modify fields |
| |
Field Details | Modify fields |
|
Add a field | Click Add Field in the upper-right corner of the table. In the Add Field dialog box, enter the information for the new field and click OK. Note You can add fields only when you view Overall Data.
| |
Append History | View append history | The Append History panel lists the appended files. Click a file card to view the appended content. |
Append a file | If new business data files are generated due to business growth, you can append the new files to the table that corresponds to the existing file data source. For more information, see Append aFile. | |
Replace a file | If new business data needs to replace old business data due to business changes, you can replace the old file. For more information, see Replace a File. | |
Delete a file | If a business data file contains dirty data, you can delete the file from the list of uploaded files that corresponds to the table. For more information, see Delete a file. | |
icon in the Actions column of the target field to delete the field.
Append aFile
If new business data is generated, you can append it to the existing file to ensure your data is up-to-date and complete. To do so, follow these steps.
Log on to the Workbench and go to the Data Sources page. In the Uploaded Files list, find the target file and click its name (③) or the
icon (④) in the Actions column.
On the Modify File Upload page, click Append on the right.
In the Append File panel, click or drag the file that you want to append into the upload area.
On the Data Preview page, switch to the sheet tab (①) that you want to append to the original file. You can change the Header Row (②) and Field Type (③) as needed. Then, click Next (④).
In the Field Matching step, match the fields between the appended file and the original file. The system matches fields with the same name by default. You can manually change 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 occurs. The matching rules are as follows:
If the original field is text, it can be matched with any field type.
If the original field is numeric, it can be matched only with the numeric type.
If the original field is date, it can be matched only with the date type.
If the appended file has more fields than the original file, you can click Add Field to create the corresponding fields in the original file for matching. If the extra fields are not needed, you can go 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 then go to the next step.
If the appended file contains duplicate fields, the system attempts to match fields with the same name in order. In this case, you must manually adjust the matching relationship in the Field Matching step. Ensure that each field in the original table matches only one corresponding field in the appended file. For duplicate fields, select one to match based on your business needs.
Click Confirm and Upload. After the upload is successful, you can view the appended file in the append history panel.
(Optional) To replace an appended file, go to the Append History panel and click the
icon next to the file that you want to replace. This opens the Replace File interface where you can perform the required operations.
Replace a File
If some data in the original file needs to be replaced while other data is still valid, you can replace the data in the existing data source to maintain data accuracy. To do so, follow these steps.
On the Modify File Upload page, go to the Append History panel. Select the file to replace and click the
icon on the file card, or click Replace Data in the upper-right corner of the data table.
On the Replace File page, click or drag the new file to the upload area.
NoteOnly single-sheet replacement is supported. If the file you upload has multiple sheets, select the correct sheet on the preview page before you upload.
On the Data Preview page, switch to the sheet tab (①) that you want to use to replace the original file. You can change the Header Row (②) and Field Type (③) as needed. Then, click Next (④).
In the Field Matching step, match the fields between the replacement file and the original file. The system matches fields with the same name by default. You can manually change 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 occurs. The matching rules are as follows:
If the original field is text, it can be matched with any field type.
If the original field is numeric, it can be matched only with the numeric type.
If the original field is date, it can be matched only with the date type.
If the replacement file has more fields than the original file, you can click Add Field to create the corresponding fields in the original file for matching. If the extra fields are not needed, you can go 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 the replacement, the extra fields are retained in the data. You can view them in the Field Details of the data.
If the replacement file contains duplicate fields, the system attempts to match fields with the same name in order. In this case, you must manually adjust the matching relationship in the Field Matching step. Ensure that each field in the original table matches only one corresponding field in the replacement file. For duplicate fields, select one to match based on your business needs.
Click Confirm and Upload. After the upload is successful, you can view the replaced file in the append history panel.
Delete a File
If you no longer need a business data file, or if the file contains dirty data, you can select and delete it from the Append History panel on the Modify File Upload page.
Sample Local Files
You can download the following CSV file for practice: Sales data examples.csv.
You can download the following Excel file for practice: Sales data examples .xlsx.
The following table describes the structure of the sales data.
Field name | Field type | Field description |
order_id | varchar | Order ID |
report_date | datetime | Order date |
customer_name | varchar | Customer name |
order_level | varchar | Order level |
order_number | double | Order quantity |
order_amt | double | Order amount |
back_point | double | Discount |
shipping_type | varchar | Shipping method |
profit_amt | double | Profit amount |
price | double | Unit price |
shipping_cost | double | Shipping cost |
area | varchar | Area |
province | varchar | Province |
city | varchar | City |
product_type | varchar | Product type |
product_sub_type | varchar | Product subtype |
product_name | varchar | Product name |
product_box | varchar | Product package |
shipping_date | datetime | Shipping date |
What to do next
After you create a file data source, you can create a dataset and analyze the data.
Create a dataset from the file data source. For more information, see Data modeling.
NoteStarting with version 6.0.1, when you create a new dataset from a file data source, you can add a new calculated field using system built-in functions.
Visualize and analyze data using dashboards, workbooks, and data dashboards. For more information, see Data analysis.
Design a BI portal for your reports, and set up tracking tasks and metric monitoring to apply your data insights. For more information, see Data application.
FAQ
1. What do I do if the content of an uploaded CSV file contains garbled text?
Quick BI accurately decodes CSV files that are in UTF-8 format. In most cases, Quick BI can also decode CSV files in GBK or GB2312 format. However, decoding may fail in some cases. If decoding fails, you must convert these files to the UTF-8 format.
Open the CSV file using Notepad.
Select Save As.
Click the drop-down arrow for Encoding.
From the Encoding list, select UTF-8.
2. What do I do if the error "Field types do not match" occurs when I append a file?
Reason 1: The field types in the source table and the appended table are inconsistent. You must manually change the data type in one of the tables or re-match the fields.
Reason 2: If a column in the appended Excel file contains only empty cells, Quick BI identifies the field as text. If the corresponding field in the original table is numeric, the "Field types do not match" error occurs. To resolve this issue, add a numeric value to any cell in the empty column of the appended Excel file. You can then upload the file successfully.
3. What do I do if the file upload fails with the error "Excel file parsing failed", or the data is inaccurate after the upload?
Reason: The format or content of the uploaded file does not meet the requirements. Check the following items:
Solution:
Check 1: Verify that the file type is CSV or Excel. If it is an Excel file, verify that the format is .xls or .xlsx. Also, ensure that the file meets the requirements described in the Notes section.
Check 2: Confirm that the file format and size meet the standards described in the Limits section.
4. Why can't I find the upload button, or why is it grayed out?
Reason 1: You do not have the required permissions. To upload a file and create a file data source, you must be a developer with developer permissions or higher for the workspace. Alternatively, you must be granted permission to use or edit the data source.
Reason 2: You do not have read and write permissions for the target data source. Ensure that the database account used for the Quick BI data source has read and write permissions for the database. Without these permissions, you cannot upload files. Contact a database administrator to grant the required permissions.
5. Why are new fields not displayed in the dataset after I append a file or add new fields?
Reason: The original table did not contain the field. After you add a new field, it is not selected in the dataset by default. On the dataset editing page, go to the model configuration interface and manually select the check box for the field to display it.
6. Why can't I see CSV files when I select a file to upload?
Reason: In a Windows environment, when you click the upload button, the file selection dialog box shows only .xlsx files by default. To find CSV files, you must select All Files from the file type options.