With OLE Automation how to Transfer data from Excel to SQL Table

  • Hi,

    My code works fine for copy from one sheet to another and then saving a new Excel file or CSV.

    My real problem is how to copy ranges from Excel to an SQL Table with OLE Automation.

    --

    --

    DECLARE @Object AS INT

    DECLARE @HResult AS INT

    DECLARE @HResult_OUT AS INT

    DECLARE @File AS INT

    DECLARE @FileSize AS INT

    DECLARE @NetworkObject AS INT

    DECLARE @FileSystemObject AS INT

    DECLARE @Workbooks AS INT

    DECLARE @Worksheets AS INT

    DECLARE @SheetsORG AS INT

    DECLARE @SheetsDST AS INT

    DECLARE @Ranges AS INT

    DECLARE @PATH AS NVARCHAR(128)

    DECLARE @Nome AS VARCHAR(60)

    DECLARE @xlExcel12 AS INT

    DECLARE @xlLocalSessionChanges AS INT

    SET @xlExcel12 = 50

    SET @xlLocalSessionChanges = 2

    --

    --Criar uma instancia do objecto Excel.Application

    --

    EXEC @HResult = sp_OACreate 'Excel.Application', @Object OUT

    PRINT 'Create Excel.Application: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Suprimir quaisquer alertas

    --

    EXEC @HResult = sp_OASetProperty @Object, 'DisplayAlerts', 'false'

    PRINT 'Not To DisplayAlerts: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Ficheiro de Teste

    --

    SET @PATH = N'C:\DPC_IRP\20130914_EXT_DETALHE.csv'

    --

    --Obter um objecto do tipo collection de Workbooks associado ao ficheiro de teste

    --

    EXEC @HResult = sp_OAMethod @Object, 'Workbooks.Open', @Workbooks OUT, @PATH

    PRINT 'Workbooks Object: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Ativar o 1º Woorkbook

    --

    EXEC @HResult = sp_OAMethod @Workbooks, 'Activate', NULL

    PRINT 'Activate Workbooks(1): ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Ativar a 1ª sheet

    --

    EXEC @HResult = sp_OAMethod @Workbooks, 'Sheets.Item(1)', @SheetsORG OUTPUT

    PRINT 'Sheets Object: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    EXEC @HResult = sp_OAMethod @Workbooks, 'Sheets.Item(2)', @SheetsDST OUTPUT

    PRINT 'Sheets Object: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    EXEC @HResult = sp_OAMethod @SheetsORG, 'Copy', NULL, @before=NULL, @after=@SheetsDST

    PRINT 'Sheets Object: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    SET @PATH = N'C:\DPC_IRP\Teste_SQL_vs_EXCEL_COPIA_PP.csv'

    EXEC @HResult = sp_OAMethod @Workbooks, 'SaveAs', NULL, @Filename=@PATH

    PRINT 'SaveAs Workbook: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Fechar o Workbook

    --

    EXEC @HResult = sp_OAMethod @Workbooks, 'Close', null

    PRINT 'Close Workbook: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Ativar novamente a produção de alertas para o objecto Excel.Application

    --

    EXEC @HResult = sp_OASetProperty @Object, 'DisplayAlerts', 'True'

    PRINT 'Yes To DisplayAlerts: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    --

    --Destruir o objecto Excel.Application

    --

    EXEC @HResult = sp_OAMethod @Object , 'Application.Quit'

    PRINT 'Quit Excel.Apllication: ' + BD_GESTAOKPI.dbo.ufn_HexToChar(@HResult)

    My example copy's from one sheet to another, but I need to copy from Excel to SQL Tables.

    Thanks

  • To read range from Excel in SSIS you can refer this:

    SSIS: Read single cell values in excel using RANGE name

    Vikash Kumar Singh || www.singhvikash.in

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply