Connecting to Excel
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 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, go to the main menu, click New, and then select Data Connector.
In the New Data Connector dialog, click inside the File Name box and enter a name for your data connector.
Click the Data Provider dropdown and choose Microsoft Excel.
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. Note that changes to the original file will no longer affect the generated data connector.
- Use the UNC Path option to specify the full network path to the file (e.g. \\server1\shared\productdata.xlsx). Note that changes to the original file will affect the data once the data connector gets refreshed.
Expand the Advanced section for some additional options:
- If the first line of your Excel sheets does not contain header text, uncheck the First Row Is Header property.
- If you do not want columns with a mixture of text and numbers to be interpreted as text, uncheck the Treat Mixed Types As Text property.
- 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 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 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.
To read multiple files located in the same folder, 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: