Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Export data from SQL Server to Excel

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (24)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Microsoft Excel Integration

Problem
Exporting data from SQL Server to Excel seems like a reasonably simple request.  I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters.  What native SQL Server options are available to do so?  Do I need to learn another tool or can I use some T-SQL commands?  Does SQL Server 2005 offer any new options to enhance this process?


Solution
Exporting data from SQL Server to Excel can be achieved in a variety of ways.  Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP).  Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job.  It is just a matter of your comfort level with the tools and the best solution to meet the need. 

Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005).  This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL.  Below outlines the full syntax available:

Source - SQL Server 2005 Books Online

 Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.
sysjobs
GO

Using the OPENROWSET command creates two caveats.  The first caveat is the need to have an Excel spreadsheet serve as a template in the needed directory with the correct worksheets and columns.  Without this the you would receive an error message.  The second caveat is that it is necessary to enable the OPENROWSET command with the SQL Server 2005 Surface Area Configuration utility.  Note - This step is not needed for SQL Server 2000. With the loop example you could copy and paste the Excel spreadsheet and load the data as needed.

Although the example above is very simple, you could integrate this simple example into your code.  For example, if you had the need to write out a number of reports in the same format, you could loop (WHILE loop or Cursor) over the records and write them out to different Excel spreadsheets based on the name or report type.   In addition, you could integrate this code with either SQL Server 2000 mail or Database mail (SQL Server 2005) and mail the results to the users with very little effort and build the process entirely with T-SQL.


Next Steps



Last Update: 3/16/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, December 09, 2008 - 12:02:04 AM - Patrickdh Read The Tip

Hi ,

   How do i install "'Microsoft.Jet.OLEDB.4.0'" driver on a 64bit sql server 2005 server ?


Thursday, April 02, 2009 - 2:03:02 AM - Rothy Read The Tip

My answer is DON'T. Rather let Excel suck it in from the database.

The simplest solution I would suggest is to use the Databse Query facility within Excel. I use this extensively and you can pass parameters through to filter data.

It uses MS Query to pull the info from any ODBC linked database and feeds it directly into Excel with a simple refresh inside Excel. 

The best part is that you can format all your reports once in Excel and you never have to do it again.

If you give your users read-only access to the database they can refresh the data themselves. And they can make modifications to the Excel report on their own too.

The data can be fed into either a flat table or a pivot table in Excel.

All you need to do is provide the data in a query in your database and let Excel pull it in from the query.

I use this so much I rarely even bother writing reports in the database.

 

 

 


Sunday, April 05, 2009 - 11:35:02 PM - sridar.be Read The Tip

While Executing the following Query i got the output as follows
and the excel file was not created in selected path
Please help me
Query:
EXEC master..xp_cmdshell 'BCP "select * from iplan_polaris.DBO.IP_PERSON Where LEN(PE_LOGN) = 5" queryout Contacts.txt -c -T '

Output:
NULL
Starting copy...
NULL
951 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total       31
NULL


My Questin:
1. While excuting the above qry, the Excel file was not created in selected path... What can i do now?
2. Excel file was created automatically i.e.created by sql server or created by manually?
3. any other alternative option is there in sql server 2005?

Its urgent
Please help me as soon as possible
thanks in advance
Sri


Thursday, April 16, 2009 - 3:20:51 PM - hoodmayor Read The Tip

I am looking for the best way to select table names that end with '_d' from a sql 2005 db and create a worksheet with the table name into one excel 2003 file.

Leaving the the format the same and with all rows from the tables where is_pickable is true.

Can you provide me with a sample T-sql script to do this?

Thanks.

Joe 

 


Friday, April 17, 2009 - 5:22:02 AM - grobido Read The Tip

This assumes you are in the database you want to use.

SELECT name
FROM sys.sysobjects
WHERE name like '%[_]d'


Friday, April 17, 2009 - 9:01:05 AM - hoodmayor Read The Tip

How would I omit the views & pk_ , & fk_ from the list, so I only have the table names that end with '_d'?


Friday, April 17, 2009 - 9:04:32 AM - grobido Read The Tip

SELECT name
FROM sys.sysobjects
WHERE name like '%[_]d'
AND xtype = 'U'


Friday, April 17, 2009 - 9:39:40 AM - hoodmayor Read The Tip

Thank you so much!

I I just need to find the best way to copy these tables into an excel file, with each tables as a sheet (with same format & all rows).

I was trying to use the openrowset, but can't seem to get the format  correct?

Do you have any ideas for an easy way to do this with t-sql script?

Thanks.

Joe 

 


Friday, April 17, 2009 - 10:36:36 AM - hoodmayor Read The Tip

I  just need to find the best way to copy my domain tables from a MSSQL 2005 db into an excel file, with each tables as a sheet (with same format & all rows).

I was trying to use the openrowset, but can't seem to get the format  correct?

Do you have any ideas for an easy way to do this with t-sql script?

Thanks.

Joe 

 


