Load SQL Server Data into Excel
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
This opens the Data Connection Wizard, Connect to SQL instance
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.
Except the save connection
Import Data to a Table (Note that you only get the options for PivotTable / Chart and Power View in Office 2013)
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.
Select the Icon next to Connection Name.
Here we can set the Connection Type. Below is how it looks based on select every column and row from a Table.
To enter out Custom Query change the “Connection Type” to SQL, Delete everything in “Command Text” and enter your Query.
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.
You will now have your SQL Query result set populating the Excel Table.