We help IT Professionals succeed at work.

Importing Excel to SQL

Suhul asked
How can I import an Excel work book(more than one worksheet at a time) to SQL serverusing T-sql?
Watch Question

you have quite a few options:

1.) Linked Server: http://www.sqlhub.com/2009/04/create-linked-server-with-excel-2007.html
2.) OpenRowSet: http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
3.) Import/Export wizard or SSIS:
Expert of the Quarter 2010
Expert of the Year 2010
Based mostly on

-- check to see if we need to remove server, because we create it dynamically
if exists (select * from sys.servers where name = 'my_Excel')
   exec sp_dropserver 'my_Excel', 'droplogins';
-- our spreadsheet name goes here
declare @file varchar(200)
set @file = 'c:\test.xls'
-- now create the linked server for that spreadsheet
exec ('EXEC sp_addlinkedserver my_Excel, ''Jet 4.0'',''Microsoft.Jet.OLEDB.4.0'','''+@file+''', NULL, ''Excel 5.0;''')
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin my_Excel, FALSE, NULL, Admin, NULL
-- we will create a temp table to store the worksheets names
if object_id('tempdb..#t','u') is not null drop table #t
create table #t (id int identity, table_cat varchar(100),table_schem varchar(100),table_name varchar(100),table_type varchar(100),remarks varchar(100))
--List the tables in the linked server (these are the worksheet names).
insert #t
EXEC sp_tables_ex my_Excel
select * from #t
-- now we will get the worksheet name we want to use based on "index" - well id actually...
declare @n varchar(100)
select top(1) @n=table_name from #t
while @n is not null
-- if it is a real name, then we'll attempt to create a table by the same name
if @n is not null
   exec ('select * into ['+@file+'_'+@n+'] from my_Excel...['+@n+']')
delete from #t where table_name = @n
set @n = null
select top(1) @n=table_name from #t

-- remove server, just to clean up 'nicely'
if exists (select * from sys.servers where name = 'my_Excel')
   exec sp_dropserver 'my_Excel', 'droplogins';

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
If you don't want to see the sheet names it has imported, comment out line 23, e.g.

--select * from #t

The tables are created using this naming convention

filename_sheetname$, e.g. if the file is C:\files\test.xls and the sheet is named "Payroll", then the table will be imported as


You can view the data in the imported table using

select * from [C:\files\test.xls_Payroll$]


that was very helpful....thank you
if you are going to use different excel files all the time, it is good to go with OPENROWSET rather than LinkedServer
Use this:

 BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.xls'
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
Expert of the Quarter 2010
Expert of the Year 2010

I don't know how to import all sheets without knowing their names using OPENROWSET.  Do you?
Steve WalesSenior Database Administrator

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.