Export to excel - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Export to excel

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-24 : 07:26:11

mark writes "I would like to be able to call a stored procedure that would contain code to export the contents of a SQL statement. Do you have any thoughts on how to do this?

Thanks

Mark"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-24 : 07:46:28

well, you could code a DTSpackage to do this, then exec an xp_cmdshell DTSRun of the package from a stored procedure ... but that's a buit of a kludge.

maybe you can figure out a way with OPENDATASOURCE ... I don't know if it is capable of inserting data. Below is an example of code that select's from an excel spreadsheet. Good Luck


if exists (select 1 from sysobjects where name = 'usp_Import_ExcelFile_TempTable' and xtype = 'p')
	drop procedure usp_Import_ExcelFile_TempTable

set nocount on

go

create procedure usp_Import_ExcelFile_TempTable
@filename varchar(250),
@sheetname varchar(100) = 'Sheet1$',
@succeed int OUTPUT
as
begin
set @succeed = -1 --failure
set nocount on
if exists (select 1 from sysobjects where name = 'excel_data_inserted' and xtype = 'u')
	drop table dbo.excel_data_inserted
declare @sql		varchar(500)
declare @filexists	int

set @sql = 'SELECT * INTO dbo.excel_data_inserted FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
	  ''Data Source="'+@filename+'";Extended properties=Excel 8.0'')...['+@sheetname+']'
exec master..xp_fileexist @filename, @filexists OUT
if (@filexists <> 1)
	print 'No file'
else
	begin
	set @succeed = 0 --success
--	print @succeed
--	print @sql
	exec (@sql)
	end
end

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

faithful
Starting Member

1 Post

Posted - 2004-06-24 : 08:50:06

Thanks Wanderer.

That looks very helpful.

Mark

_______________

The time is now
_______________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources