drop procedure sp_excel_Convert_Excel_To_Table
go

create procedure sp_excel_Convert_Excel_To_Table
(
	@excel_full_file_name 		varchar(666)

	,@convert_to_table_name		varchar(50)

	,@transfer_to_table		bit=1
	,@clear_existing_records_first 	bit=1

	,@good				int	=null output
	,@error_code			int	=null
	,@error_description 		varchar(255) = null output
)
as
	set nocount on
	

	declare @command varchar(8000)


	-- copy excel file under temp and change worksheet name
	set @good=0
	set @error_description = ''
	set @error_code=0

	declare @object int
		,@hr int
		,@src varchar(255)

	exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '1. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=1
		set @good=0
		goto error
	end
	
	exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false'
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '2. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=2
		set @good=0
		goto error
	end

	declare @workbook int
		,@workbook_path_save_as varchar(666)
	set @workbook_path_save_as = 'c:\temp.xls'
	exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@excel_full_file_name
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '3. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=3
		set @good=0
		goto error
	end


	exec @hr = master.dbo.sp_OASetProperty @object, 'Workbooks(1).Worksheets(1).Name','excel_data'
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '4. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=4
		set @good=0
		goto error
	end

	exec @hr = master.dbo.sp_oaMethod @workbook ,'SaveAs',null,@workbook_path_save_as
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '5. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=5
		set @good=0
		goto error
	end

	-- get full file name
	exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).FullName',@workbook_path_save_as output
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '6. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=6
		set @good=0
		goto error
	end

	exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'	--,@workbook out,@workbook_path
	if @hr <> 0
	begin
		exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out 
		set @error_description = '7. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=7
		set @good=0
		goto error
	end

	if @transfer_to_table = 1
	begin
		if @clear_existing_records_first=1 	
		begin
			set @command = 'delete from ' + @convert_to_table_name
			exec (@command)
		end
	
		-- copy records from excel into table:
		set @command = 	'insert into ' + @convert_to_table_name + 
				' select * from ' +
				' OpenRowSet(''MSDASQL'' '+
				', ''Driver={Microsoft Excel Driver (*.xls)};'+
				'DBQ=' + @workbook_path_save_as  +  ''''+
				',''SELECT * FROM [excel_data$]'')'
		print @command
	
		exec (@command)

		set @command = 'select * from ' + @convert_to_table_name 
		exec (@command)	
	end


error:

	select @error_description as 'Error', @error_code as 'Error Code'

	exec @hr = master.dbo.sp_OADestroy @workbook
	if @hr <> 0
	begin
		set @error_description = '7. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=7
		set @good=0
	end

	exec @hr = master.dbo.sp_OADestroy @object
	if @hr <> 0
	begin
		set @error_description = '8. ' + isnull(@error_description,'N/A')
		select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
		set @error_code=8
		set @good=0
	end

go