SQL Query to Excel

Published on
8,104 Points
3 Endorsements
Last Modified:
Why run SQL server queries on SQL Server Management Studio, only to import the results to a Excel workbook so that they may be "worked" to make those beautiful reports that the boss likes so much?

Here's how to access the data, directly from the SQL Server source, and even, keep it synchronized.

We start by creating the data source. For that, access the menu:
 "Data" -> "From Other Sources" -> "From SQL Server"

A window with presenting the Data Connection wizard will open, and we need to insert:
Data Connection Wizard 3
 1. Server name: Is the SQL server instance instance name (Hostname\Instance Name).
 2. Log on credentials: Are the credentials you use to connect to your SQL Server Instance. Either use Windows Authentication, and it will assume the current logged on user, or use SQL Authentication and fill out the User Name and Password for the SQL Login.

On the "Next" window, select the data base from witch we want to retrieve the data, and the table or view.
Data connection Wizard 2Again, Next, chose a file name for the .odc file that stores the connection info and click Finish.

A New window will appear asking to define how the data will be displayed by excel, choose Table, you can later create a PowerPivot from this table ,... and where to place the data, a usual excel range, or cell.

After making your selection, click the "Properties" button.
Import Data
Here, use the "Usage" tab to define, in this case that we're querying a SQL DataBase, the refresh of the data.
On the "Definition" tab:
change the "Command type" from Table to SQL
place your SQL Server query on the "Command text" area.
SQL Query

We are now presented with a Table (considering we didn't choose the PowerPivot), with the SQL Server table headers on the first row, of the selected range, and the data returned from the SQL Server Query.

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month