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
Perfect!!!!!!!!!!!!!!
Hi,
I’m trying to run the simple Query below just to test the export operation, but getting an error.
USE hd
INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\TesteInsercao.xls;’,
‘SELECT * FROM [SheetName$]’)
SELECT * FROM helpdeskv2.dbo.rep
Error: Msg 7403, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered.
Can anyone help, please.
Thank You
You need to register the OLEDB provider for 32 bit
And how do you register the OLEDB provider for 32 bit, Madhivanan?
Thanks.
This is listed under “more informations” here
https://support.microsoft.com/en-us/help/278604/http-500-100-internal-server-error-when-you-try-to-access-a-provider
Hi Pinal
i tired to insert data into Excel sheet from the below query
INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]’)
but it gives the following Errors
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.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Pls give me the Suggestion for this Query
Make sure the file is closed when you try to run the query.
Also make sure to read this for more troubleshootings
I need to query a SQL Server 2005 running on Server 2003 platform, for data to be put into an excel work book.
I do not want to run the query from the Server. I would like to have the query located in excel. (transportability)
example Server name Dell-2850SQL
database name =ClinicalData
I have the query I want to run and it returns the data I want. I just want it put in excel, and do not want anyone
to have access to the server, besides the query being run from a desktop workstation having MS office 2003.
I have tried this connection string am i on the right track?
Driver={SQL Native Client};server=Dell-2850;database=ClinicalData;Trusted_Connection=yes;
what can i do when i want to import data form exdel to database using sql 2005
i need it so badly
so plz replay it to me
thnks for advancer
sorry i mention excel not ecdel
really i want to konw it
i have another question
i had alot of tables in my database
i want know how can i get max value between that tables
Give us more informations
Start with
select max(col) from table
I want to have live currency exchange rates from dollar to euro, dollar to pound in my database in a separate table in SQL Server 2005 Management Studio. How can I do this? Any response would be highly appreciated..
Kindly tell me if there is any way thorugh which we can also create a schema in Excel as well.
Waiting for you kind reply
Thanks & Regards
Owais A Farooqui
mr SAlly,
Make sure that you had created the excel file in the server.
When someone using sql server as client only gets this problem you get.
So give the path in the sql server machine.
i tried this way and solved
OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=NO;Database=@C:\Documents and Settings\raise20\My Documents\Preadmission\PreadmissionDetail.xlsx’,
‘SELECT F7,F8,F9 FROM [PreadmissionDetail$] where F7”’)
Give Error Like
[OLE/DB provider returned message: Failure creating file.]
Msg 7399, Level 16, State 1, Procedure Inser_PreAdmissionFromExcel, Line 16
OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ reported an error.
in SQl 2000 Database
Hi Dave! Thanks for advice but i have a problem .İ tried many ways but i can’t solve it…
I’m using
Operating System Windows Server 2008 R2 Standart &
SQL SERVER 2005
I declared everything and i using this query;
SET @SQLconnect = ‘SELECT * INTO ##TMP FROM OPENROWSET(”Microsoft.Jet.OLEDB.4.0”, ”Excel 8.0;Database=’ + @XLSFileName + ”’, ”SELECT * FROM [Sheet1$]”)’
EXEC (@SQLconnect)
i take this ERROR:
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.
How can i solve it in 64 BIT OS ?
i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me
i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me
Refer method 5. It does what you want
I’m able to insert the ‘ID’ column into Excel, but when I try to input the ID, productname and productdescription I get this error:
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” supplied invalid metadata for column “ID”. The data type is not supported.
The ID datatype is INT. I don’t know why it wouldn’t be supported.
This is my insert statement:
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\Uploads\testing.xls;HDR=No;’,
‘SELECT ID,productname,productdescription
FROM [Sheet1$]’)
select ID, productname,productdescription from tb_Products
Any thoughts on what I’m missing would be appreciated.
Thanks
Guys ………………..!
i found the solution ……
create a new excel with some name along with columns than save as ……….Compatible with 97-2003 excel work book.
Thanks,
Deep
what about .xlsx file ??
How to insert data from office 2010 to Sql server 2008 ?
Thanks for this code .
it is working with .xls files , but i am working with .xlsx files.
I am getting the error
please paste the code for .xlsx file.
Thank you
Abhishek
For MS Office versions 2010 onwards you need to use ACE provider
Thank you all… Code is Great
when i run the below query in SQL Server 2005 and MSOffice2007
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xlsx;’, ‘SELECT * FROM [Sheet1]’) select * from member
i get following 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)”.
Member table, c:\contact.xlsx and Sheet1 exists. Iam running the query on a valid db.
Please help me regarding…
Make sure the file is closed at the time of running this code