Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool

Siccolo Development Articles - Handling Currency, SQL Development, SQL Server scripts and SQL Reporting
Google
Main
Create Stored procedure to convert Excel file into SQL table using OLE Automation and T-SQL
 
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

	
Side Note:
OLE Automation Objects in Transact-SQL
The OLE Automation - T-SQL includes several system stored procedures that allow OLE Automation objects to be referenced in Transact-SQL batches, stored procedures, and triggers. . These system stored procedures run as extended stored procedures, and the OLE Automation objects that are executed through the stored procedures run in the Microsoft® SQL Server™ address space in the same way that an extended stored procedure runs. The OLE Automation stored procedures allow Transact-SQL batches to reference SQL DMO objects and custom OLE Automation objects, such as objects that expose the IDispatch interface. A custom in-process OLE server created using Microsoft Visual Basic® must have an error handler (specified with the On Error GoTo statement) for the Class_Initialize and Class_Terminate subroutines. The error handlers prevent unhandled errors from occurring in these subroutines. Unhandled errors in the Class_Initialize and Class_Terminate subroutines can cause unpredictable errors, such as an SQL Server access violation. Error handlers for other subroutines are also recommended.
The first step when using an OLE Automation object in Transact-SQL is to call the sp_OACreate system stored procedure to create an instance of the object in the SQL Server address space.
    After an instance of the object has been created, call these stored procedures to work with the properties, methods, and error information related to the object:
  • sp_OAGetProperty obtains the value of a property.
  • sp_OASetProperty sets the value of a property.
  • sp_OAMethod calls a method.
  • sp_OAGetErrorInfo obtains the most recent error information.

When there is no more need for the object, call sp_OADestroy to deallocate the instance of the object created with sp_OACreate. OLE Automation objects return data through property values and methods. sp_OAGetProperty and sp_OAMethod return these data values in the form of a result set. The scope of an OLE Automation object is a batch. All references to the object must be contained in a single batch, stored procedure, or trigger.


for more OLE Automation samples see article How to Print a Document from SQL Stored Procedure using OLE Automation and T-SQL

Article keywords: OLE Automation, SQL Server, T-SQL, create procedure sp_excel_Convert_Excel_To_Table, sp_OACreate, sp_OAGetErrorInfo, sp_OASetProperty, sp_oaMethod, Excel.Application, WorkBooks.Open, WorkBook.SaveAs


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.