SQL Server SSMS – Save Results As CSV for Excel

excelexportsql serverssms

Using the Save Results As option, shown below.
CSV looks good!

But when Excel opens/imports it:

  • Long strings are mangled as numbers (if the data is largely numeric)
  • Dates are not detected properly
  • My users beef about all this

How do I get a query to a clean excel file (a real xls/xlsx) without dancing? (We like everything to actually in the SQL file itself… I wish we could do "SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS …")

enter image description here

Best Answer

First, make sure you've got the query options set up the way you want.

Go to the Query menu, and choose Query Options:

Query Options dialog

The first of the two highlighted options tells SSMS to include the column headers in your CSV file. The second tells SSMS to put single quotes around columns that include a comma.

I suspect you've already done this, but thought I should mention it for completeness.

If this isn't sufficient to get your data into a format Excel will correctly interpret, my next step might be (as noted in the comments) to use the Import/Export Wizard to generate a SSIS package that does this. However, the wizard does not always get the quoting correct - in particular, it can put things in double quotes, but won't "double up" the double quotes embedded in the string.

Generally at that point, I resort to brute force.

I create a modified query that:

  • Replaces double quotes in the data (") with double double quotes ("");
  • Converts numeric/date values to strings, and
  • Puts all data into double quotes, and (finally)
  • Outputs all the columns concatenated together, separated by commas.

So, instead of 

 ID | First Name | Last Name |      Address      
----+------------+-----------+-------------------
  1 |  John "JT" |   Smith   |  123 Wayne's Way

I generate:

One_Big_Column
------------------------------------------------
"1","John ""JT""","Smith","123 Wayne's Way"

If I'm going to use the query regularly, it'll include a sortOrder column (set to 1), and be UNION ALLed with a SELECT that creates the header row. I then wrap the UNION ALLed queries (making them a subquery), so I can select just One_Big_Column, and sort by sortOrder.

SELECT One_Big_Column
  FROM (
        SELECT '"Id","First Name","Last Name","Address"' as One_Big_Column
              ,0 as sortOrder
        UNION ALL
        SELECT '"' + CAST(Id as varchar(30)) + '","'
                    + REPLACE(firstname, '"','""') + '","'
                    + REPLACE(lastname, '"','""') + '","'
                    + REPLACE(address, '"','""') + '"'
              ,1 AS sortOrder
          FROM myTable
       ) sq
 ORDER BY sortOrder
;

You can see this run here.

Note: If you're formatting the CSV manually, you'll probably want both of the check boxes noted above turned off.

Related Question