CODEWITHSUNDEEP
sql-servert-sqlxlsx
aaryan
aaryan

Reputation: 277

Code to read xlsx sheet into a table in a SQL Server database

I am trying to read data from an Excel sheet (.xlsx file) into a table in SQL Server 2008. I want this to be run everyday as a batch job and hence want to write SQL code in a stored procedure to do so.

Could someone help me? I have admin rights.

~TIA

Upvotes: 24

Views: 124050

Answers (6)

kiri
kiri

Reputation: 356

This should do... 

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=NO;Database=T:\temp\Test.xlsx',
    'select * from [sheet1$]')

But we aware, sometimes this just wont work. I had this working for local admins only.

There is a way to do this using SSIS as well.

Upvotes: 34

Spyder
Spyder

Reputation: 4032

Just another quick alternative

If you don't need to import your Excel file programmatically using code you can do it very quickly manually using the menu in SQL Management Studio:

How to import an Excel file into SQL Server?

Upvotes: 0

Jitendra Sawant
Jitendra Sawant

Reputation: 698

To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from HERE

Import data from Excel to new SQL Server table Excel 2003 (.Xls) file:

select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to existing SQL Server table Excel 2003 (.Xls) file:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Upvotes: 6

Jaye
Jaye

Reputation: 152

Borrow from GridWizard https://gridwizard.wordpress.com/2014/12/23/tsql-utility-to-read-csv-and-excel-files/

/*
Usage:
exec sp_ReadExcelFile ‘C:\tmp’, ‘EmpData’, ‘EmpData.xls’, ‘[Sheet1$]’, ‘Col1,Col2,Col3,Col4,Col5′
*/
CREATE PROCEDURE sp_ReadExcelFile
(
@TargetFolder varchar(255), /* For example, ‘C:\tmp’ */
@TargetTable varchar(255), /* For example, ‘EmpData’ */
@ExcelFile varchar(255), /* For example, ‘EmpData.xls’ */
@ExcelSheet varchar(255), /* For example, ‘[Sheet1$]’ */
@ExcelFields varchar(8000) /* Comma separate list, for example: ‘Col1,Col2,Col3,Col4,Col5′ */
)
AS
BEGIN
declare @SqlStmt nvarchar(max)
declare @FirstColumn nvarchar(255)

set @SqlStmt = ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TargetTable + ‘]”) AND type in (N”U”))’
set @SqlStmt = @SqlStmt + ‘ DROP TABLE ‘ + @TargetTable
PRINT @SqlStmt
exec sp_executesql @SqlStmt

select top 1 @FirstColumn=[part] from fn_SplitString(@ExcelFields, ‘,’)
print ‘FirstColumn: ‘ + @FirstColumn

set @SqlStmt = ‘SELECT ‘ + @ExcelFields + ‘ INTO ‘ + @TargetTable + ‘ FROM OPENROWSET(”Microsoft.ACE.OLEDB.12.0”,
”Excel 12.0;DATABASE=’ + @TargetFolder + ‘\’ + @ExcelFile + ‘;IMEX=1”, ”Select * from ‘ + @ExcelSheet + ”’)’
+ ‘WHERE NOT ‘ + @FirstColumn + ‘ IS NULL’
PRINT @SqlStmt
exec sp_executesql @SqlStmt
END
GO

/*
Taken from: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql
Usage: select top 1 part from fn_SplitString(‘aaa,bbb,ccc,ddd,eee’,’,’)
*/
CREATE FUNCTION [dbo].[fn_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
@iPos int
if substring( @sString, 1, 1 ) = @cDelimiter
begin
set @iStart = 2
insert into @tParts
values( null )
end
else
set @iStart = 1
while 1=1
begin
set @iPos = charindex( @cDelimiter, @sString, @iStart )
if @iPos = 0
set @iPos = len( @sString )+1
if @iPos – @iStart > 0
insert into @tParts
values ( substring( @sString, @iStart, @iPos-@iStart ))
else
insert into @tParts
values( null )
set @iStart = @iPos+1
if @iStart > len( @sString )
break
end
RETURN
END
GO

Upvotes: 1

Jerrad
Jerrad

Reputation: 5290

The easiest way is probably the Import/Export Wizard in SQL Server Management Studio. Here is one site that explains how: http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

Right click on the database -> Tasks -> Import Data... Go through the wizard.

Upvotes: 0

Related Questions