All Products
Search

This Product

    Quick BI:Add a File to a Data Source

    Document Center

    Quick BI:Add a File to a Data Source

    Last Updated:Jan 24, 2025

    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.image

    • Upload a local file on the dataset editing pageimage

    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.

    1. 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.111

    2. 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 image 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 image icon to access Sheet shortcuts, such as selecting only the current Sheet or all Sheets.image

      • 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.image

      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.image

      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.

      image

    3. Click the Confirm and Upload button at the bottom of the page. A pop-up window will display the upload progress for each Sheet.image

    4. Once the file upload is complete, select Return to Data Source List to view the newly uploaded file data source.image

    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 image icon next to the target file to access the Modify File Upload interface, where you can perform various operations.image

    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

    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.image

      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 image 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.

    image

    • 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:

    1. Navigate to Workbench > Data Sources. In the Upload File list, select the target file by clicking its name or the image icon.image

    2. On the Modify File Upload page, click the Append button.image

    3. In the Append File panel, click or drag the file into the designated area.image

      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.image

    4. Post-upload, the appended file will appear in the append history panel.image

    5. (Optional) To replace an appended file, click the Append HistoryimageReplace fileimage 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:

    1. On the Modify File Upload page, within the Append History panel, select the desired file for replacement and click the image icon on the card, or use the Replace Data button located at the top right of the data table.image

    2. On the Replace File page, click or drag the new file into the designated area.image

      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.image

    3. Click Confirm and Upload. The replaced file will then be visible in the append history panel.image

    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.image

    Sample local files

    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.

    1. Open a CSV file with Notepad.

    2. Select Save As.

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

    4. From the Encoding list, select UTF-8.