Welcome to part two of this two part series covering how to use Excel with a MySQL database. Part one of this series can be found here:
Connecting Excel to MySQL – Part I
In this post we are going to use a tool that is built into Excel. It’s called Microsoft Query Wizard and it will allow you to customize the data that you retrieve from any MySQL database. We don’t always need an exact replica of the Database in our spreadsheet. Sometimes we need specific pieces of data and that’s what the Microsoft Query Manager does best.
In this post we’re going to retrieve a customer id, a payment id and a payment amount from a specific table. We will sort our results by customer id and then payment id. Our restrictions will be payments must be greater than $8.00.
Preparation
As always, backup your database data before attempting to use any new tool, application or query. For this post we are working with a free example database provided by MySQL. The database is called Sakila and can be downloaded as a zip file here:
http://downloads.mysql.com/docs/sakila-db.zip.
If you prefer a tarbal, you can find that here:
http://downloads.mysql.com/docs/sakila-db.tar.gz.
If you would like to read about the Sakila example database, please visit
http://dev.mysql.com/doc/sakila/en/sakila.html.
It’s always a good idea, when you are working with databases, to confirm that what ever process you are performing works well with a large data set. Of course everything works fast with a small table, but what about a table that has several thousand rows?
In the Sakila database, the “payment” table contains over 16,000 rows. We chose this table in order to demonstrate how well the Query Manager handles large data sets.
Let’s Begin
Let’s start by opening Excel and creating a connection to the Sakila database. If you need a refresher on creating and using connections, please visit part one of this series, the link is in the introduction paragraph above. Once you have created your connection the next thing is to click on the Data tab and then From Other Sources and then From Microsoft Query. Here’s an image showing the click path:
The next step is to select the Sakila connection under the Databases tab in the Choose Data Source window. Ours looks something like this:
Next, we select the table and columns we want to work with. We’re going to choose the payment table:
We’re also going to choose all of the columns in the payment table:
The next step is where all of the fun begins. As stated previously, we only want records where the amount paid is greater than $8.00. In the next step of the Query Wizard, we can setup our “where” clause or “Filter” for those of you that are new to databases. Ours looks something like this:
In the next step of the Query Wizard, we can choose how we want our data sorted. We want our data sorted by customer id and then by payment id. We are sorting the data in this fashion so we can see which customers (customer id) have had payments over $8.00. We are also sorting by payment id in case a customer has more than one payment over $8.00. Our sort order criteria looks like this:
The final step of the Query Wizard is to decide what to do with our new query. For this post, we’re going to return the data to Excel. Feel free to save your query for use in the future. If you choose to save your query, you will see the save dialogue. Once you have saved your query, you will be returned to the final step in the wizard. Ours looks something like this:
To complete the setup of gathering our data, click on Finish. If all goes well, you should see the import data prompt. We are going to use the default selections for this post. Our import data prompt looks like this:
Finally, we have our query results displayed in our spreadsheet. Notice, our data is already sorted by customer id and then payment id just like we specified in our Query Wizard settings. Also notice, customer id #3 had 4 payments over $8.00. Our final spreadsheet looks like this:
Wrap Up
In this post we covered how to use the Query Wizard to build a custom query for the Sakila database. Using the Query Wizard we were able to trim over 16,000 rows down to 858 in our result set. All within the blink of any eye.
Using this method is much quicker and more efficient than the old “save the spreadsheet, sort the columns, delete what I don’t need and resave the spreadsheet” technique.
The purpose of this post was to present the basics of the Query Wizard. The Query Wizard is a very powerful tool and has several advanced features that we did not cover in this post. It’s up to you to experiment with your data and the Query Wizard.
It can be a great time saver and a simple tool you can use to get the specific data you need and impress your co-workers.