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:
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.
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.
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:
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.
In the ‘data ribbon’ select the ‘From other sources’ button and click the ‘From Microsoft Query’ option:
Then click on the ‘
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”.
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.
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.
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.
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':
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.
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.
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.