February 8, 2010 at 10:43 am
I'm trying to use an Excel workbook to create a table for each worksheet. How do I Select the worksheet names using an OpenRowSet?
February 8, 2010 at 11:15 am
Found this which I should be able to adapt nicely from Experts Exchange:
-- 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:\ee\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 @n = table_name from #t where id = 1
-- if it is a real name, then lets try to use it (probably should check for a '$' in the name
if @n is not null
exec ('select * from my_Excel...['+@n+']')
-- now this is where you do whatever you want with the data using the linked server
-- remove server, just to clean up 'nicely'
if exists (select * from sys.servers where name = 'my_Excel')
exec sp_dropserver 'my_Excel', 'droplogins';
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply