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 pinal, how to do it in sql server 2008 with excel 2010?
hi Pinal,
i have question and maybe somebody or you can help me to solved this problem.
if i have 1 files Excel,and 1 files excel have 3 sheet (sheet1,sheet2,and sheet3) with different column on every sheet,can you tell me how to import that file and create table based on how many sheet in that excel files (e.g i have 3 sheet), so if i have 3 sheet, i can create 3 table on 1 database.i suppose i can use foreach loop container for that,but how to create the 3 tables based on sheet ? (dynamic).
i can do that if i use import data features on sql server,are ssis have item tool like that ?
thank you very much for your help.
Mr.Dave
I have a problem in my store procedure. when execute sp, i have go this error
“Msg 137, Level 15, State 2, Line 5 Must declare the scalar variable “@FDate”.
I have tried may way, But I could not. but without date, it works fine. Pls
Please advice me where I did wrong?
Thank You
Maideen
it is My Store Procedure
USE [collegeBac]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
@FDate datetime,
@TDate datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ‘,[‘ + CAST(RCVDFor AS VARCHAR) + ‘]’,
‘[‘ + CAST(RCVDFor AS VARCHAR)+ ‘]’ )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N’
SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails
ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor
WHERE dbo.vwRCHeadDetails.RCDate >= @FDate and dbo.vwRCHeadDetails.RCDate <= @TDate
) AS PivotData
PIVOT ( SUM(Amount) FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable'
EXECUTE(@PivotTableSQL)
END
hi Dave Sir
greeting
I have tried to copy the Sql server Query 2008 data to excel 10 ,
to do such work i have run these statement
“”
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 12.0;Database=c:contact.xls;’,
‘SELECT * FROM [Sheet1$]’)
SELECT TOP 5 Name, Add1
FROM Table Name
“”
After doing this i getting an error that is
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
“”
Please Give me Solution its very important to me,, any one can know it please let me know tha solution
Thanks it works good one, but need to modify this query,suppose after generating the data in excel one time, if i run the query next time the existing data in excel should gets deleted or it should validate the data present in excel as well as the fresh data which is going to be uploaded.
I’m Getting Error in SQL Server 2008
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.
Please tell me the Solution
Hi Pinal,
Do you know how to export the SQL data into a new excel file /separate file every week?
Trying to get this working for importing sql table to an existing access table
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Access 9.0;Database=C:\Database1.mdb’,’SELECT * FROM [Code]’);
hi Dave Sir,
I’m using MS SQL server 2008 R2
I have sample table call Person and there are two fileds call F_Name and L_Name
I want to export and Import data to using excel sheet
I run this query for export data
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=E:\dil.xls;’,
‘SELECT * FROM [Sheet1$]’) select * from Person
i got this 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 2
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
can you solve this error and how can import data from excel data
I got this error
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.
Please help me
Hi,
Can any one Help me out. I want to set Font to excel i m exporting using from Procedure.
this is m code:
INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0′,’Excel 8.0;Database=E:\Excels\FileSG.xls;’,
‘SELECT * FROM [Sheet1$]’)
Select * from SGDetails
Thx,
Naeem
I’m getting error: The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered. How to reslove it? Note: I have Windows 7 64bit machine, Both SQL server 2005 and 2008 installed and excel 2010.Please help.
Facing an issue when exporting data to an Excel template from SQL server. The issue is that the Excel worksheet is protected. Can you please suggest me how to resolve the issue?
I have not found any solution using OPENROWSET. Can you try creating a linked server and give password there?
Hi Iam getting the below error
pls help me
Msg 7399, Level 16, State 1, Line 67
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 67
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Do you have provider installed correctly? What is the version of OS, SQL Server and provider?
Hi Pinal ,
Is there any way to fetch the data from password protected Excel file.
thanks
Ashish
how to Deleting Data in insert into OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;Database=C:\Scale\contact.xls;’,
‘SELECT * FROM [Sheet1$]’) select * from ScaleUpdate2
how to delete date Before inser insert into OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;Database=C:\Scale\contact.xls;’,
‘SELECT * FROM [Sheet1$]’) select * from ScaleUpdate2
Hi,
how can I specify the start cell of the Excel file where to export data from SQL ?
Eunhee… the best way to do this is to create a refreshable Excel report based on a SQL Server stored procedure.
I do not know if Pinal wrote any articles on that. :(
Thank You
Your welcome @Hasha.