Knowledge Base

Connecting to Excel Files

Next >>

<< Prev

Connecting to Excel Files tutorial transcript

This tutorial walks through the fundamentals of how to connect your data in Analytics Canvas.

Analytics Canvas connects to many different data sources. As you can see here, in the New Source menu, there is a long list of native connections to different kinds of data sources including, Text files, Excel files, databases, and online data sources.

connecting to excel files data sources

Today we’ll be using the Excel file, which is available for download. You can save this file on your computer to recreate everything we do here, with the same data set.

To connect to the Excel file, go to the navigation window and click on the New Source button. Select Excel File from the drop down menu, navigate to where you saved the file, and click Open.

Analytics Canvas prompts you to select a data range. Here we can see the content of the file, including rows and columns, as well as all the sheets in the file. We can choose to connect to particular range and click OK.

Note: If your table doesn’t have column names, de-select the Include column name option.

Connecting to Excel files import

In this example, we have only one sheet in the file and no excessive formatting. So let’s select the Market Name, Website, Street, City, Country, State and Zip, and click OK.

You will see the data loaded onto the data source block on the Main Canvas. The block will appear blue before being run for the first time. Or it may appear red, if Analytics Canvas has not loaded the data successfully. In my example, as I run the block, the data loads fully and the block becomes green.

Connecting to Excel files Block Colors

Let’s look at the data viewer. On the left side we see a preview of the Excel data that we just loaded in. On the right side, the source of the data input is displayed. You can see the table name and the location of the data.

Connecting to Excel files Import

Modifying Data Output

There is a lot we can do in Data Viewer. If we want to sort the data in a column in ascending or descending order, we can click on the little triangles in the right corner of the column name.
We can explore the data by right-clicking and selecting Show Data Type by Color. Dates will be highlighted in light blue and numbers in brown.

Connecting to Excel files Data Type color

If any of our column names aren’t ideal, we can click on the Edit Output Columns tab on the top left side of the Data Viewer, select the column name that we want to correct and type in the new name.

Clicking on the data type icon on the right of the column name allows us to change the default data type for that column.
If a field contains data that is concatenated and we’d like to split it, we can do that by clicking on the Add Calculated column. We will talk about writing calculation formulas later in our tutorial series.

Update Data Constantly or Use Extract

Something to consider before we begin working on our data is if we want to refresh it constantly or use an extract form.

Refreshing constantly means that data will be updated every time during preview run. This is best when we need to work with newest changes in data imported in Analytics Canvas. That being said, sometimes frequent updating will result in slow performance as the data is queried from databases for every change, depending on the database.

Connecting to extract means that the data is extracted into Analytics Canvas. This is useful when the data that we work with is updated infrequently. It is also helpful when working with slow database or to take query load from a critical system.

To access those options, click Update Data Settings tab in data viewer window. Depending on the nature of data source that you work with, you may or may not be able to use this option. For example, some cloud-based data sources must be extracted.

For now we will connect to extract. So with just a few clicks, navigate to data source, select your data, connect, and you are ready to work with your dataset.

Changing Input Data

What if we realize that we’ve missed a column on the input?

If you want to change the data that you’ve just loaded, you can click on the Select button under the Configure (input) tab and add or remove rows, or select the entire sheet or multiple sheets. In this example, let’s add two more rows: x and y. We will then go ahead and select all that we want, and click OK.

You can also add or delete columns by clicking on the Edit Output Columns tab. You can add columns by clicking on the Add Input Column button and selecting any columns that you need from the menu. To delete a column, you simply click on the cross button that is located next to the column name.

Again, we can view the data in the Data Viewer window, and verify the data we are connecting to. It’s looking good! We now have all the columns we want.

Contents:

  • Importing data
  • Connecting to Excel Files
  • Show data type
  • Change column name
  • Update data snapshot
  • Add columns
  • Configure input to add or remove columns

Related Materials:

Transcript – Connecting to Excel Files
Data Set

Other resources:

Tutorial 1: Getting Started
Documentation: Importing Data

Length:

5:05

You can download a free trial of Analytics Canvas to follow along with the video.

Back to more Support    |   Try Analytics Canvas for Free

Was this article helpful?

Related Tutorials/Video

Need Support?

If you need any further help, don't hesitate to send a support request to our support team.

Contact Support