SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

January 8, 2008 by pinaldave

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 thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 61 Comments

61 Responses

on January 10, 2008 at 3:38 pm | Reply Edgar

Hi, this codes work great but how about it done by reverse..
like i have database in Access file in my C: drive then i want this to insert my SQL server database..

how could i do this..?

please help…
on January 10, 2008 at 7:58 pm | Reply Madhivanan

More informations are available here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
on January 10, 2008 at 8:00 pm | Reply Madhivanan

Edgar

Use Import/Export wizard from SQL Server
or read about OPENROWSET in sql server help file
on January 11, 2008 at 2:02 pm | Reply Gunjan Shah

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.
on January 11, 2008 at 2:09 pm | Reply ed9teen

thanks for advice.. all i saw there is inserting data using excel to SQL server. my question how about inserting data using access to SQL SERVER….

HOW COULD I DO IT..?

PLEASE HELP..
on January 11, 2008 at 5:35 pm | Reply ed9teen

yes that is the error but i got correct link or path… and filename..
on January 11, 2008 at 5:38 pm | Reply ed9teen

hi, i tried this codes to transfer my data from access to sql server…
but it’s not working…
can someone modify my codes please..?

Insert into magpatoc.dbo.RSOTransfer
Select * FROM (‘Provider=Microsoft.Jet.OLEDB.4.0;’,
‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
‘SELECT * FROM FinalCustItemRSO’)

Help please..
on January 16, 2008 at 11:56 pm | Reply neeraj

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 :

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Person.Contact’.
on January 17, 2008 at 10:14 pm | Reply Kostas Michas

ed9teen and neeraj instead of [Sheet1] write [Sheet1$]
on January 23, 2008 at 11:59 am | Reply siva

Is it possible to set a nullable column as a Foreign key
to PrimaryKey Column of another table?

Siva.
on January 23, 2008 at 8:52 pm | Reply raju

thankx
on January 23, 2008 at 8:59 pm | Reply raju

its also can do using DTSWizard.exe in command Prompt or in RUN in windows

Thankx
on January 28, 2008 at 11:45 am | Reply brajesh

sir,
i m working in sql server 2000, now i want to import server 2000 data in sql server 2005 ,how can i do this…help me plz
on February 5, 2008 at 9:14 pm | Reply Meesh

hi Dave
Trying to export a lot of data into Excel file but I get this error:
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.

I think it’s because there are line breaks and other characters in the data. Is there any way to get around this?
Thanks a lot!
on March 11, 2008 at 5:40 pm | Reply saraboji

TITLE: Microsoft SQL Server Management Studio
——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
ADDITIONAL INFORMATION:

String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3033&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

——————————
BUTTONS:

OK
——————————
on March 13, 2008 at 4:24 am | Reply aay

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)”.
on March 13, 2008 at 7:59 pm | Reply Sean

Hi – I would like to do something very similar…

I want all of the databases tables and their columns inserted into an excel spreadsheet. So schema rather than data.

Do you know how this can be done?
on March 13, 2008 at 10:32 pm | Reply Daniel Serodio

Sean, see the SQL Server help for “Information Schema”, it’s a set of “meta-tables” (or views) which contain information about your database’s schema.
on March 25, 2008 at 5:18 am | Reply abdul

Puinal Hi:

Is there a way to use query like below………..BUT NOT give column names hardcoded in the excel file….

The reason being my sql is dynamically being passed and I donot want to have column names hardcoded into my excel template.

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
on April 22, 2008 at 3:42 pm | Reply Tiong

if exceed field length 255 then how?
anyone can help or not
on May 13, 2008 at 3:18 am | Reply Monika

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
on May 21, 2008 at 4:03 pm | Reply ZR

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
on June 7, 2008 at 11:54 am | Reply Gufran Sheikh

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
on June 25, 2008 at 1:37 am | Reply priya

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: 0×80004005.
[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
on June 25, 2008 at 2:59 am | Reply priya

I am adding this to my above question
I need to insert data from B6
on July 24, 2008 at 2:09 pm | Reply nanaji

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.
on July 26, 2008 at 1:29 am | Reply Denny

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
on July 30, 2008 at 9:25 am | Reply Shyam

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
on July 31, 2008 at 3:34 pm | Reply p s

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)”.
on August 3, 2008 at 6:57 pm | Reply James

Works great, thanks
on December 20, 2008 at 12:14 am | Reply Brian

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?
on January 6, 2009 at 3:26 pm | Reply sizwe

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.”
on January 20, 2009 at 1:33 pm | Reply Prashant Shetake

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)”.
on February 16, 2009 at 12:36 pm | Reply Pritesh Parmar (Pintoo)

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……..
on March 10, 2009 at 4:32 pm | Reply dona

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)”.
on March 20, 2009 at 1:06 pm | Reply Sav

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
on March 29, 2009 at 1:25 am | Reply Anand

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.
on March 29, 2009 at 1:27 am | Reply Anand

Sorry forgot to mension n think this is required.

I am using Sql Server Express 2005.
on April 8, 2009 at 7:09 pm | Reply Scott

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?
on April 22, 2009 at 12:09 pm | Reply pramod

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
on April 30, 2009 at 1:03 am | Reply Steve

