SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 SQL For DTS Export to Worksheet/Excel
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishal_7
Posting Yak Master

127 Posts

Posted - 08/28/2006 :  09:50:11  Show Profile
Hello,

I am trying to export data to excel from SQL, but not just CSV - I want the data to be transfered in two sheets but one excel file. I know that the DTS Export can do that, but I need to do that via standard sql. The reason is, because I need to export several tables from my asp.net code. The selection is done by the user. The user will simply select the report he wants to export. I would prefer to use a stored procedure, where I could pass the sql or some other parameters to export the tables to one worksheet. I have already checked the sp_xmdshell but it seems that I can only export that to a csv and only one table per excel file. Is there a way to export several tables to one worksheet via standard sql without using a dts?

Thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/28/2006 :  09:55:55  Show Profile  Visit SwePeso's Homepage
If the Excel file is already created, use OPENROWSET.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/28/2006 :  09:57:14  Show Profile  Send madhivanan a Yahoo! Message
See if this helps
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 08/28/2006 :  10:01:34  Show Profile
Thanks guys, I think that will help. I will get it from here.
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 08/28/2006 :  10:28:09  Show Profile
Any idea why the following code is failing?

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;', 'SELECT * FROM [Sheet1$]') select * from tblColors

I created an empty file on my local system (c:) - sql runs on a different machine. The excel file is empty, I want all columns to be exported. The sheet name is "Sheet1"

I am getting the error:
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: ].

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/28/2006 :  10:31:56  Show Profile  Visit SwePeso's Homepage
You have to provide the path FROM the SQL Server TO your file.
use UNC path as '\\YourMachine\YourShare\Test.xls'.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 08/28/2006 :  10:36:41  Show Profile
Thanks Peter, that did it. However I had to specify the first row in my excel sheet with the column names. Is there a way to avoid that? I tried this:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\bla\test.xls;', 'SELECT * FROM [Sheet1$]') select * from tblColors

But it complains with:
Insert Error: Column name or number of supplied values does not match table definition.

If I add the columns in the first row of my excel sheet, then it works, but I would like to avoid that.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/28/2006 :  11:51:40  Show Profile  Send madhivanan a Yahoo! Message
Refer this
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 08/28/2006 :  13:45:35  Show Profile
Thanks Madhivanan, but that seems to use bcp, which creates a csv instead of worksheets, correct? I need to create a worksheet.

The following seems to work:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\bla\test.xls;', 'SELECT * FROM [Sheet1$]') Select color from t

but this doesnt:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\bla\test.xls;', 'SELECT * FROM [Sheet1$]') Select color, ColorID ... from t

The problem is that I cant write to the file from my code, because I am using worksheets. If it were a single sheet, then I think I could write to it (not sure though).

Any idea?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000