Hello
I am not able to find a way to extract the results of query in csv. I tried the Results to File but the file produced is not csv. I also tried the Results to Text and the format is not csv-like.
Results to Grid is the only option that works, with some editing, but I am worried that there will be a problem to copy large data through the clipboard.
Any idea?
Thanks!
You can run the same query from the command line with the sqlcmd utility. The bcp utility can also output CSV, assuming you have a short query and use the "queryout" option.
You can configure SSMS to output query results to text as comma-separated values:
Or you can use Powershell:
PS> Invoke-Sqlcmd -ServerInstance YourServerName -Database YourDatabase -Query "YourQueryHere" | Export-Csv -Path C:\Temp\output_file.csv
who/what will be consuming this csv data? there must be a reason you are exporting it as such.
I suppose there will be an issue if I set SSMS to export in comma separated values text, in case a value has itself commas? In that case it will need to include the value in '' which will again complicate things in case values already have ' characters. That's from experience with other csv.
The purpose is to load it to Excel basically, without having to do any conversion.
Is this a process that will be repeated on a scheduled/regular basis for that code? Or are you just looking for an adhoc way of exporting the results to Excel?
For automated solutions - there are several methods available, depending on how you want to deliver the data to the end user.
Use sp_send_dbmail - you can include the results of a query as an attachment and as a comma-separate values file. Note: with this solution you need to adjust the first column of the first row to include the hdr key value so the file can be opened directly from email. You may also need to quote specific columns if they may contain comma's in the data - or you can choose to use a different delimiter.
Use SSRS report - you can build and deploy an SSRS report and schedule the report to execute at a specific time, outputting the report to email or file share in Excel format.
Use SSIS to build the Excel report - however, this requires a 'template' Excel file to be available. This template would have the defined columns already created with any filters or formatting pre-defined.
Use OPENROWSET to open an existing Excel file (template) and populate/update the data.
Use OLE Automation to create an Excel file and populate the data.
For manual options - there are several available. Change SSMS as outlined before or:
Save as CSV directly from the grid - just select all rows, right-click and save as...however, you have to manage any quoting if needed and Excel may not display columns correctly. For example - any columns that are numeric with leading zeroes - the leading zeroes will be dropped on display (or if you save as an Excel file). Here - you can modify the format of the cells to text and re-paste the contents...
Copy/Paste from the grid - select all rows, right-click and copy with headers. Paste directly into Excel - with this method you will need to adjust for quotes and cell formatting the same way.
Use a third-party tool...I utilize a tool called SQL Assistant that does quite a lot more than this...but included in that is an option to export directly to Excel. Select all in the grid - right-click - select to SQL Assistant option to export to Excel and I get a formatted ready to use Excel file.
Well - not quite exhaustive if you just added a few more options 
But...there are so many different ways to accomplish this task and it really does come down to which one solves your particular issue, is something you can easily setup, configure, manage, use and easily deploy (if needed).
More and more I am starting to despise ssis
Why?
If you believe the tool is somehow designed for DBA's - I could see that position. It isn't really a DBA tool - rather it is a programming tool for BI on the same order as Informatica, Ensemble, SeeBeyond, etc... (although not as feature rich as those tools).
because I am starting to see the light, I have used SSIS since I was in diapers. I can do things much easier using python/powershell
SSIS to me personally now feels bloated after using this scripting languages.
Giving back to our wonderful community, I found out that if you right click on the results to grid, there is an option for a csv export by selecting 'save results as'!
However, there is a problem, it cannot save the headers with that option which is a major inconvenience.
I just tested that option - I got the column headers in my results...this can be controlled by an option in SSMS:
This method does have an issue though...if you have embedded commas in the data it does not quote that data.
For ad-hoc exports to Excel - using copy/paste (with column headers) is the easiest. It does require formatting the results after pasting - but once the columns are formatted you can paste again to get the correct results.
or you can do it with powershell, not manual stuff and no SSMS involved
I know you like powershell - I like it too and use it quite often - but how can you write a query, test the results of that query and output the data in CSV all in Powershell?
Or - why would I open SSMS and create a query, test it - validate it - then open Powershell ISE - copy/paste my query into a Powershell script to execute it and output to CSV?
Just easier to select all - right-click and copy with headers - paste in Excel.
If you are setting up a repeatable process - then Powershell is one of the many possible solutions. 
Agreed
you can get as fancy as you want, a whole ecosystem of powershell driven data exports, dynamic etc.
Instead of an embedded sql query you can opt for stored procedures ,views etc.
$SQLServer = "localhost"
$SQLDBName = "sqlteam"
$delimiter = ","
#SQL Query
$SqlQuery = "select p.id, name From products_logger l join products p on l.id = p.id where isexported = 0;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
#Creating Dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path "C:\_work\TeachYourself\PowerShell\export.csv" -NoTypeInformation