Friday, April 17, 2009 - 5:46:16 PM - Rothy Read The Tip

 First create a query with the commands that grobido has provided. Call it SysTableView or whatever is appropriate.

Then in an Excel sheet do the following..

\Data
Import External Data
New Database Query

Switch off the query wizard

Create a new data source pointing to your MS SQL database.

Select the query above "SysTableView"

Select the relevent fields

 From the Records dropdown menu switch off automatic query

 From the File menu select "Return Data to Microsoft Office Excel"

 Select OK

 ...from now on all you have to do is refresh the query within Excell to get the latest information.

 

 


Monday, April 20, 2009 - 9:08:06 AM - hoodmayor Read The Tip

Thanks I will try this now.


Monday, April 20, 2009 - 10:12:38 AM - hoodmayor Read The Tip

following the steps all I got was the tables names listed in cell a of the excel file.

I was looking to create a sheet in the excel file for each of the sql tables (with all columns & rows) populated into the sheet and the sheet having the same name as the table.

Can this be done using this method?

Thanks!


Monday, April 20, 2009 - 1:07:04 PM - Rothy Read The Tip

 Yes - using the method described; from Excel, select each table you require rather than the SysTableView. You can rename the sheet-tab with the name of the table.

If you are still having trouble we can hook-up through Skype.

 


Monday, April 20, 2009 - 2:10:59 PM - hoodmayor Read The Tip

Thanks.

I will see what I can create today with each select for each table, but I am not sure how I can save the sheet with the table name.

if you have time to hook-up tomorrow, that would be real helpful.

Thanks,  Joe

 


Tuesday, April 21, 2009 - 11:13:31 AM - hoodmayor Read The Tip

I was playing around with creating a Macro this morning and have one saved in my 'All open Workbooks'.

How can I copy the saved Macro and send it to a co-worker for testing on his PC?

I am not sure if this is the path I should take or did you want to show me something else from my comments yesterday?

btw ....how do we hook up with Skype?

Thank you for your support!

Joe 

 


Tuesday, April 21, 2009 - 3:18:22 PM - Rothy Read The Tip

 Go to Skype.com and download the latest version. I assumed that most people would have Skype but if you don't know what it is then it may not be a good option. If you want to phone me instead thenlet me know with a private message.

 To change the name of a tab you merely double click on it and type the new name.  As the spreadsheet can be saved and reused repeately this once-off operation probably doesn't warrant a macro.

 To send the macro to someone else, edit the macro then cut-and-paste the text into an emai. The co-worker can then paste it into a new macro. Probably just easier to send the spreadsheet with the macro to the co-worker.

 


Tuesday, June 23, 2009 - 6:22:26 PM - ssivaprasad Read The Tip

I am getting error below, while trying to export the query data into a excel file

Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.


Wednesday, December 09, 2009 - 11:29:47 AM - Lisa7 Read The Tip

How to export MSSQL data to Excel 2007:

OMG, I am posting this to save others hours of frustration importing MSSQL data into Excel.  I'm with Rothy who posted earlier (THANK YOU!).  Use Excel's data import function to do this, it is so much easier than messing around with MSSQL query code. It took me ONE MINUTE to import an MSSQL table and most of that time was spent looking up my login credentials;)

Go the Data ribbon in Excel 2007, click on "From Other Sources" in the "Get External Data" area on the left side.  It will ask you for the login credentials to your database.  Data is dumped right into Excel, with automatic row formatting, and all those miserable blank cells you might get with a straight html copy/paste are automatically removed.  Easy peasy! 


Tuesday, February 21, 2012 - 2:51:57 AM - f Read The Tip

Go the Data ribbon in Excel 2007, click on "From Other Sources" in the "Get External Data" area on the left side.  It will ask you for the login credentials to your database.  Data is dumped right into Excel, with automatic row formatting, and all those miserable blank cells you might get with a straight html copy/paste are automatically removed.  Easy peasy!


Tuesday, March 20, 2012 - 6:53:54 AM - JIT Read The Tip

How to export data into Formatted Excel Sheet Using OPENROWSET From SQL Server

Formatted Sheet Means it has table which starts from 5th row of excel sheet


Thursday, March 22, 2012 - 12:56:52 AM - JIT Read The Tip

Eagerly waiting for answer............


Tuesday, June 26, 2012 - 4:23:00 PM - Edward Jd Read The Tip

Using current tools in Ms Excel (Data\From Other Sources\From SQL Server or Data\From Other Sources\From MS Query) almost give the same result, except that SQL data can be filtered before being exported inito Excel speadsheet. The export process is quite easy & there's no any issue when the number of record is not over than 65536.


Wednesday, July 10, 2013 - 11:54:07 AM - Lynore Read The Tip

hi i need some help with exporting a table from my database (sql server R2 2008) to another database also using sql server R2 2008.

 

 


Thursday, July 11, 2013 - 12:11:12 PM - Jeremy Kadlec Read The Tip

Lynore,

Check out this tip -http://www.mssqltips.com/sqlservertip/2676/export-data-to-an-earlier-sql-server-version/.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.