The reason many are getting this message:
[ “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” ] is because Excel must exist on the same machine you have the xls file on. The spreadsheet itself does not carry the Jet database engine.
on May 14, 2009 at 11:39 am | Reply Rizwan

hi dave i tried above code its working for excel but i want to export data from access data base to sql server and vise versa how can do this

pls help me i am a ferser……………..
on May 15, 2009 at 8:42 am | Reply Imran Mohammed

@Rizwan

There are a tleast 5 ways of doing this, please look at the below link for more details.

http://support.microsoft.com/kb/321686

IM
on May 22, 2009 at 5:36 am | Reply jith

Hi,

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\temp\test.xls;’,
‘SELECT aftercare_id,participant_id,r_followup_period_id FROM [Sheet1$]‘)
SELECT [aftercare_id],[participant_id],[r_followup_period_id]
FROM view_1
go

The above code works, but it will allow me to put only 7 columns, if i put ‘*’ it is not inserting data into excel, could you please tell me how to fix this problem

i have 30 columns in my view. i want to use all columns, could you please respond to me as early as possible.

Thanks,
Roy
on May 22, 2009 at 10:49 am | Reply Imran Mohammed

@Jith,

Sample from below link :

insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=D:\testing.xls;’,
‘SELECT * FROM [SheetName$]‘) select * from SQLServerTable

Please visit this link to get more details.

Link : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

~ IM.
on May 25, 2009 at 2:17 pm | Reply Arjay Orcasitas

Hi,

I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I’m working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=C:\Documents and Settings\…analysis.xls’, [data$])

you can contact me here: jaythree03@gmail.com
on June 3, 2009 at 6:29 pm | Reply Test

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Destination – Configuration” (37).
(SQL Server Import and Export Wizard)
on June 5, 2009 at 3:52 pm | Reply Thato

Hi I’ve tried this example with minor changes like:

INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0;Database=C:\Documents and Settings\Thato\My Documents\contact.xlsx;’,'SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
——————–
I get a syntax error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

Please help
on June 17, 2009 at 2:49 am | Reply Praveen

In a simple way, we can export the resultset to .csv file using the following scenario:

1. Go to Tools>>Options>>Query Results>>Sql Server>>Results to text: specify the output format to “Tab delimited”

2.While executing the query – set Resultset to show as text instead of in grid (Ctrl + Q + T)

3. Select all from the Resultset and save Results as “name.csv” or “name.xls”

- Praveen
on June 30, 2009 at 2:45 am | Reply Corelio

Hi!!!

Is possible to insert data from SQL Server to an Excel Spreadsheet that has a password protection? I need this because I want to prevent User Editing…

Tks a lot!!!
on July 20, 2009 at 10:39 am | Reply Nisam

Great topic. Thanks
on July 27, 2009 at 11:52 pm | Reply Bevo

Worked for me, thanks!
on July 30, 2009 at 11:55 am | Reply Jeff Moden

I can’t find the URL that I wanted to cite, but just to let folks know, the “Jet” drivers for this type of wonderful thing are not supported on the 64 bit version of SQL Server. Nice, huh?
on August 3, 2009 at 6:40 pm | Reply Kishore

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)”.

Solution :

Make sure your the file path and SQL server are in the same PC
on September 22, 2009 at 2:42 pm | Reply Allen

Hello sir,

For the solution you gave regarding exporting data from SQL server to Excel sheet, is there any alternative that the file should be manually created along wid a header row.

Is there any way the excel sheet too can be created with a header row defined by running a command line script from the sql promt so that the file along wid header is created at the same time just before data is inserted into that excel sheet i.e creating a dynamic query in which the file path , filename, column headers are specified in variables

Please help out.

Thanx
on October 11, 2009 at 10:49 pm | Reply mahmoud

USE [hrnew];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=d:\12345.xls;’,'SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 EMP_NO,EMP_FULL_NAME_AR FROM dbo.EMPLOYEES
GO

after run your script with my data i get the next error messge.
please help me

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
on October 26, 2009 at 3:05 pm | Reply karinloos

Hi, to all above that got the error :
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.

Ensure that you have a space between Excel and 8.0 in the OPENROWSET section ie

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0; Database=c:\somesheet.xls;’
,’SELECT * FROM [Sheet1$]‘)
on October 27, 2009 at 4:30 pm | Reply karinloos

to mahmoud

You generally see this type of error if you have not put the Column headers into Sheet1 of your spreadsheet .

ColumnHeaders must be the same as the Field Names.
on October 30, 2009 at 11:16 pm | Reply Rob

Thanks this works great.

One problem I am having. Numeric fields that I am exporting to excel are entered into the excel sheet as text.

I have formatted the column in the excel sheet to number and I am still getting text. Any ideas how to fix this?

Thanks
on November 14, 2009 at 2:41 pm | Reply Malleswarareddy

Hi Every one,this good and simple. I tried this but it is not Working For me.after that working fine because the same syntax could not be worked for Excel 2007.Finally i got and if we use .xls format and also it will give error if an excel file is open and also for wrong name and also for columns not mentioned
on November 18, 2009 at 2:43 pm | Reply Deepak

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