One of the regular tasks i need to do for our BA team is pull out some data from a database and let them see it in a readable format. The easiest way to do that is just call the query in SSMS. Right click on the results Grid, Copy with Header and Paste it into Excel.  Sadly since SQL Server 2008 r2 under some circumstances then when you paste  into Excel the results are a mess and unreadable..

There is a connect for the problem here: https://connect.microsoft.com/SQLServer/feedback/details/735714

This post is alternate option should you need to get either a SQL Database Table or a Custom Query into Excel.

 

Open Excel -?> Data Ribbon -> Get External Data -> From Other Sources -> SQL Server

ExcelExternalDataSQLServer1

This opens the Data Connection Wizard, Connect to SQL instance

ExcelExternalDataSQLServer2

Select the database you want to query.  Note we cant start with a SQL Query so i just select a table i know has small data set.

ExcelExternalDataSQLServer3

Except the save connection

ExcelExternalDataSQLServer4

Import Data to a Table (Note that you only get the options for PivotTable / Chart and Power View in Office 2013)

ExcelExternalDataSQLServer5

This will populate the Excel sheet with Database Table. If you want to do a SQL Query not just a SELECT * then you need to go back to the Data Ribbon, And select Properties.

ExcelExternalDataSQLServer6

Select the Icon next to Connection Name.

ExcelExternalDataSQLServer7

Here we can set the Connection Type. Below is how it looks based on select every column and row from a Table.

ExcelExternalDataSQLServer8

To enter out Custom Query change the “Connection Type” to SQL, Delete everything in “Command Text” and enter your Query.

ExcelExternalDataSQLServer9

When you select OK you will get a message saying the New results dont match the current columns and do you want to refresh them, Select Yes.

ExcelExternalDataSQLServer10

You will now have your SQL Query result set populating the Excel Table.

ExcelExternalDataSQLServer11

Advertisements