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 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 Excel dashboard and you have to work with 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 web query also.
Connect to external data sources in Excel
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 up 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? Than 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 on the figure below. If we need the connected tables also, than 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 to data import. In this chapter we’ll show you how to create the 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 connection: if we don’t need the data right away than 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 excel 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 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 that 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, than 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 you 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.
SLocate the file and click Import.
Before the data import a preview window will be appeared. 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 source!
Import data from website into Excel using XML files
We have two choices to import form 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 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 format for the data needed to be imported. Last but not least we talked about the management of data connections. Excel is excellently capable to make charts, reports and varied data visualizations. We should leave the managing of large data to the external data sources.