01.08.08
Export data from SQL Server to Excel without using SSIS or DTS
Hi,
Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005).
For some reason if at all you want to do it via query read on:
Step 1: Execute the code snippet
–Step 1: Execute the code snippet
EXEC sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
Step 2: Create the excel file and then add the headings in the .xls file.
– Create one Excel File in c:\Test.xls and rename the Sheet1 to Emp. The Sheet should contain 2 columns EmployeeID,Title
Step 3: Run the following query to export data from sql server to excel
USE [AdventureWorks]
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\Test.xls;’,‘Select * from [Emp$]‘)
SELECT EmployeeID, Title FROM HumanResources.Employee
Points which might interest you:
1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\Test.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).
2. Instead of “Emp” replace it with your excel worksheet name.
priya said,
January 24, 2008 at 7:04 am
Thanks daya!! Its really helped me a lot….
Ankit said,
April 8, 2008 at 11:29 am
This is exporting data directly from sql server to excel, is there any way to do so using java???
Please let me know…..
dayananthan said,
April 10, 2008 at 4:02 am
I dont know abt java. Hope this article may help you
http://java.ittoolbox.com/groups/technical-functional/java-l/read-data-from-excel-using-java-and-insert-it-in-to-mssql-database-1622390#
brad77 said,
August 14, 2008 at 6:01 pm
I tried to get this working on the SQL Express instance on my laptop running Vista and had a few problems. I kept receiving the following message:
OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
Not all that helpful. It ends up that it was a permissions problem. I needed to be sure to run the SQL Management Studio as Administrator (right-click on the shortcut and choose “Run as administrator”) to get it to work.
Guruprasath B said,
October 21, 2008 at 5:29 am
Thanks Daya. Wonderful Article. Its helped me a lot.
Bill Obst said,
November 4, 2008 at 7:57 pm
Daya… is there a parm that can be specified to direct that the data should be exported to a specific worksheet in the Excel file?
dayananthan said,
November 13, 2008 at 5:24 am
Hi Bill,
You can give the worksheet name as parameter
Select * from [Emp$]
In the above statement [Emp] is the worksheet name. You can place a variable as a parameter there and data will be directed to that specified sheet
Srinivas said,
February 27, 2009 at 7:31 am
It helped me a lot and solved my problem.
But i have some queries…
How to delete existing data in the excel file through SQL Query?
So that i can remove repeated data.
Keith Robinson said,
March 4, 2009 at 5:39 pm
Just as a heads up to everyone. This won’t work in SQL 2008 64bit. There is not a Microsoft.Jet.OLEDB.4.0 driver for SQL 2008.
dayananthan said,
March 10, 2009 at 6:52 pm
Oh is it i didn’t try that in SQL 2008 64 bit… Thanks for your information
Dayaa
Naveen kumar said,
March 24, 2009 at 7:03 am
hi
I tried running the query in sql 2000. I got the following error. My sheet name is sheet1 only. But still I am getting the error. Can u help me out?
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]
Marin said,
May 15, 2009 at 12:05 pm
Ah, I was so happy to find this. but it doesnt work on 64bit
Dave said,
May 20, 2009 at 4:37 am
I am wondering is there a way to Export from EXCEL to MS SQL Server 2005 Express without using SSIS or DTS ?
Very much appreciate for your kind suggestion.
John Esraelo said,
June 1, 2009 at 5:53 pm
Very useful information and it worked for me right away.. I wonder if the same type of openrowset would work with almost any external links..
Of course with the proper selection of the OLEDB and the path and all. that..
very good.. and thank you again.
JohnE
John Esraelo said,
June 1, 2009 at 5:55 pm
Thank you!! It worked for me right away.. excellent method..
This way, I don’t have to enable my SSIS engine and process and I can put my script right onto the Job/Task sql panel.
awesome
JohnE
tms said,
June 4, 2009 at 8:32 pm
Configuration option ’show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 213, Level 16, State 1, Line 2
Insert Error: Column name or number of supplied values does not match table definition.
help-me!
Matt Thompson said,
August 18, 2009 at 10:27 pm
Great idea. I tried BCP to get the desired output but it wouldn’t readily re-import correctly back to SQL.
Now i am going to incorporate this with just adding the column names via the information schema. and trying to specify the worksheet name the same as well.
tester said,
October 6, 2009 at 5:38 pm
ERROR:
Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ has been denied. You must access this provider through a linked server.
murale said,
November 20, 2009 at 5:58 am
hey.. i need to export tables in my sql 2008 into multiple excel files,( not in multiple excel pages in one excel file…)
murale said,
November 20, 2009 at 5:58 am
hey.. i need to export tables from my sql 2008 into multiple excel files,( not in multiple excel pages in one excel file…)