Connecting to Excel Files
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.
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.
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.
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.
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.
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.
- Importing data
- Connecting to Excel Files
- Show data type
- Change column name
- Update data snapshot
- Add columns
- Configure input to add or remove columns
You can download a free trial of Analytics Canvas to follow along with the video.