Connecting Bold BI to Excel Data Source

Bold BI dashboard designer allows you to connect Excel files from both local and cloud storage.

File Prerequisites

• Bold BI supports connecting Excel workbooks created with version 2007 and later with .xlsx and .xls file extensions.

• Prepared workbook should be a structured one. It should be created with unique column names and the value in a row must be in same data type for the corresponding columns.

• The first row’ values of your worksheet will be considered as column names by default in Bold BI. So, provide a unique and meaningful column name in your spreadsheet that will make it easy to identify your data.

• You can upload a maximum of file size 200 MB.

Connecting an Excel file from Local Storage

To connect an Excel file from local storage, follow the below steps:

1. Click the Data Sources icon in the configuration panel to add a new data connection.

2. Click CREATE NEW to launch a new connection from the connection panel.

3. Select Excel data source from the connection panel.

4. Now the Excel connection window will be opened. Provide a unique name for the data source.

5. Choose the excel file from local storage by clicking the browse button and then file will be uploaded.

6. Here, Bold BI allows different Import Type and click here to know more.

7. Now, click Preview & Connect and then preview window will appear. Here, you can choose your preferred Tables and Columns.

8. Click Connect and you will be navigated to data source designer page. Here,the worksheets/tables are listed in a tree view on the left side of the editing window.

9. Now, drag and drop the worksheet/table you want to analyze, and preview the data by clicking Update.

10. You can also refresh the Excel data and click here to know more.

11. Click Save to save the data source.

Connecting an Excel file from Cloud Storage

To connect an Excel file from cloud storage, follow the below steps:

1. Click the Data Sources icon in the configuration panel to add a new data connection.

2. Click CREATE NEW to launch a new connection from the connection panel.

3. Choose the Cloud Storage from the category and for example, choose the data source as Microsoft SharePoint.

4. New window will appear. Here select your account and click Connect as shown in the following image.

5. Now, Microsoft SharePoint window appears. Set a data source name, select site and navigate through the folders to choose file as shown in the following image.

6. Here, Bold BI allows different Import Type and click here to know more.

7. You can schedule refreshes to keep your data on the dashboard up to date using Refresh Settings option and click here to know more.

8. Now, click Select and then preview window will appear. Here, you can choose your preferred Tables and Columns.

9. Click Connect and you will be navigated to data source designer page. Here, the worksheets/tables are listed in a tree view on the left side of the editing window.

10. Now, drag and drop the worksheet/table you want to analyze, and preview the data by clicking Update.

11. Click Save to save the data source.

Importing data

Bold BI allows you to import excel as Worksheets and Tables.Choose Tables to fetch tables from Excel worksheets. Choose Worksheets to fetch an entire worksheet of data along with tables.

NOTE: The import type feature is only available for the version 2007 and later with .xlsx extension format.

Importing Worksheets

Bold BI Excel data connector allows you to connect data from different worksheets.

Follow the below steps to connect data from different tables:

1. To connect entire worksheet along with tables, choose Worksheets in Import type.

2. Click Preview & Connect button and then preview window will appear.

3. Here, the sheets are listed in the left pane. To see the data preview of a sheet in the right pane, choose specific sheet from the drop-down list at top right corner of the window as shown in the following image.

Importing Tables

Bold BI Excel data connector allows you to connect multiple tables across the worksheets. In the following workbook, Order Details worksheet has two different tables. Now, you can connect both tables in the designer.

Follow the below steps to connect data from different tables:

1. To connect one or more tables from your worksheets, choose Tables in Import type.

2. Click Preview & Connect button and then preview window will appear.

3. Here, the tables are listed in the left pane. To see the data preview of a table in the right pane, choose specific table from the drop-down list at top right corner of the window as shown in the following image.

Joining Excel data source with other data source

Bold BI allows you to join tables from two or more different data sources. To combine multiple data sources, use the Shared Tables panel at the bottom of the data source designer.

Click here to know more.

Refreshing Local Excel Data Source

Bold BI allows you to refresh the excel data in your dashboard. You can update this by editing the data source connection.

Follow the below steps to edit data source connection:

1. Click on Edit Connection button in data source designer page.

2. In the Edit Connection dialog, browse your Excel file from local storage, which should be the same source file with updates or a different file name but with the same schema.

3. Click Reconnect button and now your data gets updated.

Refreshing Cloud Excel Data Source

Bold BI allows you to refresh the cloud-stored excel data in your dashboard. You can update this by creating a schedule to move data from your cloud location to an intermediate database location.

Follow the below steps to refresh the data:

1. Click Refresh Settings button in data source designer page.

2. Select the recurrence type, recurrence, start and end dates in the Refresh Settings dialog box.

   • Data refresh can be scheduled hourly, daily, weekly and monthly.
   • Application Time Zone is displayed below the date picker. Start time of the schedule is converted to client Time Zone and shown in the right side for the user’s convenience.

   

3. Now click Schedule button.

NOTE: In cloud storage, you may either replace the latest file with one of the same name or edit your existing file and save it with the modified content before refresh triggered. If schema changed in uploaded file, then data will be lost in your dashboard.

NOTE: You can also schedule refresh for already created data source. Click here to know more.

Limitations

Below are the limitations for connecting excel file in Bold BI,

• You couldn’t connect an excel file with the formats other than .xls and .xlsx extensions.
• You couldn’t connect an empty worksheet.
• Inserted image, charts, shapes or non-data elements in the file will not be shown.
• You couldn’t connect the password protected worksheet.

Related Links

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough

Excel Integration

Tutorial Video

Blog Post