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 ALL
ed with a SELECT
that creates the header row. I then wrap the UNION ALL
ed 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.