SQL Server: SSMS "Save Results As" creates CSV that Excel mangles
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.