Add Excel dataset

The Excel dataset belongs to the basic table. For the concept of basic table, please refer to: Basic table.

1.1 Version

1.2 Application scenarios

This article describes how to add Excel to FineBI as a data source for subsequent data analysis and dashboard display.

1.3 Feature introduction

Supports batch upload of Excel. If you need to update the uploaded Excel data, please refer to: Update Excel.

2.1 Confirm that the upload file type is supported

The table types that FineBI can upload are csv, xls, and xlsx.

Note 1: Supports the upload of 2003 and 2007 versions of Excel files with the suffix of xls and xlsx, and does not support uploading of Excel with the save type of Excel 5.0/95 and the suffix of xls.

Note 2: Upload of encrypted Excel is not supported.

2.2 Turn off file upload verification

In the 5.1 version after FineBI 2019-07-15, before uploading the csv format file, you need to enter "Manage> Security> Security" and turn off "File upload verification".

2.3 Cannot merge cells in the first row

You need to confirm before uploading. The first row of the added Excel cannot have merged cells, otherwise the upload will fail, as shown in the following figure:

3.1 Select data table

1) Log in to FineBI, select a business package in "Data Preparation", and click "Add Table > Excel Dataset", as shown in the figure below:

2) Choose the Excel you want to upload to FineBI. You can upload three formats: csv, xls, and xlsx. It can also select multiple tables, as shown in the following figure:

Note: You can select at most 100 Excel files, and each sheet in Excel cannot exceed 255 sheets.

3) FineBI will automatically expand all sheets in Excel, and users can check the sheets they need. As shown below:

Note: At most 100 tables can be checked on the left, otherwise the addition will fail.

Note: FineBI will automatically skip empty sheet pages when reading Excel files.

The default table name is "Excel name-sheet name", and the user can modify the table name manually.

4) Click "Continue upload", and the user can continue to add Excel data sheets.

3.2 Modify field type

When uploading, the user may need to adjust the field type of the table. The user can adjust the field type of each individual Excel or sheet page. There are two adjustment methods:

• Change the field type in the header

• Click "table structure display" to adjust the field type. For example, the operation of adjusting the field type of "Contact-sheet1" is as shown in the figure below:

3.3 Choose whether to merge tables with consistent fields

After adjusting the field types of all tables, you can click "OK".

If there is a table with "field name, field type and field order are exactly the same" in the selected table, the system will prompt: The fields in the data table are consistent, whether they are merged into one table. As shown below:

In this example, the field names, field types, and field order of "Contact-sheet1" and "Contact-sheet2" are exactly the same.

• Select "Do not merge", "Contact-sheet1" and "Contact-sheet2" will not be merged, and the final upload result is as shown in the figure below:

• Select "Merge", "Contact-sheet1" and "Contact-sheet2" to merge up and down, and merge the following table into the previous table. That is, "Contact-sheet2" is merged into "Contact2-sheet1", and the uploaded result is shown in the following figure:

Note: After adding the basic table, you can edit it. For details, see: Edit basic table.

Note: Fields recognized as a date type by BI will automatically complete the "year, month, day, hour, minute, and second" and display it as yyyy-MM-dd HH:mm:ss (example: 2020-10-15 00:00:00).

4.1 Limit on uploaded data

For Excel in xls and xlsx formats, a sheet page can only upload up to one million of data. If the amount of data exceeds the limit, multiple additional uploads can be performed.

For Excel in csv format, there is no limit on the amount of uploaded data.

4.2 Field type recognition rules

4.2.1 Extracted version identification rules

1) Prioritize recognition of whether the added field is in number format (the number starting with 0 is not recognized as a number).

2) Secondly, identify whether the added field is in date format.

3) Finally, identify whether the added field is in text format (values with more than 15 digits default to text format).

If the field type of the table after adding is inconsistent with that before adding, you can manually modify the required field type. When the added Excel dataset has a field value of decimal type (such as double, float and other floating-point types) values, the decimal places may not be displayed accurately when the grouping table or cross table is used for summary calculation.

4.2.2 Direct connection version identification rules

After removing the null value, go to the first 100 rows to prejudge the field type: there are three field types in the first 100 rows with the number greater than 1/3 as the identification type, and there are two field types with the number greater than 1/2 as the identification type. If the quantity is the same, it will be judged according to the rule of "text>date>value>empty".