Search results
Suggest a FeaturePDF

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. Data icon configuration panel
  2. In the DATA configuration panel, click on the NEW DATA button. New Data Panel
  3. In the connection type panel, choose the Excel data source type. Connection types panel
  4. Specify the data source name without special characters, in Name field. Excel datasource properties

You can connect Excel data using the following two options,

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

Source types

Load from disk

  1. Choose File option in Type field. Now, the upload option will start displaying like below. New connection panel
  2. Click on the three-dotted button to open upload box. Browse the Excel file and click open, New connection panel
  3. Here, Bold Reports allows different Extract Type and click here to know about each extract type. Choose 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. Left schema
  5. Now, drag and drop the worksheet/table you want to analyze, from the left pane in the query design view page and execute. New connection panel

    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. New connection panel

Load from external URL

  1. Choose External option in Type field. New connection panel
  2. Specify an external URL in the External text area. Choose source type
  3. Here, Bold Reports allows different Extract Type and click here to know about each extract type. Choose 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. Left schema
  5. Drag and drop the table from the left pane in the query design view page and execute. New connection panel
  6. Click Finish to save the data source with a relevant name to proceed with designing report. Dataset list view

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.

Excel with multiple worksheet

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

Choose worksheet as extract type

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

Left schema

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.

New connection panel

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

Choose tables as extract type

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

Tables listed in left 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.