Connect to Excel data source

The Bold Reports^® allows you to connect to the Excel data source that retrieves data from an excel file and external URL.

File Prerequisites

1. Bold Reports^® supports connecting Excel workbooks created with version 2007 and later with .xlsx and .xls file extensions.

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

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

4. You can upload a maximum of file size 20 MB.

How to create Excel data source

To configure the Excel data source, follow the below steps:

1. Click on the Data icon in the configuration panel.
2. In the DATA configuration panel, click on the NEW DATA button.
3. In the connection type panel, choose the Excel data source type.
4. Specify the data source name without special characters, in Name field.

You can connect Excel data using the following two options,

• File Path - Load from disk
• External - Load from external URL

Load from disk

1. Choose File option in Type field. Now, the upload option will start displaying like below.
2. Click on the three-dotted button to open upload box. Browse the Excel file and click open,
3. Here, Bold Reports^® allows different Extract Type and click here to know about each extract type.
4. Click on the Connect to proceed with query design pane. The query design pane will show the worksheets/tables in a tree view on the left side pane.
5. Now, drag and drop the worksheet/table you want to analyze, from the left pane in the query design view page and execute.

   NOTE: The Excel data source does not allow multiple joins across sheets or tables.

6. Click Finish to save the data source with a relevant name to proceed with designing report.

Load from external URL

1. Choose External option in Type field.
2. Specify an external URL in the External text area.
3. Here, Bold Reports^® allows different Extract Type and click here to know about each extract type.
4. Click on the Connect to proceed with query design pane. The query design pane will show the worksheets/tables in a tree view on the left side pane.
5. Drag and drop the table from the left pane in the query design view page and execute.
6. Click Finish to save the data source with a relevant name to proceed with designing report.

Importing data

Bold Reports^® allows you to import excel as Worksheets and Tables.

• Worksheets: Choose Worksheets to fetch an entire worksheet of data along with tables.
• Table: Choose Tables to fetch tables from Excel worksheets.

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

Importing Worksheets

Bold Reports^® Excel data connector allows you to connect data from different worksheets. In the following excel file, there are two worksheets.

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

Click on the Connect. The query designer will show the worksheets in a tree view on the left side pane.

Importing Tables

Bold Reports^® Excel data connector allows you to connect multiple tables across the worksheets. In the following workbook, Order Details worksheet has two different tables.

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

Click on the Connect. The query designer will show the tables in a tree view on the left side pane.

Limitations

1. You couldn’t connect an excel file with the formats other than .xls and .xlsx extensions.
2. You couldn’t connect an empty worksheet.
3. Inserted image, charts, shapes or non-data elements in the file will not be shown.
4. You couldn’t connect the password protected worksheet.
5. You couldn’t execute an SQL query on Excel data within the code mode of the query design pane.