SQL in Excel
Connecting to an external database in Excel is often necessary to process large data sets. In Excel 2016, there are several techniques available that allow external data import. In this post, I will describe one nifty way to connect your workbook to an external database and use SQL to produce valuable tables.
The simple example below can be applied to accomplish some amazing tasks. The freedom to run SQL queries in Excel allows users to analyze millions and millions of data points at a time. I often use this technique to “pre-process” enormous data tables before preparing final reporting products.
***Hover your mouse over the images below to enlarge (supported by most browsers)
In a fresh workbook, activate the data tab on the navigation bar. In the “get data” window, select “from other sources” and choose the “from SQL server” option.
Enter the server name and your authorized login credentials.
Here you will see a list of tables and views available for query in your database. Since our goal here is to use SQL to pull information we don’t need to worry about selecting the correct tables at this point.
For now, select any table and click next.
Here you can add a recognizable nickname and description of the connection. Adding a few descriptive notes may come in handy when working on this connection in the future.
Also, there is an option here to save your login credentials. This option is convenient but is a security risk when exercised on a shared file.
Next, you have a few options of how to import the data. For now, select the Only Create Connection option.
The main reason to stick with this option is that we have yet to add our SQL query statement. Other options would cause irrelevant information to display.
Now that our connection is created we can apply our SQL statement.
In the Data tab, select Connections.
In the Workbook Connections window, highlight the new connection and select properties.
Go to the Definition tab in the Connection Properties window. Change the Command type to SQL. Cut and paste your SQL statement into the Command text box and click OK.
The results of our query can be utilized in a variety of ways. For this exercise, I will assume that our goal is to display the output of the query in a simple table.
In the Data tab, select Existing Connections found in the External Data window.
Select the applicable connection in the Existing Connections window and click Open.
In the import Data table, select the Table option and indicate where to insert the table on your workbook.
After you click OK, your table will appear in the location you selected.