One of the things a DBA does is run some sort of query in Microsoft SQL Server Management Studio (SSMS) and export the results for someone else to use in Microsoft Excel. So let us look into two of the ways to do just that.

In both cases, we will be executing a select statement in SSMS, and to keep things even for both examples we will use the same simple select statement “Select * from upr00100”.

Option 1

Have SSMS set to display results in Grid mode and execute the query. Here is a screen shot of the results:

image

Once you have this, open Microsoft Excel to a new blank worksheet, like so.

clip_image003

Then in SSMS first left click on the top left blank box just above the record count to select the entire grid, then right click on the same box. Notice the one that I circled in Red in the following image. Then select “Copy with Headers”, of course, I am assuming that you do want the headers in your Excel Spreadsheet, if not then just select “Copy”.

clip_image004

Then back over in Excel select Cell A1, right click and paste. At this point, other than a few minor Excel format options, that is it.

 

Common Excel Format Items

The most common of the Excel format options are the width of the cell and Date Time fields. See the following image.

clip_image005

Notice how column “H” does not look like a Social Security Number and Column “I” does not look like a Birth date.

To fix Column “H” all you have to do is the increase the width of the column. To fix Column “I” just format the column as a date field and you get the nice looking Social Security Number and Birth Date as seen below.

clip_image006

Now you are ready to send the file on to your users.

 

Option 2

The other option is to send the results directly to a CSV file that Excel can easily open just by clicking on the file name of the CSV file. Now the easiest way for me, is to have the Output format for Text Results set to “Comma delimited”. The menu path is Query >> Query Options then click on “Text” under the Results leaf and set the output format, as shown in the following image.

clip_image007

Set SSMS to send results to file and then execute the query. You should have a dialog box similar to the follow show up.

clip_image008

On this dialog box the first thing to do is to change the “Save as type” to “All Files (*.*).

Then type in a name for the file, and be sure to use the extension of “.CSV”.

Once this is done click on the small down arrow on the right side of the “Save” button select the “Save with Encoding” option.

clip_image009

For “Available encodings”, the “UTF8” option works with the newer version of Excel, but I must admit that I usually select “ANSI” that way I do not have to worry about what version of Excel or whatever program the end use actually uses, as most all programs understand the ANSI format.

clip_image010

Then just click the “OK” Button and you are almost finished.

The only thing left to do is to open the file in Excel and do the same column format things for columns “H” and “I” that we did we did in the first example. See “Common Excel Format Items” above.

 

Till later,