Hi
I am trying to load an Excel file into a SQL Server
table. This file contains number of sheets. I am asked to
pick up only sheets that have the name 'sheet1...sheet30'.
There may be also some other sheets. (likily the 30 is
constant)..
Please bear with me if this has been answered already.
Is there a way i can pickup only those sheets and load
them into SQL Server..
Thanks for your help
l

Sheets AFAIK are not really what you import rather it is the named ranges.
within them. Granted a lot of the time those names are the sme as the
sheet.

You could use a Linked server details here (from BOL)

To create a linked server against an Excel spreadsheet:

The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft
Excel spreadsheets.

a.. To create a linked server that accesses an Excel spreadsheet, use the
format of this example.
sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO
To access data from an Excel spreadsheet, associate a range of cells with a
name. A named range can be accessed by using the name of the range as the
table name. The following query can be used to access a named range called
SalesData using the linked server set up in the previous example. SELECT *
FROM EXCEL...SalesData
GO
You could loop through from 1 to 30 and dynamically populate the
SourceObjectName for the DataPump task.You could also load them up through
automation but that is not all that desirable.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Leo" <leo_victor (AT) yahoo (DOT) com> wrote