SQL Server: SSMS "Save Results As" creates CSV that Excel mangles

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 ...")

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

Go to the Query menu, and choose Query Options:

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.

StackExchange Database Administrators Q#190898, answer score: 5