Drag and drop an Excel file


1. Overview

This article shows you how to display data from a Microsoft Excel file using simple drag-and-drop operations in Dundas BI. This also works for other file data sources such as Access, flat files such as CSV, or XML files.

You can also connect to an Excel file located at a URL/address or in a shared folder if you want to display future updates to the file.

2. Drag an Excel file to the Explore window

This example depicts these steps followed in a new dashboard created from the main menu, but you can follow the same steps in a new metric set.

Locate your Excel file in Windows Explorer or Finder, then drag and drop it into the Explore window in Dundas BI.

Drag Excel file to Explore window
Drag Excel file to Explore window

To browse through files on your device to find the one to upload instead of dragging and dropping, create an Excel data connector and click to Browse/Choose File.

The Excel file is automatically imported into Dundas BI as a data connector.

Expand the Data Connectors folder in the Explore window to see the auto-generated data connector for the Excel file. You can further expand the data connector to see any sheets and tables found in the file, and the column structure of each sheet.

Auto-generated data connector for Excel file
Auto-generated data connector for Excel file

Drag an Excel sheet from the Explore window to the canvas. A table visualization appears which displays the contents of the Excel sheet.

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

3. Drag an Excel file directly to the canvas

Optionally, you can drag an Excel file from your computer and drop it directly onto the canvas of the dashboard when editing, or onto a new metric set.

The Excel file will also be added as a data connector in the Explore window as shown above, but a table visualization will automatically appear on the canvas displaying data from one of the file's sheets.

4. Remove a column from the table

If the Data Analysis Panel is not visible next to the visualization, select Data Analysis Panel in the toolbar or from the right-click menu.

In the Data Analysis Panel, remove the 'x' column by clicking the x icon to its right.

Remove column
Remove column

5. Replace date column with time hierarchy level

Expand the Time Dimensions folder in the Explore window.

Expand the default time dimension and also its first hierarchy.

Drag the Month level of the hierarchy and drop it onto your existing date column in the Data Analysis Panel.

Replace date column with Month level
Replace date column with Month level

The table visualization is updated to group the dates into months and display the month names in the 'date' column.

Table showing month names
Table showing month names

6. Re-visualize as a bar chart

Finally, select the table, click Re-Visualize from the toolbar, and then click Bar. The table is now displayed as a bar chart with month names along the X axis.

Re-visualize as a bar chart
Re-visualize as a bar chart

7. Notes

Dragging and dropping a file to the application will upload the file.

  • For updates to the file to take effect, you must either edit the data connector and upload a new file, or specify a URL or path to access the file in the data connector settings. See Connecting to Excel for more information.
  • An uploaded file will be automatically warehoused after the first use to improve performance.
  • You can download an uploaded file. Right-click the checked-in data connector from the Explore window or main menu and select Download File.

8. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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