Using Microsoft Query in Excel to Retreive SQL Server Data


By:   |   Updated: 2016-11-23   |   Comments   |   Related: 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 Updated: 2016-11-23


get scripts

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





Comments For This Article





download





Recommended Reading

Insert, Update or Delete Data in SQL Server from Excel

Passing Dynamic Query Values from Excel to SQL Server

How to Create a Burndown Chart in Excel from Scratch

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

How to Export Data from SQL Server to Excel














get free sql tips
agree to terms