| Author |
Topic |
|
vishal_7
Posting Yak Master
127 Posts |
 Posted - 2006-08-28 : 09:50:11
|
| 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
30421 Posts |
Posted - 2006-08-28 : 09:55:55
|
| If the Excel file is already created, use OPENROWSET.Peter LarssonHelsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
vishal_7
Posting Yak Master
127 Posts |
Posted - 2006-08-28 : 10:01:34
|
| Thanks guys, I think that will help. I will get it from here. |
|
|
|
vishal_7
Posting Yak Master
127 Posts |
Posted - 2006-08-28 : 10:28:09
|
| 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 tblColorsI 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-28 : 10:31:56
|
| You have to provide the path FROM the SQL Server TO your file.use UNC path as '\\YourMachine\YourShare\Test.xls'.Peter LarssonHelsingborg, Sweden |
|
|
|
vishal_7
Posting Yak Master
127 Posts |
Posted - 2006-08-28 : 10:36:41
|
| 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 tblColorsBut 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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
vishal_7
Posting Yak Master
127 Posts |
Posted - 2006-08-28 : 13:45:35
|
| 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 tbut this doesnt:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\bla\test.xls;', 'SELECT * FROM [Sheet1$]') Select color, ColorID ... from tThe 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? |
|
|
|
|
SQL For DTS Export to Worksheet/Excel