Connecting to Excel
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.
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
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.
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).
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.
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.
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.
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:
In the UNC Path, specifiy the path using a wildcard character "*" in the filename part:
"File Name" is included in the structure of the table: