Adding Excel Datasets

Version

Application Scenario

This document describes how to add an Excel file to Public Data.

Function Description

FineBI supports uploading batch Excel files.

Confirming the File Type

You can upload CSV, XLS, and XLSX files.

Note:

1. You can upload Excel files of which versions are Excel 2003 and Excel 2007 with the xls and xlsx suffixes. However, you cannot upload Excel files saved as Excel 5.0/95 with the xls suffix.

2. Encrypted Excel files cannot be uploaded.

Disabling File Upload Verification

You need to choose System Management > Security Management > Security and disable File Upload Verification before uploading a CSV file.

No Merged Cells in the First Row of Excel

Before uploading the file, you need to confirm that the first row of the Excel table has no merged cell, otherwise, the upload will fail.

No Content Calculated Through Functions

Besides, you need to confirm that no content calculated through functions such as VLOOKUP and SUMIFS is included in the Excel dataset.

You can download the Example.rar.

Uploading the Excel File

Go to Public Data and select a folder on which you have management permissions. Choose Add Dataset > Excel Dataset, as shown in the following figure.

Select the Excel files you want to upload. You can box-select multiple files with Ctrl or Shift.

Note:

You can select no more than 100 Excel files, and the number of sheets in each Excel file cannot exceed 255.

3. The sheets will be expanded automatically. Select the needed Excel tables and click OK.

Note:

1. You can select no more than 100 tables in the left area.

2. Blank sheets will be skipped when read in FineBI.

The name of the table is that of the sheet by default. You can modify the name manually.

If there are tables with the same field name, field type, and field order in the selected tables, a prompt "The fields in the data table are consistent, whether they are merged into one table?" will be displayed.

· If you click Do not merge, the three tables will be uploaded normally.

· If you click Merge, the Shanghai and Beijing tables that contain the same fields will be merged into one table.

Uploaded Data Volume Limits

You are advised to upload an Excel file with no more than 1 million cells. (More than 1 million cells may cause performance risks.) You can upload an Excel file with more than 1 million cells by multiple times.

Identification Rules in Field Type During Upload

Identification Rules in Extracted Data Version

First, it identifies whether the added field is in numeric format (numbers starting with 0 are not recognized as numbers.).

Second, it identifies whether the added field is in date format.

Third, it identifies whether the added field is in text format (values longer than 15 characters default to text format.).

If the field types of the table after the table is added differ from those before the table is added, you can modify the field types manually. If there is a field value with a decimal type (such as double, float, etc.) in the added Excel dataset, decimal places may not be accurately displayed when you use a group table or cross table for summary calculation.

Identification Rules in Directly Connected Version

After the null value is removed, the first 100 rows are selected for field type prediction: If there are three field types in the first 100 rows, the field type whose number of rows exceeds 33 is the identification type. If there are two field types in the first 100 rows, the field type whose number of rows exceeds 50 is the identification type. If there are more than one field types that have the maximum number, the identification type will be determined in order of text, data, value, and null.