By: Douglas Correa | Updated: 2016-11-23 | Comments (1) | Related: > Microsoft Excel Integration
Problem
Excel is an easy way to retrieve external data from SQL Server or other database platforms. We are used to retrieving data from a table or a view, but sometimes we need to filter the data using parameters like using a WHERE clause in a SQL query. The problem is that you need to change the SQL statement every time you need a different result or if the table has so many records you can't load everything into Excel at once.
Solution
You can use Microsoft Query to retrieve data from external sources, you don't have to retype the query and you can use Excel cells to filter the data from the database.
To import external data into Excel with Microsoft Query, follow the steps below.
On the DATA tab, click From Other Sources and then click From Microsoft Query.
Specify a data source for a database, text file or Excel workbook.
Follow the steps below to create a new data source.
Once you have configured the database server options, you need to select the table where you will get the data. The query wizard helps to create a simple SQL query to retrieve all data from that table. After finishing the query wizard you will select the option to edit the query in Microsoft Query.
The window after those steps is similar to the MS Access Query Wizard. In the previous steps we didn't choose to filter data and now you will change the SQL statement typing the WHERE clause with "?" as the parameter value.
Above you defined which field or fields will work to filter your data. Now choose how the parameter value is obtained and in this case you select to get the value from the following cell.
With two parameters in this example, the cells for the date can be seen below and after changing the value you will get different results.
Conclusion
Instead retrieving the entire dataset from the table and then filtering in Excel, you can use filters to retrieve only the necessary data using a SQL query.
Next Steps
- Read these other Excel Integration Tips
About the author
Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-11-23
