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
can you send your site address
alter procedure [dbo].[SP_ExportExcelFromSql]
as
begin
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
‘SELECT * FROM [Sheet1$]’) select * from usermas
end
After executing this i am facing the following error:
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Cannot update. Database or object is read-only.”.
Msg 7399, Level 16, State 1, Procedure SP_ExportExcelFromSql, Line 5
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Procedure SP_ExportExcelFromSql, Line 5
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]”.
Could anyone help plz……
If the EXCEL is in read-only mode it is not possible to add data to it
Hi Thanks for your response madhivanan,
I checked but it has permission for Read/Write…
Now im using my local database server….
and my file path is my local system…
whether this may give any problem?
Thanks in Advance
Try this
alter procedure [dbo].[SP_ExportExcelFromSql]
as
begin
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;IMEX=0;Database=F:SARAVANATest.xls;’,
‘SELECT * FROM [Sheet1$]‘) select * from usermas
end
Hi madhivanan,
Thanks for your support
I tried this one but it showing the same issue as :
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Cannot update. Database or object is read-only
But i saw the file by Right-Clicking and its properties
and it is not a read only Mode
moreover in the excel file(Test.xls) i added four column names in first line as
and No records for that is it right?
———————————————————-
Id User Name Password User Type
———————————————————-
So Plz give some suggestions to fix this issue
Thanks in Advance
ALTER procedure [dbo].[SP_ExportExcelFromSql]
as
begin
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
‘SELECT * FROM [Sheet1$]’) select * from usermas
end
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Cannot update. Database or object is read-only.”.
Msg 7399, Level 16, State 1, Procedure SP_ExportExcelFromSql, Line 4
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Procedure SP_ExportExcelFromSql, Line 4
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]”.
Still i am facing this error
Can anyone help me to solve this problem
Thanks in Advance
bhai 1st create excel and then try doin the same thing
Hi,
I am getting problem at the time of uploading “The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered” please help
Hi ,
I want to export sql server 2005 table to excel 2003(windows 7).
i used this query:
INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=C:\n\testing.xls;’, ‘SELECT * FROM [Sheet1$]’)
SELECT * FROM dlr_master
All column names in both sql server table and excel file are same.
Excel file is not open.
Path is perfect.
Still I m getting the error,
Cannot Initialize Datasource Object Of OLEDB Provider “Microsoft.Jet.OLEDB.4.0” for Linked server “(null)”
I feel its Windows 7 platform thats creating a conflict because this query runs well on Windows Xp.
Please suggest the right query and versions required
Thanks
Make sure that the file is closed at the time of execution and check if the JET driver for EXCEL is installed in the Server
Hello there..
Can u please guide me as to how to check whether the JET driver for excel is installed in the server?
Thanks
Hi,
I checked it. i have 6.0 version for Excel driver on my Windows 7.
Now can u suggest what i need to do?
Thanks
You can know it via Control panel–>Admistrative tools–>Data sources (ODBC)
Great code, thanks a lot!!, it really works
Hello ALL,
I am getting this error, can any help to get rid this of
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Could not find installable ISAM.”.
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)”.
Regards,
Shiv
Excellent……. Excellent…….. Excellent……… 100% fit solution complete solution. Bundle of thanks. Your solutions are really have authority in SQL…
beautiful solution, thank you. The codes are fine. But I have problem here. After execute the insert statement, the data always appear starting different row. How I can do if I want the data always start at A2 since my data is linked to charts. I need to update the weekly.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
problem:
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “TestLinkServer”.
Hi Sir when i try to run yoour Script sql Server give me this Error
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Note : im not open the Ecexl File its Close When i run this script
its very help full to mee
thnks
Print ‘Creating Proc’
GO
Create Proc GetMonthlyDownloadLogDetails
/* *****************************************************************************
** Name: GetMonthlyDownloadLogDetails
** Desc: To Get DownloadLogDetails Monthly Wise
** Auth: BalaKrishna Hari
** Date: 10/05/12
********************************************************************************/
AS
SET NOCOUNT ON;
Select convert(varchar,reqtime,103)
,Appid,channel,bill_rescode
,Unitprice,count(*) Requests
,SUM(Case When dwnstatus=’Y’
Then 1 Else 0 End) as Success
,SUM(Case When dwnstatus ‘Y’ Then 1 Else 0 End) as Failures
From cms_download_logs (nolock)
Where DATEPART(MM,reqtime) =DATEPART(MM,dateadd(MM,-1,getdate()))
And DATEPART(MM,reqtime) = DATEPART(YY,getdate())
And appid Not Like ‘%_FREE’
Group By Convert(varchar,reqtime,103)
,appid
,channel
,bill_rescode
,unitprice
Order By convert(varchar,reqtime,103),appid
Go
–Print ‘Creating Proc For BCP’
–GO
Declare @path varchar(100)
,@CMD varchar(1000)
SELECT @path = ‘C:\MonthlyDownloadLogDetails\MonthlyDownloadLogDetails_’ + CONVERT(VARCHAR,GETDATE() ,110)
SELECT @CMD = ‘bcp “GetMonthlyDownloadLogDetails” queryout ‘ + @path +’ -S.\sql2008r2 -T -c’
SELECT @CMD
EXEC XP_CMDSHELL @CMD
If record are more than 65535 rows then csv can used.
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
BULK
INSERT CSVTest
FROM ‘c:csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘n’
)
GO
Hi Gurus, when I have run the below query to export to excel ;
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:test.xls;’,
‘SELECT * FROM [Sheet1$]’)
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
I have got below given error..pls help me to get it done…
“Msg 7308, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.”
Thanks..
Chintesh Soni
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\test.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT * FirstName, LastName
FROM Person.Contact
It takes long time to execute.can u please tell me how to make it faster
Hi All
When I Execute above code got error
Msg 7308, Level 16, State 1, Line 2
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Any Suggest Please