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, 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…
HI Pinal,
I have an issue with SSIS package, I have designed a package and schedule it in the prod server, this package delete the exsiting detstination file through Script task and pump the data every time as I need to overwrite the existing data with new data.
The issue is, if anybody open the destination excel file, the pakcage is failing and I am unbale to track those details via send mail task.
Can any body help on this please..
Thanks,
Rams.
More informations are available here
Edgar
Use Import/Export wizard from SQL Server
or read about OPENROWSET in sql server help file
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.
Close the file if it is opened. i got the same error but after closing the file i had executed the query it is exporting perfrectly…
Yes. When a file is opened it is exclusively locked by the user/system. So it should be closed at the time of running a query that is accessing the EXCEL file
Hey,
first of all create an contact.xls file in specified location with Firstname and Lastname column heading in first row in sheet1 then execute the query.
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..
Read about OPENROWSET in SQL Server help file
It has example on how to do it
yes that is the error but i got correct link or path… and filename..
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..
What did you mean by “it is not working”?
Did you get any error?
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’.
hi.use it-
==========
insert into P
values(‘gt’,’gt’,’gst1@in.com’)
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=c:contact.xls;’,
‘SELECT * FROM [Sheet1$]’)
SELECT top 5 FirstName,LastName,email from P
where FirstName>email
=========
ed9teen and neeraj instead of [Sheet1] write [Sheet1$]
Is it possible to set a nullable column as a Foreign key
to PrimaryKey Column of another table?
Siva.
thankx
its also can do using DTSWizard.exe in command Prompt or in RUN in windows
Thankx
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
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!
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:
——————————
BUTTONS:
OK
——————————
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)”.
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?
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.
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
if exceed field length 255 then how?
anyone can help or not