How to use external data sources in Excel? Today’s tutorial is all about data import (SQL, CSV, and XML) and the managing of data connections. Stay with us!
It is not surprising that we almost never store databases and a large amount of raw data in Excel worksheets. The main reason for this is that Excel was not made for this.
Use external data connections if you want to create dashboard templates, and you have to work with a huge amount of data. We think that the subject is so important. So, we provide a whole separate article about it.
The most common External Data Sources in Excel
The most important sources are:
- SQL database
- CSV file
How can we import data with the use of various external data sources into Excel?
The starting point is always the same. Go to the Data tab on the ribbon. Choose ‘Get data’ or ‘From Text/CSV’. Of course, we can import data with the help of a web query also.
Connect to External Data Sources
We’ll use the AdventureWorks SQL Server database. It’s well known and used by many. SQL Server Management studio will help us to import the given database from a backup.
First, start Excel.
Choose the Get Data > From Database > From SQL Server Database menu.
In the next window, we have to give the name of the SQL Server. The name of the Server is a mandatory field. The name of the database is optional.
By clicking on the OK button, the Navigator will appear, and we can find multiple things here. In our example we’ll use the AdventureWorks database table.
We’d like to direct your attention to the ‘Select multiple items’ checkbox. If we do not check it we can only import one table.
And what happens if we check it? Then we able to import the highlighted table AND all the relevant connected tables also!
Now we’d like to import the Person.Address table. It can be clearly seen in the figure below. If we need the connected tables also then we have to check the ‘Select related tables’ button.
This button located at the bottom of the window.
We have highlighted the data. Now we’ll import with the help of the external data source. By clicking on the Load button, we’ll transfer the data into Excel.
If we pay more attention to the button we can see it has a drop-down list. Let’s try out how to the ‘Load to’ option work!
Take a closer look at the Import Data window! We can see we have more options for data import. In this chapter, we’ll show you how to create a data structure.
The Import Data Window: Select views
Using the import data window, we can choose a view and layout for our project.
- Table: we can display data in tabular form
- Pivot Table Report: we don’t have to specifically explain the advantages of Pivot tables. Choose this if you’d like to group the data in a short time. If you’d like to apply the drill-down method, this is the best choice!
- Pivot Chart: using the columns of the Pivot table we can make a chart
- Only create a connection: if we don’t need the data right away, then we only create the connection between the SQL database and the Excel worksheet.
- Add this data to Data Model: in this case, the highlighted data will upload into the Power Pivot Data Model.
Explanation of Get and Transform function: If you use this function in Excel, you create a query in your workbook. This query enables you to manage data using various external data sources. If you want to refresh your data and keep your dashboard always on use, the built-in Data Model in Excel. Most effective to use the tabular format. Let’s choose this option!
How to manage and refresh External Data Connections?
We are already done with an important step. Now we have our data in Excel. What happens when data changes in the source database? How can we keep data up to date? We’ll find the answer in this chapter.
Check the ‘Queries and Connections’ window! It’s located on the right-hand side. You can see the imported records and all related records of the Person.Address table. Every single database table is on a separate Worksheet.
Refreshing Data Connections manually
Usually, we use two methods to refresh existing data connections. We can find this menu on the ribbon in the Table Tools tab. Click on Refresh / Refresh all icons to refresh the data connections manually.
It is important to note that there’s a big difference between the two. We use Refresh when we only want to refresh the data of the given Worksheet. For example, a specific database table. When we use the Refresh all button than every table structure, and all the Worksheets are being refreshed.
Using auto-refresh control to Scheduled Data Import
We also have the option to automate this process. How to schedule refreshing? Set a 10-minute interval and forget about the manual work.
Click on the ‘Existing connections’ icon on the Data tab.
We’ll see the following window appear. We see the relevant connections of the Workbook in one list. After a right-click, you can choose the ‘Edit Connection Properties’ button.
The Query Properties Window
You can find all the adjustments on the ‘Usage’ tab. Let us set the parameters of the query! Here are the details below:
The Refresh Control section informs us of the actual setups.
Enable Background refresh: This is a query running on the background. We’ll be informed about the status of the process. Check the marked section on the picture. The extraction is in progress.
- Refresh every x minutes: We can set the intervals of the import. Our data set will be updated using external data sources.
- Refresh Data when opening file: This setting is automatically refreshing the data connection in the moment Excel Workbook is opened. Usually, we shouldn’t use this option. If a large query starts up, it can even freeze up Excel. We’re better off if we refresh data after we’ve opened the Workbook.
- Refresh This Connection on Refresh All: We already talked about this, but it is very important to let’s see it again! If we allow this option, all imported tables will refresh. (If not, then only the data on the active Worksheet will refresh.)
We can find more information about the data connection on the Definition tab. Add a specific name for the query. In the description field, we can give notes also.
Now let us see further options:
- Connection Type: The type of the connection, in our case, it is OLE DB Query.
- Connection file: We can save the connection file to any place we want to. Also, we can browse an already created connection file.
- Connection String: It describes how and what we’ll import using external data sources.
- Command type: In our example, this is SQL.
- Command text: If you want, you can write your own SQL query. In this case, we import all records from the Person.Address table.
Importing CSV files to Excel – External Data Source Example
In this chapter, we’ll import into Excel a CSV file containing sales records. This will be a lot easier than the SQL data import.
Choose the ‘From text / CSV’ option on the Data tab from the ‘Get and Transform Data’ group.
Locate the file and click Import.
Before the data import, a preview window will appear. In the ‘File Origin’ section you can set the code page. In the ‘Delimiter’ menu you can give the character that will separate the columns of the imported table. The default ‘comma’ setting is the right one for us.
On the bottom right-hand corner, there’s the usual ‘Load / Load To’ button. We have talked about this before. The Tabular format is perfect for us now.
Click on the Load button.
The file containing 100 sales records is in Excel in a matter of moments with the use of external data sources!
Import Data from Website into Excel using XML files
We have two choices to import from XML to Excel. Either we use a specific URL address, or we browse in the file itself.
Using Web query
In this example, we’ll import into Excel a short restaurant menu using a web query.
Type the URL and click OK.
On the left-hand side of the Navigator Window, highlight the table we would like to import. After the highlight on the right-hand side, we’ll see a preview.
Click on the Load button.
With the help of the external data source, the data of the table will be on the given Worksheet.
Import a local XML file
On the Data Tab choose Get Data > From File > From XML menu.
Click on the Import button and open the XML file.
From here on, the steps completely the same as described in the web query tutorial.
In today’s article, we endeavored to show you the most important external data sources. It is clear that we can choose multiple formats for the data needed to be imported. Last but not least, we talked about the management of data connections. Excel is excellently capable of making charts, reports, and various data visualizations. We should leave the managing of large data to external data sources.