Import Data From Database Using Native SQL Query in Microsoft Excel 2013

Introduction

This article shows the powerful features of Excel 2013 to import data from a database. This is a new feature introduced in Excel 2013 to retrieve data from many of the sources. I like the most powerful BI feature of Excel that contains a Get External Data menu option for linking the workbook to an OData data feed. Knowing that Dynamics CRM 2011 and 2013 both provide an OData endpoint, this would seem like a great option for retrieving data from a CRM Online based system for reporting purposes, since direct database connections aren't available in the cloud.

You can also import data from all the databases supported by Power Query, except Microsoft Access, by directly running native database queries against the database using Power Query instead of having to build your query using a Power Query interface. This enables you to use an existing query that returns the required data results without having to rebuild the query using the Power Query interface. This feature is especially useful for importing data using complex queries that you might not want to or know how to rebuild using the Power Query interface.

Power Query enables you to specify your native database query in the SQL Statement box while connecting to a database. In this example, we will import data from a SQL Server database using the native database query. The procedure is similar for the rest of the databases supported by Power Query.

Connect to a SQL Server database using Power Query. In Excel, on the Data tab, click "From SQL Server" > "From SQL Server Database".



In the Microsoft SQL Database dialog box specify the server and database to connect to from where you want to import data using a native database query.

Expand the SQL Statement to display a box where you can type or copy your native database query. After specifying the query, click "OK".



Select the database from the DataConnectionWizard. You can select the option "Enable Selection of Multiple" as per your requirements. I selected Single table for demo purposes.



Then write the native query to retrieve the data from the database.



Select the preceding marked option if you need to refresh or reload the data from the database. Then click on "Finish" and you will be able to see your data in Excel.

Result



Now use some of the powerful features of Excel to manipulate your data.

For instance “Remove Duplicates” as in the following:



Result



Summary

Using this feature you can import the data from various data sources like the Azure marketplace, XML and OData. It helps to see the production database in real time where in general we don't have control of the database server.

Happy Coding!