SSMS Export Query Results to Excel or CSV

Question

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?

You should be able to create temp tables. All the user needs is to be able to connect to the database in order to create those. — NotMe, Aug 23 '17 at 21:57

score 10 · Answer 1 · answered Aug 24 '17 at 00:24

10

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

answered Aug 24 '17 at 00:24

Vlad

793
6
15

1

This is great, although it wouldn't allow a query with temp tables for me – JonoB Feb 20 '20 at 13:19

score 8 · Answer 2 · answered Feb 01 '19 at 03:03

8

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

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.

answered Feb 01 '19 at 03:03

Mikael Eliasson

5,157
23
27

user890332 · Answer 3 · 2022-04-20T18:26:58.077

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.

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

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.

score 0 · Answer 4 · answered Aug 23 '17 at 21:44

0

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

answered Aug 23 '17 at 21:44

user7593937

545
1
5
16

score 0 · Answer 5 · answered May 01 '18 at 00:30

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())

score 0 · Answer 6 · answered May 25 '22 at 08:11

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.

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.

SSMS Export Query Results to Excel or CSV

6 Answers6

Commas

Header row

Leading zeroes