x

Export Sql Server ResultSet to Excel Sheet

Hi

I need the Sql Server Resultsets to be exported to Excel Sheets. There will be multiple queries and it is not fixed. Hence we CANNOT create the destination Excel Sheets in advance.

I'm aware of the following script, but for this the excel sheet has to be created in advance...

INSERT INTO OPENROWSET 
('MICROSOFT.JET.OLEDB.4.0', 
'Excel 8.0; Database=\\dba\Share\Test.xls;HDR=NO;IMEX=2',
'select * from [Sheet1$]' 
)
 SELECT * from category 
Rgds
Mohan Kumar VS
more ▼

asked Jul 01, 2011 at 05:25 AM in Default

Mohan Kumar gravatar image

Mohan Kumar
204 18 20 21

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Not 100% on the OPENROWSET, but BCP will allow you to do this.

[MSDN BCP][1]

[1]: http://msdn.microsoft.com/en-us/library/ms162802.aspx
more ▼

answered Jul 01, 2011 at 05:49 AM

sp_lock gravatar image

sp_lock
9.1k 24 28 31

Thanks a lot.
Jul 03, 2011 at 11:39 PM Mohan Kumar
(comments are locked)
10|1200 characters needed characters left

Another option is to use Powershell. Within Powershell you can create Excel objects (sheets), run sql and export data - seems like a candidate!

Maybe start with this link : http://sqlserverpedia.com/blog/sql-server-bloggers/powershell-and-sql/
more ▼

answered Jul 01, 2011 at 09:01 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

Thanks a lot.
Jul 03, 2011 at 11:39 PM Mohan Kumar
(comments are locked)
10|1200 characters needed characters left

Hi could you please let me know some more info about this query....

INSERT INTO OPENROWSET 
('MICROSOFT.JET.OLEDB.4.0', 
'Excel 8.0; Database=\\dba\Share\Test.xls;HDR=NO;IMEX=2',
'select * from [Sheet1$]' 
)
 SELECT * from category ....
more ▼

answered Nov 19, 2012 at 10:24 AM

pan_cbe gravatar image

pan_cbe
0

I would suggest you ask this as a new question, indicating what you want to know...
Nov 19, 2012 at 11:10 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x115
x28
x26

asked: Jul 01, 2011 at 05:25 AM

Seen: 4209 times

Last Updated: Nov 19, 2012 at 11:10 AM