Full Power of SQL with Your Excel Spreadsheets!

In a previous article we discussed how importing SQL Server data into Excel and using it can be a very useful and powerful thing.

We also showed how to connect directly to an SQL Server database and import the entire contents of a specific table within that database.

While this is an important capability of Excel, it does have a few practical drawbacks:

1. You can only import the entire contents of a table (that could contain many, many records)
2. You can’t summarize the data from your table
3. You can’t import data that is found in multiple tables

This might seem like a bit of a problem, but worry not!

In today’s article we will introduce you to a tool called ‘Microsoft query’ that allows you to do all of the above, and more.

What is Microsoft Query?

Microsoft Query is an optional Microsoft Office component that you can install and use to retrieve data. Unlike the method we used previously, with MS Query you have far more control over the data that we get back and how it is presented.

Getting ready to use Microsoft Query

As we showed in the previous article, before attempting to import data you need to contact your DBA/System administrator and find out the following:

1. The name of the Database Server (The machine that runs the database software)
2. The name of the database your data is stored in
3. Whether your windows credentials are enough to access that server or a username/password for your database

Microsoft Query in Action

Let’s imagine we are running a scientific research project and are gathering information from our medical research database. Rather than download the entire massive data set into Excel, we want to extract just the names that match a specific criteria.

We could import the entire patient table into our spreadsheet and then use Excel filters to filter down the results. But the faster and more efficient way to do this would be to create a ‘Query’ that imports only the subset of records that we want into our Excel sheet.

First we need to turn our SQL server database into a ‘data source’ for the ‘Microsoft Query’ tool.

Connecting ‘Microsoft Query’ to an SQL Server database

In the ‘data ribbon’ select the ‘From other sources’ button and click the ‘From Microsoft Query’ option:

Then click on the ‘‘ option in the Databases tab

Then you will be asked to name your data source. It’s a good idea to pick a good, meaningful name that you will recognize next time you see it.

You will also be required to select the driver that’s suitable for your database. In our case it’s either the SQL Server driver or the ‘SQL server native client 10.0′ driver. If you are not sure you can try them both and see which works.

Then press the connect button:

Then, enter the database server name you got from your system admin:

After that, press the ‘options’ button as shown below:

Then enter your ‘Login ID’ and password that you got from your administrator or check the ‘Use trusted connection’ box if you were set up to be a trusted connection.

Most importantly, remember to select the correct database from the database list below. If you forget to do that you’ll be presented with all sorts of confusing data later on and you won’t know why.

Next, press the ‘OK’ Button

To finish your setup, press ‘OK’ in the ‘Create New Data Source’ window

At this point you have connected the Microsoft Query tool to your database. From now on you can easily make your data extraction queries and import the data into Excel.

Now that is done, let’s see how we get some data!

Start by clicking the data source that you named earlier. We called ours “VersaForm”.

Extracting Data from SQL Server with MS Query

Once you have clicked on the data source you will be asked for a password again, plus a small ‘connection to data source window’ will appear. When you enter the LoginID/Password you’ll be connected to the Microsoft Query tool (this stage is skipped if you are a trusted connection).

In the Query Wizard you’ll see a list of tables contained in your database. At first this might be confusing because there will in all probability be a lot of tables, but in time after you’ve looked through the database several times, you’ll begin to see what information is located in which table.

If all else fails and you can’t find the table that contains the information you need, just ask your DBA – they should know.

Selecting Your SQL Data

After looking through the list of tables, find the table that you need. In our case it is ‘Patients’.

When you click on the plus sign on the left side of the table, the tree will ‘open’ to show the list of fields in the table.

When you double click the field name or select the field and click the ‘>’ button, the field is added to your query.

You should add all the fields you need from the table to your query and then click the next button. In our case I’ve added the ‘first_name’, ‘last_name’, etc.

Setting filters for your Query

Now you can add filters to the query to retrieve only specific records from the table.

For instance, you can choose to retrieve only records of male patients (as shown below)

Note that once we set a filter for the sex field, it is marked in bold. If you want to change the filter for a certain field, double click it and you’ll see the filter settings for that field and will be able to edit them.

When you are done, click next…

You can also define the sorting order of the records in your query, but in our case it is not relevant so I just pressed the next button again.

Saving Your Query

While it is possible to save the query and return the data to Excel directly from this form, I like to see the query and the resulting data set (list of records) in the standard Microsoft Query interface before moving on.

We will be using this interface quite extensively in future articles so it’s not a bad idea to get acquainted with it right now.

So, select the ‘View data or edit query in Microsoft Query’ option and click the finish button.

You will arrive to the ‘Microsoft Query’ main form.

You’ll see it’s divided into 3 ‘strips’:

1. The upper strip contains the list of tables used in the query. In our case, we are only using data from one table
2. The middle strip shows the filtering and sorting criteria. If we defined filters or sorting order in the wizard, it will show here
3. The lower strip shows the list of records that are returned from the query. This allows you to see if your query returns the desired output, and change it if you are not getting the list of records you wanted

At this point we will not make any changes to the query. So click the ‘save’ button to save the query.

Again, try to give it a meaningful name so you can know in future what this query is about. Next, click the save button.

Once the query is saved we can finally import the data into Excel by clicking on the ‘return data’ button.

Select where and how you want the query data to be inserted into your excel sheet.

And there we have it – query data in Excel!

Note that this table is connected to the query. This means that if you add more records then you can refresh the table and get the new data automatically into your sheet.

Summary

Microsoft Query is a very powerful tool that allows you to get any data and any possible aggregation of data from your SQL Server database.

In this article we’ve covered how to set it up as a data source to Excel and how to connect it to your SQL Server database. We also used Microsoft query to create a simple query.

In the following article in the SQL article series we will see how to combine data from different database tables and how to aggregate information on the query level using “sum”, “count”, etc, giving you unprecedented analytical power in Excel.

P.S.

Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.