Import podataka iz Excel fajla u MS SQL Server tabelu

Veoma često postoji poslovna potreba za importom podataka iz Excel fajlova u MS SQL tabele. Najčešće se takav import podataka realizuje korišćenjem funkcionalnosti Microsoft SQL Server Management Studia ili kreiranjem SSIS paketa. Obično se zaboravlja da se import podataka iz Excel fajla može realizovati i Transact SQL-om.

U primeru koda 1 je data stored procedura za import podataka iz Excel fajla u verziji Excel 2003.

--sp_ImportFromExcel_03 'Sheet1$', 'C:\TestImportExcel03.xls', 'YES', 't_TestImportFromExcel_03'
CREATE PROCEDURE sp_ImportFromExcel_03
	@FilePath varchar(250)
	,@SheetName varchar(50)
	,@HDR varchar(3)
	,@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)

	IF OBJECT_ID (@TableName,'U') IS NOT NULL
		SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
	ELSE
		SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

    SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='
	SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
	EXEC sp_executesql @SQL
END
GO

Code 1. Stored procedures za iport podataka iz Excel fajla (Excel 2003)

Za import podataka iz Excel fajla verzije Excel 2003 se koristi OLEDB provajder Microsoft.Jet.OLEDB.4.0.

U primeru koda 2 je data stored procedura za import podataka iz Excel fajla u verziji Excel 2007/2010/2013.

--sp_ImportFromExcel_07 'Sheet1$', 'C:\TestImportExcel07.xlsx', 'YES', 't_TestImportFromExcel_07'
CREATE PROCEDURE sp_ImportFromExcel
	@FilePath varchar(250)
	,@SheetName varchar(50)
	,@HDR varchar(3)
	,@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)

	IF OBJECT_ID (@TableName,'U') IS NOT NULL
		SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
	ELSE
		SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
	EXEC sp_executesql @SQL
END
GO

Code 2. Stored procedures za iport podataka iz Excel fajla (Excel 2007/2010/2013)

Za import podataka iz Excel fajla verzije Excel 2007/2010/2013 se koristi OLEDB provajder Microsoft.ACE.OLEDB.12.0.

Parametar Connection stringa HDR=YES kaže da prvi red konkretnog Excel Sheet-a sadrži nazive kolona.

Ako se želi da sve kolone budu tretirane kao tekst kolone onda treba u Connection String dodati i parametar IMEX=1. Na primer ako su u prvom redu konkretnog Sheet-a numerički podaci a želi se da se upotrebe kao imena kolona (HDR=YES) onda je neophodno u Connection string dodati i IMEX=1 da import proces ne bi javio grešku.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestImportExcel07.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Code 3. Sve kolone u Excel fajlu se tretiraju kao tekst kolone

Ako je u pitanju Excel fajl sa ekstenzijom .xslb (binarni Excel fajl) onda je neophodno da Connection string ima strukturu koja je data u primeru koda 4.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestImportBinaryExcel2007.xlsb;Extended Properties="Excel 12.0;HDR=YES";

Code 4. Import binarnog Excel fajla

Ako je potrebno importovati podatke iz Excel fajla sa enable-ovanim macro-om (ekstenzija .xlsm) onda Connection string treba da ima strukturu koja je data u primeru koda 5.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestImportExcel2007_Macro_Enabled.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

Code 5. Import Excel fajla sa enable-ovanim macro-om

Leave a Reply

%d bloggers like this: