All Products
Search
Document Center

Quick BI:Add a File to a Data Source

Last Updated:Apr 28, 2024

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

Description

Applicable Document 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

Usage notes

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 point

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

    • When you connect to a MySQL, MaxCompute, AnalyticDB for MySQL 3.0, SQL Server, PostgreSQL, Hologres, ClickHouse, AnalyticDB for PostgreSQL, PolarDB for MySQL, Oracle, Alibaba PolarDB Distributed Edition (DRDS, Alibaba PolarDB-X), PolarDB for PostgreSQL, PolarDB for Oracle (formerly PPAS), Apache Doris, or StarRocks, and you have the read and write permissions on the data source, you can upload local files to the database. image

  • Upload a local file imageon the dataset edit page

Upload an object

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

    Note

    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.

    image

    Parameter

    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 (_).

    Field preview

    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. You can modify the File Field Name, Database Field Name, and Field Type parameters when you upload an object.

    image

  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.

    image

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.

Click the chart on the imageright of the target file to enter the file upload modification interface.

image

  • On the File Upload Record page, perform the following operations:

    image

    Operation

    Scenarios

    How it works

    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.

    N/A

  • In the Advanced section, perform the following operations:

    image

    Operation

    Scenarios

    How it works

    Description

    Add fields

    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.

    N/A

    Delete a 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.

    N/A

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

The ID of the order

report_date

datetime

The date on which the order was generated

customer_name

varchar

Customer name

order_level

varchar

The level of the order

order_number

double

The product quantity in the order

order_amt

double

The amount of the order

back_point

double

The discount

shipping_type

varchar

The transportation method

profit_amt

double

The profit amount

price

double

Unit price

shipping_cost

double

The cost of transportation

area

varchar

The region.

province

varchar

Province

city

varchar

The city.

product_type

varchar

The type of the product.

product_sub_type

varchar

The product subtype

product_name

varchar

The name of the product.

product_box

varchar

The packaging of the product

shipping_date

datetime

The shipping date