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 ...")

enter image description here


6 Answers 6


First, make sure you've got the query options set up the way you want.

Go to the Query menu, and choose Query Options:

Query Options dialog

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:

"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.


I was having same trouble with field and limiting the length of the output didn't help at all because it has to do with the carriage returns.

Before you run the data you want output to Excel, change your query on those free text fields to deal with those returns:

SELECT REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), ''), ...

Once I did that, Excel output was as expected. This is the link that got me moving correctly.


The only real answer that works without dancing is: Use SSIS

I wanted to avoid SSIS, because I like simple SQL. Simple SQL is:

  • easy to test
  • easy to version control
  • easy to throw in a SQL Agent job
  • easy to get sql results into an email

I can do the whole thing in five to twenty minutes and be done. I can also replicate it to multiple SQL Servers easily.

I would have loved to use SQL#, (have long wanted an excuse to use it!) but it lacks native excel export functionality, and I have to fight some fires today, so can't screw around learning it.

When I have to chain pieces together, like SSIS , SQL Agent, etc, it is 1 or more hours of tinkering, and will all fall apart when we move SQL Servers (which we do every 16-24 months). Pieces are bad.

But I am stuck w pieces for this one.

Pity. I expect SQL Server could be stronger, at a meta level.


It's not SQL Server fault, nor SSMS'.

Excel by design open CSVs in the wrong way possible, it's been a well known issue for years now. You can even easily export a CSV file from Excel which is then not opened correctly by the same Excel.

The only solution with CSV and Excel is to not double click the file or use Open command from inside Excel (otherwise it will decide on its own to devastate them), but instead opening an empty worksheet and use Import->Import from text. With that, you can tell it how to manage every different column.

  • 1
    It is absolutely the fault of SSMS. Look at the OP. It says "csv is a mess, so how do I get a real EXCEL file out of SSMS?" Aug 23, 2018 at 18:12

I changed the SQL data in the export to an Excel formula. The Excel Trim() function will show numbers as numbers without scientific notation. Does not work for keeping leading zeros.

Since non-numerics blow up if used in a TRIM() function I had to do a check:

'=if(isnumber('+POH.[BEG_03]+'),TRIM('+POH.[BEG_03]+'),"'+POH.[BEG_03]+'")' AS 'TP_PO',

This is the formula I used to pass the PO number from the customer, which could be a string or a number.

In the example if it is a number, the formula looks like this:

which will look like this in the Spreadsheet: 100000657618

if it is a string:

=if(isnumber(C22686608),TRIM(C22686608),"C22686608"), it will look like this: C22686608

So even though it will look messy in the csv file, when it is opened in Excel, it looks correct. The user can then copy the column and paste it back as values if they don't want the formulas in the spreadsheet.


Use TOAD For SQL Server. The free version includes export to excel, even after the trial expires.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.