We help IT Professionals succeed at work.

Importing Excel to SQL

Suhul
Suhul asked
on
How can I import an Excel work book(more than one worksheet at a time) to SQL serverusing T-sql?
Comment
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
Commented:
Based mostly on

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24825375.html
-- 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
begin 
-- 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
end

-- 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
Commented:
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

[C:\files\test.xls_Payroll$]

You can view the data in the imported table using

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

Author

Commented:
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'
   WITH
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )
Expert of the Quarter 2010
Expert of the Year 2010

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

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