4

I am successfully exporting to excel with the following statement:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\template.xls;', 
'SELECT * FROM [SheetName$]') 
select * from myTable

Is there any standard way to use this template specifying a new name for the excel sheet so that the template never gets written to or do I have to come up with some work-around?

What's the best way to do this in people experience?

shA.t
  • 16,580
  • 5
  • 54
  • 111
JohnIdol
  • 48,899
  • 61
  • 158
  • 242
  • Oh lucky man. I've been already spending 2 hours trying to run this and getting all kind of errors. (using 2008SP2) – iDevlop Oct 06 '15 at 14:24

3 Answers3

7

You'd have to use dynamic SQL. OPENROWSET etc only allows literals as parameters.

DECLARE @myfile varchar(800)

SET @myfile = 'C:\template.xls'

EXEC ('
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=' + @myfile + ';'', 
''SELECT * FROM [SheetName$]'') 
select * from myTable
')

Remember: the path is relative to where SQL Server is running

shA.t
  • 16,580
  • 5
  • 54
  • 111
gbn
  • 422,506
  • 82
  • 585
  • 676
  • this is good - but I'd still have to put the template there in the first place so I cannot come up with names in the SQL, I'd have to programmatically create the spreadsheet first. – JohnIdol May 26 '09 at 12:01
  • Ah I get you. No, you can't manipulate Excel that way in T-SQL. Perhaps a CLR stored proc? – gbn May 26 '09 at 13:19
  • never worked with CLR SPs - but I guess it's easy enough - any good sample to get me going? – JohnIdol May 26 '09 at 14:02
  • Sorry... not used them. The "SQLCLR" tag has 31 related questions. – gbn May 26 '09 at 14:12
  • I'll clarify.. our corporate build from DB engineering says no – gbn May 26 '09 at 19:50
  • I'm following the same but getting this error "The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered." I have tried to change the OLEDB version also. – Sunil Acharya Aug 24 '18 at 13:21
1

Couldn't you make a copy of your template first, then pass the copy's filename into OPENROWSET?

Seth Ladd
  • 112,095
  • 66
  • 196
  • 279
0

You can use a template in one location and the data file in other location. When you run the script , it will delete the old file and generates a new data file.

 EXEC xp_cmdshell 'del D:\template.xls'
 EXEC xp_cmdshell 'copy C:\template.xls D:\template.xls'

 INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
 'Excel 8.0;Database=D:\template.xls;', 
 'SELECT * FROM [SheetName$]') 
  SELECT * FROM myTable
Tharindu
  • 91
  • 4