Export SQL Data to Excel from Microsoft SQL Server
Let’s go over three ways to export an SQL Query to an Excel File Using MSSQL
Despite the pushback from Database aficionados, sometimes it makes sense to export data from SQL to an Excel file. It really depends on who is the audience of the information. Not everyone is great with SQL Analysis. Few people require access to the database.
And lots of times the boss just needs to see the information in Excel.
So, if Excel is the Output required this article is here to help you Export SQL Queries to Excel.
Here are three ways to Export a SQL query to Excel format. The last one can be kind of tricky. I’ll provide a condensed version followed by a detailed version with screenshots.
Three quick ways to Export SQL Data to Excel:
Choose the Summarized Version and then scroll to further down to use the SQL Export Version to Excel that works best for you.
Method Number 1 – Copy Grid results and Paste into Excel
Under Query, Make sure results to Grid are selected.
After
Running your query, right-click the top right corner of the grid.
Copy With Headers.
Paste into an Excel File
Possible Issues:
I’ve seen formatting issues with this strategy. For Example, there are situations where the spreadsheet drops preceding zeroes after the copy-paste.
I’ve also noticed lengthy fields, (think of a really long free text field) end up running into the next line.
For the reasons above, I prefer this next method for a clean Excel file.
Method Number 2: Export the Query Results as a Task
In the object explorer window, find the database you want to export from.
Right Click the Database, Click Tasks, and Export Data
The Wizard walks you through the rest of the steps but I have included screenshots below.
Method Number 3 – Use Good Old fashioned TSQL to send the data to an excel file
For those who value speed above all use the following script format.
INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0; Database=C:\SQL2019\Reports\Usernames.xlsx;’,’SELECT * FROM [Sheet1$]’) SELECT DisplayName FROM dbo.Users Where Reputation > 2000
Possible Issues – Configuring this might not be your type of fun and getting this straight deserves its own article.
Step by Step instructions with screenshots
Method Number 1 – Copy Grid results and paste into Excel
After ensuring results to grid turned on, Execute your query, right-click the top left-hand corner of the results grid.
Choose Copy with Headers and then you are ready to paste in Excel with Ctrl + C
Method 2 – Export Via the Export Wizard
Right-click on the database you want to export from. Then Select tasks and “Export Data”.
The SQL Server Wizard will startup. Click Next through the prompts.
Select the appropriate Native client, your server name, and database and choose “Next”.
Next, Select Microsoft Excel and the file path where you want to import the data. The .xls file that you name will be created by this process.
Now you can choose to export a whole table or a query. For the purpose of this exercise, we are creating a query.
Paste the query into the SQL Statement field. Make sure every output field has a unique name.
Click Next on the “Select Source Tables and Views” screen.
I use the default settings on the “conversion issues and data type mapping screen”
Now you can choose to Run immediately or Save an SSIS Package for later reuse.
Double Check your settings and click finish.
Make sure there were no errors in the Export.
Now Go to the directory you choose earlier and make sure you have a sweet-looking Excel File at that location!
Method Number 3 – Use TSQL to send the data to an excel file
This method is the quickest once you get it set up but the configuration is the tricky part. Permissions can be a limiting factor.
Also with the script below, you have to make sure the file exists before you run the query for it to import properly.
First, create a blank excel file at the directory of your choosing.
C:\SQL2019\Reports\Usernames.xlsx
Then run this script below.
INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,’Excel 12.0;
Database=C:\SQL2019\Reports\Usernames.xlsx;’,’SELECT * FROM [Sheet1$]’)
SELECT DisplayName FROM dbo.Users Where Reputation > 2000
Configuring this can be tricky and dependent on your level of permissions. Make sure you have the correct Linked Server/Provider installed (‘Microsoft.ACE.OLEDB.12.0’) And check your Database user settings to this server .