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 used your code
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
to export data from sql server to excel but i am getting 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 ‘SheetData1$’. 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 you help me out to get rid of this?
Thanks!
Monika
The error message is very clear
Make sure you spelled the sheet name correctly
Hi,
I want to create new excel sheet. How to do?
@Damodharan.
There is a very complex process to create Excel Sheet, which I do not suggest to anyone.
Simplest way to create new Excel sheet is to follow below steps,
1. Create a Template/Model Excel Sheet (Empty) in any folder.
2. Use Xp_Cmdshell copy command to copy this file from above folder to another folder.
EXEC master.dbo.xp_cmdshell ‘copy Folderlocation\ExcelFileTemplate.xls new_folder_location\ExcelFileTemplate.xls’
Now you have a new Excel File.
Perform you action, Write to Excel Sheet. Once done with your task.
3. Using Xp_Cmdshell you can also rename Excel Sheet.
~IM.
what doed sheet signify?
The sheet name should be correctly specified
Hi,
I have problem with Excel when I using Openrowset in sql 2k.
When I export sql table witch has column type decimal in excel I have column type string. I would like decimal in excel too.
What I can do?
THX
You should format the EXCEL cell to be of type decimal
I formatted the excel to number and it did not work.
Hi,
This Query runs successfully
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:mydb.mdb’;’Admin’;”, Table1)
But this doesnt because it has password
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:ACCESS.mdb’;’Admin’;’mypwd’, Table1)
Before it both queries giving authentication error but after setting the permissions i am able to run the first query, but i am still having problem to run second query
Hi,
I am trying to create SSIS to export data to excel sheets(to multible sheets from different queries using views)
I tried to run one query and export to one of the sheets.
I got this error
[Excel Destination [153]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
[Excel Destination [153]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Excel Destination Input” (164)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Excel Destination Input” (164)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
and more…
Pinal or some Gurus ,
do you know how should I export data to excel from
2005 using SSIS?
priya
I am adding this to my above question
I need to insert data from B6
when ever I tried to execute the below query it gives me an error.
USE [TEST];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\TEST.xls;’,
‘SELECT * FROM [Sheet1$]’)
SELECT * FROM t11
GO
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.
Hello,
“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.”. ”
this error means your excel file do not have a sheet named Sheet1, so check the excel file
This is not still working anybody know, how to overcome on 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.”. ”
Regards
Shyam
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)”.
Go thru this
https://support.microsoft.com/en-us/help/209805/you-receive-a-could-not-find-installable-isam-error-message-or-some-fi
Works great, thanks
We use SQL Server 2005 & SQL Server Express to keep our Management Software data.
My question is simple, but may not be so easily implemented. I would like to take our data (*.mdf) files and export it to a different work station that has also SQL Server Express. From my understanding this is not easily done thru SQL Server 2005. Does anyone have any suggestions?
hi, please help, i’m new to this sql…i’m running sql server 2005 on ms server 2003(64x). everytime i click on management studio this is the message i get “The file C:\windows\microsoft.net\framework\\mscorlib.tlb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”
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
This code is working properly, but when i changed the path of excel file then it gives an error(Note:- excel file is on anthoer machine)
path is like that :- \\bhavin\AX-Config\contact.xls
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)”.
Make sure your accout has access to that specific file
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=D:\UserRights.xls;’,
‘SELECT * FROM [Sheet1]’)
select * from dbtemp.dbo.Operators
here dbtemp is the database name and Operators is the table name
i have already created the file ‘D:\UserRights.xls;’
While using this query, i m getting following error whot should i do
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.]
OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
PLs Reply soon……..
Most likely sheet name would be $sheet1 and not sheet1
can anyone please post the solution to this problem
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
The first row of EXCEL sheet should have column informations
Hi,
The code provided works fine, but when i run the same second time the data in excel gets appended, where i want it to copy to a new sheet or delete the existing data and insert new data.. Awaiting for the response…
Thanks in advance
If you want to copy to a new sheet, use that sheet’s name in the query. Also it is not possible to delete data of excel sheet using a query
Hi, I would like to export data from store procedure (which has two input parameter) to Excel. The store procedure will return multipal record sets.
Please let me know if we have any way to do this.
Do all resultsets return same number of rows? Then it is possible. Otherwise you will get error
Sorry forgot to mension n think this is required.
I am using Sql Server Express 2005.
I have procudures that successfully download data into Excel worksheets but run into issues if a field on the SQL Server has more than 255 characters it fails. I am using Excel 2003 and have found some post regarding “Extended Properties” of the connection string however I cannot get this to work correctly.
Do you have any suggestions on how to get around this type of issue?
I tried u r code
It is showing Error
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.]
what is exact prob
is it with Excel Or with Sql
plz help
It is with the code used in sql
Make sure the sheet name is sheet1 (but most likely it should be $sheet1)