Question: How to Export Data From SQL Server to Microsoft Excel Datasheet?
Answer:
Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively, you can download sample Spreadsheet from here.
Run following code in SQL Server Management Studio – Query Editor.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.
Make sure your spreadsheet is closed during this operation. If it is open, it may throw an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.
Let me know what you think of this blog post. It is very short but indeed a very effective query. I have been using it for a while and it works just fine.
Reference : Pinal Dave (https://blog.sqlauthority.com)
231 Comments. Leave new
hi
I tried the same but i get th following error
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” 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.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Can u please help me how to solve this ,
can i use the same in server 2008.
Please help me
This code works properly! Thanks
this code is work fine
thanks
hi
I tried the same but i get th following error
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” 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.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
please give me solution
dtswizard.exe .net framework initialization error unable to find a version at runtime t orun this application
can u tell me the the posiple problem,when i try
tnx for ur help
I have the same problem. I get the same error msg when i try to import/export data. That is “dts wizard.exe -.net framework initialisation error. Unable to find a version of the runtime to run this application” can any one help please.
Hi , Prashant Shetake
Please close the contact.xls sheet and try to execute the query.it works fine
Regards
kalyan
Hi, Devdatta Bhosale
Please close the contact.xls sheet and try to execute the query.it works fine
Regards
kalyan
hi
Using the example above – I have a situation, where i need to save each person’s detail as a seperate excel file. The thing is i am not sure how many rows are going to be returned by my query each time. so can some one please help me I have the following done –
– I have the query
– I have the rows being returned in ssms
– the results are stored in a temp table
– I would like to get each row saved at a location and the file name appened with the person’s name so I know who it is for to be emailed.
Can some one please help me with the bit of storing each row as an excel file or flat file and adding the persons name to the file name..
Thanks
bubbles
Hi,
If you want to store results in text files thaeI would like then SQLCMD is an easy method.
At first create a script to run on command prompt. customize the below statement:
SELECT ‘SQLCMD -S server_name -Q “SELECT * FROM db.schema.Table WHERE personname = ”’ + personname + ”'”
-o c:’ + personname + ‘.txt’
from db.schema.Table
It would create a SQLCMD statement for each record. Then execute these SQLCMD command on command prompt. a new file would be created for each statement.
Regards,
Pinal Dave
hi,
I want to transfer the data from excel worksheet to a database table in sqlserver.
Kindly help.
when ever I tried to execute the below query it gives me an error.
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1]‘)
SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
FROM ACCTaxMaster where txem_id>32
The error is :
OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” 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.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
Pls reply.
hi
i have use ur code to transfer data from execel sheet to sqlserver database with a little change
the code are given billow
USE [salary9]
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=D:contact.xls;’,’SELECT * FROM [Sheet1$]’)
SELECT FirstName, LastName
FROM testtable
GO
After executing this code in sql query editor
the following err is fired:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
plz Help.
Make sure Server has access to that file and also it is closed
Dear Sir,
I am using Siemens SCADA, WinCC V7.0 which uses SQL SERVER 2005 Database.
I want to generate report in excel format.
For this, want to export data from SQL SERVER DATABASE to excel.
for report, client will select on SCADA runtime Screen , start date & time and end date & time, then tags logged during this time period should be exported to excel sheet.
how and where to write script for this?
please help.
Warm regards,
Damayanti
i have 3 sheets in an excel with names sheet1,sheet2,sheet3..
i have transfer all the columns in all the sheets into sqlserver
table in data flow task..
How can i do it??i need whole steps…
Hi Suma
I had this problem a few weeks ago. One of the best things to do is to run the Import Export Wizard or alternatively
in the data flow task in the OLEDB destination or SQL Server Destination which ever you are using select the Source to be SQL command from the drop down box – and in the SQL box enter –
select * from Sheet_Name;
select * from Sheet_Name;
and i think this should work.. try it out and let me know
Hi,
I am exporting data from a sql 2008 database using ssis import/export wizrd to excel. The columns in excel are truncated. How can I autofit the columns in sql?
Please help.
Is there any way where you can automatically enter in cells(1,1) on contact.xls with a tag like “XYZ Company” and
cells(2,1) with “Location: texas”. I want to do this automatically when the scripts run.
Hi Dave,
Can I export the Stored Procedure (SQL Server 2005) result to Excel (version 2007), like the way we do for Tables and Views (Creating SQL Server connection from excel and export table and view).
Yes
Insert into OPENROWSET(….)
EXEC procedure_name
To elaborate it:
I am using SQL Server (version 2005) data connection in Excel 2007 to retrieve bulk of records from SQL Server table. Currently, the number of records are around 400,000 and they keep on increasing further going down the line. The query is taking too long to execute while refreshing the data.
I want to use a Stored Procedure instead of a SQL Query which helps in executing result faster and give me data without causing delays in my Excel spreadsheet.
Is there any way by which I can import data using a Stored Procedure from SQL Server ?
Any help would be appreciated.
Many Thanks.
I’ve tried the below script following your example:
USE [EPATEST1];
GO
INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\Dummy EPA Test Data1.xls’, ‘SELECT * FROM [Sheet1$]’)
…but I get the below error message on SQL Server 2005:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘)’.
What is it that I’m doing wrong?
Your file path has spaces so put double quotes around file path and try it
It works nicely. Thank you.