Export to Excel from SQL Server

Brad Arnold

I often find myself needing to take data from a SQL query and give it to another person. SQL Server Management Studio (SSMS) does have the ability to right click in the results grid and save as a CSV file. There are two problems with that method. First, it is a time consuming process! You have to save the .CSV then open a new Excel workbook, then import the data from text (going through the wizard to define how it is delimited) then save your new .xls file. The other (and bigger problem) is that my data always seems to have a comma here and there which causes the column/row mapping to get out of sync.

That got me thinking, what if I could go right from SQL Server to Excel? Turns out there is a pretty easy way to do this. But, your going to need to follow some tips and tricks. The reason this isn’t as straight forward as you would expect is because SSIS (SQL Server Integration Services) has a very difficult time converting data and determining the correct type of data columns in your destination location.

Instructions

  1. Open Excel and create a new workbook where you will be exporting the data to.
  2. Run your query in SSMS. In the results grid, highlight the first row, right click, select “Copy with Headers”
    SSMS Copy with Headers
  3. In your Excel workbook created in step 1 select cell A1 and then right click, select “Paste”
    • This should have pasted the column headers as well as 1 row of data into your Excel workbook.
  4. Highlight row 2 (the one row of data) and delete the entire row.
    • You should now have an Excel workbook with column headers matching that of your query results.
  5. Close Excel
  6. Copy your SQL query syntax.
  7. Right click on the database you want to export out of, Tasks, Export Data.
  8. The SQL Server Import & Export Wizard will launch. [You may get a ‘welcome’ page, I’ve checked the check box so that I don’t get that page anymore. If you have the welcome page hit Next.] You should be at Choose a Data Source step. Data Source, Server Name and Database Name should all be populated correctly. If they arn’t set these settings now with your values. Click Next.
  9. You should be at Choose a Destination step. From the Destination drop-down select Microsoft Excel. Excel File Path click the Browse button and go select your Excel workbook we created in step 1. Notice how Import & Export Wizard automatically detects if you have an Excel 97-2003 file vs 2007/2010. Make sure “First row has column names” is checked. Click Next.
    Choose a Destination
  10. Select ‘Write a query to specify data to transfer’ [Note: I am using this because I am typically using specific criteria to select records. If you want everything in an existing table you can select the ‘Copy data from one or more tables or views’ option.] Click Next.
    Specify Table Copy or Query
  11. Paste your SQL syntax in that we copied in step 6. [Note: you can click the browse button and go find a .sql file if you have the SQL saved in a file somewhere.] Click Next.
    Provide a Source Query
  12. On the Select Source Tables and Views step it is going to default to trying to create a new worksheet in our workbook called Query. When the wizard attempts to create a new table (worksheet) I have run into nothing but errors here. What we are going to do is map it to our Sheet1 that we already pasted in the column headers. Under the ‘Destination’ column select the word Query and change the drop-down to ‘Sheet1$’. Click Next.
    Select Source Tables and Views
  13. On the Review Data Type Mapping step a lot of things are going to be marked with warnings. Don’t worry about this. We are going into columns marked as varchar(255) so everything should convert over without issue. Click Next.
    Review Data Type Mapping
  14. On the Save and Run Package screen accept all the defaults. Click Next.
    Save and Run Package
  15. On the Complete the Wizard step select Finish.
    Complete the Wizard
  16. Cross your fingers! SSIS is about to export your data to Excel. If everything worked correctly you should get The execution was successful page. You should see that all Status is ‘Success’ and Message column for the row Copying to ‘Sheet1$’ tells you the number of rows it exported to Excel. Click Close.
    The execution was successful
  17. Open your Excel workbook and you should see the data was exported!
    Exported to Excel

Note

Excel has some limits. If your query results exceed either of these limits you will have problems trying to export to Excel. Here is a table of Row/Column limits by version of excel.
Excel Limits

 

Conclusion

Exporting data out of SQL Server is a pretty common task. With this simple process you can now go directly to Excel and save yourself the trouble of importing and cleaning up the .CSV file!