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

Question

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

Answer 1

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.

Answer 2

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$]')

Answer 3

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

Answer 4

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

Answer 5

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/

Answer 6

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?