September 17, 2013 at 12:16 pm
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
September 17, 2013 at 6:43 pm
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