Connecting to Excel
1. Overview
This article shows you how to connect to Microsoft Excel (XLS/XLSX/XLSM) files by creating a data connector. This is usually for connecting to an Excel file that exists on your network or the internet rather than importing the data directly into Dundas BI.
2. Drag and drop an Excel file
The easiest way to work with an Excel file is to drag it from Windows Explorer or Finder and drop it directly into Dundas BI. 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, create a new data connector.
In the New Data Connector dialog, click inside the Name box and enter a name for your data connector.
Click the Data Provider dropdown and choose Microsoft Excel.
Next, specify the Excel file using one of two options:
- Click Choose File or Browse and use the file selector to select an Excel file to import into Dundas BI. Note that changes to the original file afterward will not be reflected in Dundas BI unless you edit this data connector later and upload the updated file in its place.
- Or, use the File Path option to specify a web or network address to access the file (a URL or a UNC path). A UNC path example is \\Server1\Shared\Latest.xlsx. Changes to the data in this file will be reflected in Dundas BI afterward.
When using a UNC path, use the Authentication dropdown to choose the authentication method used to connect to the file. For example, set it to Specified Windows credentials and enter your domain, username, and password on Windows.
Expand the Advanced section for some additional options, such as:
- If the first line of your Excel sheets does not contain header text, uncheck First Row Is Header.
- If you do not want columns with a mixture of text and numbers to be interpreted as text, uncheck Treat Mixed Types As Text.
- If your data is culture-specific, select the Culture Name from the drop-down. This will enable Dundas BI to convert the data to the proper format for each culture.
The Define structure option lets you add additional tables or sheets to list under the Excel data connector, and edit column details such as its data type in case these were not discovered automatically. It is recommended to first submit the new data connector dialog and let the auto-discovery run when creating a data connector, and then you can go back to edit it if necessary.
When finished, click the Submit button at the bottom of the dialog.
When choosing data in a metric set or dragging data from Explore onto a dashboard canvas, you can now find and expand the newly added data connector to see its discovered sheets and tables. For example, choose a column of data to start with, or you can drag an entire sheet onto a dashboard canvas to see its data displayed in a table visualization (in Raw Data format).
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 or the main menu and locate the Excel data connector. Right-click (or long tap) the data connector and choose Edit.
In the data connector dialog, scroll down and 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 and tables. The columns and tables are discovered on-demand so if you don't see any listed, click Re-discover table.
Select a column or table and then set the details for the column as needed.
5. Read multiple files
To read data from multiple files located in the same folder together, specify the path using a wildcard character as a filename. The matching Excel files in the folder should contain the same table structure and sheet name, and their data will be combined into one data connector.
For example, the folder below contains 2 Excel files with the same structure:
In the File Path, specify the path using an asterisk (*) as a wildcard character in the filename:
The data from multiple files is then combined into a single set of sheets/tables and columns for you to work with.
A File Name column is included in the structure of the resulting tables, and you can choose whether to include or exclude this in your metric sets:
