SQL Server: SSMS “Save Results As” creates CSV that Excel mangles

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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