Connecting to Excel

Contents[Hide]

1. Overview

This article shows you how to connect to Microsoft Excel (XLS/XLSX/XLSM) files.

2. Drag and drop an Excel file

The easiest way to work with an Excel file is to drag it from Windows Explorer and drop it directly onto the Dundas BI Explore window or the dashboard canvas. This will automatically import the Excel data to Dundas BI and create a corresponding data connector.

See Drag and drop an Excel file for more details.

3. Connect to Excel file

The following walkthrough shows you how to manually create a data connector for an Excel file with two sheets of data.

To begin, go to the main menu, click New, and then select Data Connector.

In the New Data Connector dialog, click inside the File Name box. Enter a name for your data connector.

Click the Data Provider dropdown and choose Excel file.

Set up a new data connector
Set up a new data connector

Click the Authentication dropdown to choose an authentication method. For example, set it to Specified Windows credentials and enter your domain, username, and password on Windows.

Next, specify the Excel file using one of two options:

  • Click Choose File and use the file selector to select the Excel file to be imported.
  • Or, use the UNC Path option to specify the full network path to the file. Example: \\server1\shared\productdata.xlsx

Choose the Excel file or enter its network path
Choose the Excel file or enter its network path

Note
If choosing a file to be imported, Dundas BI will upload this file and changes to the original file will not affect the generated data connector. If specifying a UNC Path, Dundas BI will retrieve the data from the original file and changes will affect the data once the data connector gets refreshed.

If the first line of your Excel sheets contains header text, expand the Advanced section and make sure the First Row Is Header property is checked (which is the default setting).

If your Excel data has columns with a mixture of text and numbers, and you want that data to be interpreted as text, select the Treat Mixed Types As Text option.

Set advanced options
Set advanced options

The Define Structure option lets you add a table (which corresponds to an Excel sheet), add columns to the table, and then define each column by setting its data type and more. While you can define tables and columns at this point, it is recommended to just click Submit and let the auto-discovery run first. Then you can go back and edit the discovered tables and columns.

Go to Explore and locate the newly added data connector. Expand it to see the Excel file and its discovered sheets. Drag a sheet to the dashboard canvas to see its values displayed in a table visualization (in Raw Data format).

Drag an Excel sheet to the dashboard canvas
Drag an Excel sheet to the dashboard canvas

4. Define tables and columns

After the initial creation and discovery of the Excel data connector, you can redefine properties of the tables and columns as needed.

First, go to Explore and locate the Excel data connector. Right-click (or long top) on the data connector and click Edit from the context menu.

In the Edit Data Connector dialog, scroll down and click Define Structure.

Click Define Structure
Click Define Structure

In the Define Data Structure dialog, select a table (which corresponds to an Excel sheet) and then scroll down to see its discovered columns. The columns are discovered on-demand so if you don't see any listed, click Re-discover table.

Discovered columns
Discovered columns

Select a column (e.g., Sales) and then set the details for the column as needed. For example, numeric columns are discovered as double columns but you may want to change the data type to Currency instead.

Define column details
Define column details

5. Reading multiple files

Starting in Version 3.0, it is possible to read multiple files under the same folder. To do this, specify the path using a wildcard character as a filename. The excel files inside the folder should contain the same table structure and sheet name.

For example, the folder below contains 2 excel files with the same structure:

Files inside a folder
Files inside a folder

In the UNC Path, specifiy the path using a wildcard character "*" in the filename part:

UNC Path
UNC Path

"File Name" is included in the structure of the table:

Structure
Structure

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri