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.