Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

With OLE Automation how to Transfer data from Excel to SQL Table Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:15 AM
Points: 8, Visits: 147
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
Post #1495623
Posted Tuesday, September 17, 2013 6:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
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
Post #1495733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse