<

SQL Query to Excel

Published on
8,270 Points
1,970 Views
3 Endorsements
Last Modified:
Approved
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.
Usage
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.
3
Author:x-men
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.