Skip to main content

Connecting to Excel

This applies to: Managed Dashboards, Managed Reports

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 Symphony.

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 Symphony. This will automatically import the Excel data to Symphony and create a corresponding data connector.

See Drag and Drop an Excel File for more details.

Connect to an Excel File

You can Manually create a data connector for an Excel file with two sheets of data.

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

In the New Data Connector dialog, select inside the Name box and enter a name for your data connector.

Select the Data Provider drop-down and choose Microsoft Excel.

Set up a new data connector

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

  • Select Choose File or Browse and use the file selector to select an Excel file to import into Symphony. Note that changes to the original file afterward will not be reflected in Symphony 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 Symphony afterward.

Choose the Excel file or enter its web or network path

Note: An imported file will be automatically warehoused after the first use to improve performance.

When using a UNC path, use the Authentication drop-down 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, clear First Row Is Header.

  • If you do not want columns with a mixture of text and numbers to be interpreted as text, clear Treat Mixed Types As Text.

  • If your data is culture-specific, select the Culture Name from the drop-down. This will enable Symphony to convert the data to the proper format for each culture.

Set advanced options

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, select Submit 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).

Drag an Excel sheet to the dashboard canvas

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 select Define structure.

Select 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, select Re-discover table.

Discovered columns

Select a column or table and then set the details for the column as needed.

Define column details

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:

Files inside a folder

In the File Path, specify the path using an asterisk (*) as a wildcard character in the filename:

UNC Path

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:

Visualizing combined data including file name

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk