Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Using Microsoft Query in Excel to Retreive SQL Server Data


By:   |   Read Comments   |   Related Tips: More > 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.

On the DATA tab in Excel, click From Other Sources and then click From Microsoft Query.

Specify a data source for a database, text file or Excel workbook.

Specify a data source for a database, text file or Excel workbook.

Follow the steps below to create a new data source.

Follow the steps below to create a new data source.

Create a new SQL Server login.

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 query wizard helps to create a simple SQL query to retrieve all data from that table

Query Wizard - Filter Data in Microsoft Excel

Query Wizard - Sort Order in Microsoft Excel

Query Wizard - Finsih interface in Microsoft Excel

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.

Microsoft Query interface with the query and data

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.

Choose how the parameter value is obtained in Microsoft Excel

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.

Two parameters in Microsoft Excel

Data updates in Microsoft Excel as the parameters are updated

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools