Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bbasir
Yak Posting Veteran
76 Posts |
 Posted - 2008-03-21 : 20:21:13
|
| HI:MY STORE PROC IS BELOW....IS THERE A WAY TO CREATE AN EXCEL FILE LIKE BELOW, WITHOUT GIVING THE COLUMNS NAMES.....IN THE SELECT OF SHEET1$ OR SHEET2$ AS SHOWN IN THE CODE BELOW...THE REASON IS THIS, MY SQL QUERY WILL BE DYNAMIC AND I WILL NOT KNOW WHAT COLUMNS WILL BE SELECTED, SO IF IF DO SELECT * ON SHEET1 OR SHEET 2 IT GIVES ME AN ERROR THAT COLUMNS DONOT MATCH THE SELECT STATEMENT.Create PROCEDURE MCA_SP_CREATE_EXCEL @File_Name as varchar(50) = ''ASBEGINSET NOCOUNT ONdeclare @File_Name as varchar(50)DECLARE @Cmd varchar(1000)DECLARE @fn varchar(500)DECLARE @provider varchar(100)DECLARE @ExcelString varchar(100)-- New File Name to be createdIF @File_Name = ''Select @fn = 'C:\Test.xls'ELSESelect @fn = 'C:\' + @File_Name + '.xls'-- FileCopy command string formationSELECT @Cmd = 'Copy C:\Template.xls ' + @fn-- FielCopy command execution through Shell CommandEXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT-- Mentioning the OLEDB Rpovider and excel destination filenameset @provider = 'Microsoft.Jet.OLEDB.4.0'set @ExcelString = 'Excel 8.0;Database=' + @fn-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'') select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'') select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')SET NOCOUNT OFFEND |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-22 : 05:01:30
|
Include the "Hdr=Yes" option to the connection string.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-03-24 : 12:43:48
|
quote:
Originally posted by Peso
Include the "Hdr=Yes" option to the connection string.Thank you for your reply, let me try this. I appreciate it.
E 12°55'05.25"N 56°04'39.16"
|
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-03-24 : 17:33:36
|
quote:
Originally posted by Peso
Include the "Hdr=Yes" option to the connection string.Thanks you fro your reply, however one thing that is still giving me a problem is this, when I remove columns from my excel spread sheet and my code is as follows:- set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sqlRIDBID + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sqlRELID + ' ') sqlRIDBID ='select * from table1'sqlRELID ='select * from table2'the above gives me an error....Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.Server: Msg 213, Level 16, State 1, Line 1Insert Error: Column name or number of supplied values does not match table definition.even when I change the above two sql statments with the column names, then it also gives me an error, ??? the same one as above...I have added the hdr=yes as suggested as well.It seems that the only way it works is that as long as column names are there in the template file??? I donot want to hard code the headers in the template file as want to generate them dynamically from the sql that passed to the open rowset.
E 12°55'05.25"N 56°04'39.16"
|
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-03-25 : 13:12:00
|
| Even this simple query doesnot work... In my excel file I donot want to hard code the column names....so there are no column names entered in that... I want the select * from pubs.dbo.authors to get the column names and display in the sheet.INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\pubsTest.xls', 'SELECT * FROM [Sheet1$]')SELECT * FROM pubs.dbo.authorsServer: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. |
|
|
|
jrogers
Starting Member
34 Posts |
Posted - 2008-03-26 : 04:53:36
|
| If you are using an existing workbook, then 'Select * from [Sheet1$]' in the openrowset will be defining the table, so if you have more or less column headings in the worksheet then you will get an error.Are you just looking to create a new worksheet, or are you actually wanting to enter data into this specific workbook. If the former then why not use DTS |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-03-26 : 12:18:05
|
| Thanks Rogers,But I am creating the worksheet from the web application and as performance and multi worksheet are the requirements hence I need to use this approach as this way creating the excel spreadsheet is quite fast plus I need to pass dynamic sql as well, so the number of columns may not be the same each time the query is run.I mean e.g for this query to run....there must be a way to NOT include column names in the excel template file and let the excel take the column names from the openrowset query passed....Peso... suggested me an approach i.e. include hdr=yes, but that also gives the same error.INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\pubsTest.xls', 'SELECT * FROM [Sheet1$]')SELECT * FROM pubs.dbo.authors |
|
|
|
gdeep14
Starting Member
1 Post |
Posted - 2008-10-08 : 07:51:57
|
| Hi,Can we exec storeed procedure in -- Executing the OPENROWSET Command for copying the select contents to Excel sheet.exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'') select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')in openrowset commandRgds,Gagan |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-10-09 : 03:17:23
|
quote:
Originally posted by gdeep14
Hi,Can we exec storeed procedure in -- Executing the OPENROWSET Command for copying the select contents to Excel sheet.exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'') select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')in openrowset commandRgds,Gagan
Yes. Do you get any errors?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
OPENROWSET(DO WE HAVE TO GIVE COL NAMES OF SHEET?)