'SSMS Export Query Results to Excel or CSV

I am trying to export the results of a query to CSV and then ultimately Excel.

My issue is, one of my columns has commas in it and the commas interrupt Excel parsing the CSV in the correct places.

I don't have write privileges to the db, so dropping the query results into a table and then exporting that table to excel is not an option.

Is there a way to export to excel, or at least CSV with a column that has commas in it?

sqlsql-serverssms-2014


Solution 1:[1]

1 Right click your DB in object explorer window, go to Tasks -> Export Data

2 Pick Sql Server native client as your source, put in your connection parameters

3 Pick Excel as your destination

4 On the next screen paste your select query

5 go through the remaining screens making sure things look correct

6 profit

Solution 2:[2]

The way I have dealt with this is to use a tab separated file instead (assuming we don't have tabs in the data)

Step one to export tsv

Selecting the tsv option

Another way to try is to copy & paste the table directly from the query results in SSMS into Excel and then saving the file from there.

Solution 3:[3]

If you can query the database, you can use something like this to retrieve the data without columns.

DECLARE @String varchar(25) = 'Comman delimited, value.'

SELECT REPLACE(@String, ',', ' ') AS ScrubbedValue

Solution 4:[4]

Use a replace function to replace commas with a pipe or other character. In Excel, the pipe can be reverted to a comma using Excel's Replace, if required.

select
 Date     = a.Date_Time,
 Action   = Type,
 Username = a.username,
 Desc     = replace( description,',','|'),  -- replace pesky commas
from
 dbo.AuditLog a
where
 a.Date_Time > DATEADD( DAY, -1, getdate())

Solution 5:[5]

You can use TOAD for SQL Server. It has export as Excel. This is better then csv because it preserves the data type. This works even in trial mode and after the trial expired. I use Toad just for the export to excel. enter image description here

Another option is to use "Azure Data Studio". It's free, and has save as excel option.

enter image description here

I use SSMS as my main tool, but when I need to export something to excel, I copy and paste the query into azure data studio.

Solution 6:[6]

Commas

If you use the solution by @MikaelEliasson, you can actually save to CSV without any issues - SSMS will automatically surround any data containing commas with double quotes. Just right-click, Save Results As... anywhere on the data grid results.

Step one to export tsv

Header row

To include the header row, navigate to Tools ? Options ? Query Results ? Results to Grid and ensure "Include column headers when copying or saving the results" is ticked.

Leading zeroes

If any fields have leading zeroes, Excel will truncate them (even if you convert to text). To avoid this, use "=""MyLeadingZeroField""", so:

 SELECT CONCAT('"=""', MyLeadingZeroField, '"""')

which will then display correctly once opened in Excel.

If you try the solution of a leading single quote or surrounding by single quotes this doesn't really work as these will display in Excel.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vlad
Solution 2 Mikael Eliasson
Solution 3 user7593937
Solution 4 jim birch
Solution 5
Solution 6 SharpC

Related Questions