All Products
Search
Document Center

Quick BI:Add a file to a data source

Last Updated:Feb 08, 2023

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.

Limits

Item

File type

The table header of the file that you want to upload must be placed in the first row of the first sheet. The first sheet cannot contain merged cells.

Excel

A file can contain a maximum of 100 columns.

Excel and CSV

The maximum size of a file is 50 MB.

If a file contains 98 columns and 1 million rows and the size of the file is 80 MB, you must split the file into multiple small files and upload the data of the files by appending rows.

Excel and CSV

Precautions

When you upload a file, take note of the following points:

  • When you upload a CSV file, we recommend that you convert the format of the CSV file to the 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.

Entry points

Log on to the Quick BI console and perform the steps that are shown in the following figure to upload a local file.

  • Upload a local file on the Data Sources page

    • If the data source is MySQL, SQL Server, MaxCompute, or Oracle, and you have the read/write permissions on the data source, you can upload local files to the database. intl_1

  • Upload a local file on the dataset edit page3

Upload a file

Quick BI allows you to upload local Excel files and CSV files.

  • When you upload an Excel file, only the first sheet of the file is uploaded.

  • When you upload a CSV file, we recommend that you convert the format of the CSV file to the UTF-8 format.

  1. In the Upload File panel, configure the parameters. The following table describes the parameters. intl_3

    Parameter

    Description

    File

    Select a CSV, XLSX, or XLS file from your local computer.

    Custom File Name

    After a file is uploaded, Quick BI automatically uses the file name as the custom name of the uploaded file. You can change the file name.

    The file name must be 1 to 50 characters in length, and can contain letters, digits, underscores (_), forward slashes (/), backslashes (\), vertical bars (), parentheses (), and brackets [].

    Physical Table Name

    After a file is uploaded to the database, the name of the table is automatically created in the database. You can change the name of the physical table.

    The table name must be 1 to 150 characters in length, and can only contain letters, digits, and underscores (_).

    Note

    When you upload a file to the user space, you do not need to configure this parameter.

    Data Field

    After a file is uploaded to the database, the file is stored as a database table. Database Field Name and Field Type indicate the field names and types of the generated database table. File Column Name indicates the header name of the uploaded file and the name is used as the field description of the database table. When you upload a file, you can change the database field names and field types. intl_5

  2. Click Save to complete the upload.

    If the message "The import operation is completed" appears, you can view the file that you uploaded on the Uploaded Files tab. Import succeeded_intl

Change the upload file

If a new data file is generated after you upload the initial business data file due to business changes, you can write the data of the new data file to the related physical table. This way, you can continuously track and analyze your business data over a long period of time. Change_intl

Operation

Scenario

Principle

Description

Add File

A new business data file is generated due to business growth. You can append data in the new file to the table that corresponds to the existing file data source.

The operation of appending data in a file to a table is to write rows in the related physical table.

  • The format of the file whose data you want to append or the file that you want to use as a substitute can be different from the format of the previously uploaded file.

  • If you want to append data in a specific sheet of an Excel file to an existing CSV file or replace the data in the CSV file with the data in the sheet of the Excel file, make sure that the names and data types of all fields in the Excel file are the same as those in the CSV file.

  • After you upload a file for the first time, make sure that the sequence and data types of the fields in the new file whose data you want to append or that you want to use as a substitute are the same as those in the file that you uploaded.

Replace File

New business data needs to replace original business data due to business changes. You can replace the original files with new files.

The operation of replacing a file is to replace rows and columns in a physical table.

Delete File

If the business data file whose data you want to append contains dirty data, you can remove the file from the Uploaded Files tab for the table. The content of the business data file is automatically synchronized to downstream dashboards to ensure precise analysis of business data.

The operation of removing a file is to remove rows from a physical table.

None.

Add Field

You can add fields to a physical table to meet the analysis requirements.

The operation of adding a field is to write a column to a physical table.

None.

Delete Field

You can remove fields from a physical table to meet the analysis requirements.

The operation of removing a field is to remove a column from a physical table.

None.

What do I do if the field content of the CSV file that I uploaded contains garbled characters?

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.

  1. Use Notepad to open the CSV file.

  2. Choose File > Save As.

  3. Click the drop-down list next to Encoding.

  4. Select UTF-8 from the Encoding drop-down list.

Sample local files

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

Field

Type

Description

order_id

varchar

Order ID

report_date

datetime

Order date

customer_name

varchar

Customer name

order_level

varchar

Order level

order_number

double

Product quantity in the order

order_amt

double

Order amount

back_point

double

Discount

shipping_type

varchar

Transportation method

profit_amt

double

Profit amount

price

double

Unit price

shipping_cost

double

Transportation 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 packaging

shipping_date

datetime

Shipping date