DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John Peterson
John,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29...8-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1' ' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogive it)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>
|||Steve Kass <skass@.drew.edu> wrote in message ...
[vbcol=seagreen]

> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.c om...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --
|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.c om...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --
|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google. com...
>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29C76785-22D9-46A2-A398-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...
> > I have a series of Excel spreadsheets, of which
> > I don't know the columns beforehand, that
> > I'd like to import into SQL Server. These spreadsheets
> > are pretty simple in that they'll
> > always have a title row and all the data will be
> > on the primary (first) worksheet.
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
--|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
> > > I have a series of Excel spreadsheets, of which
> > > I don't know the columns beforehand, that
> > > I'd like to import into SQL Server. These spreadsheets
> > > are pretty simple in that they'll
> > > always have a title row and all the data will be
> > > on the primary (first) worksheet.
> > You can do this with an ad hoc query:
> >
> > select * into #abc
> > from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> > 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...
> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
--|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
> > Jamie, in my case, I think that there will
> > only be one Worksheet that I need to worry
> > about -- so I'm hoping that determining which
> > is the "first" sheet won't be a problem.
> > Though, if I understand your concern correctly
> > -- it seems like I need to know the *name*
> > of that worksheet beforehand in the OPENQUERY?
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:
>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>>"John Peterson" wrote ...
>>
>>Jamie, in my case, I think that there will
>>only be one Worksheet that I need to worry
>>about -- so I'm hoping that determining which
>>is the "first" sheet won't be a problem.
>>Though, if I understand your concern correctly
>>-- it seems like I need to know the *name*
>>of that worksheet beforehand in the OPENQUERY?
>>
>>Correct, unless you are using a defined Name ('named range') in the
>>Excel workbook in which case you'd need to know the Name's name.
>>Jamie.
>>--
>>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns bef
orehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in
that they'll
always have a title row and all the data will be on the primary (first) work
sheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one
with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the T
ransform Data
Task on a specific spreadsheet basis. However, I'd like to make the package
dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet colum
ns (is this
even possible?), create a destination table, and squirt the data into the ta
ble.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=2...98-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheon
e')
set @.sql = replace(@.sql,'##table##','thisisthetable
nametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns be
forehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple i
n that they'll
>always have a title row and all the data will be on the primary (first) wor
ksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-on
e with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the
Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the packag
e dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet colu
mns (is this
>even possible?), create a destination table, and squirt the data into the t
able.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...

[vbcol=seagreen]
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server?
I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need
to worry
about -- so I'm hoping that determining which is the "first" sheet won't be
a problem.
Though, if I understand your concern correctly -- it seems like I need to kn
ow the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.|||I'm still at kind of an impasse with this. I had thought that Steve's sugge
stion might
work for me, but there are issues with OPENROWSET that are kind of stymieing
me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to i
dentify all
the columns that are "in use" and create Transformations for them, maybe I c
an do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's sugg
estion might
>work for me, but there are issues with OPENROWSET that are kind of stymiein
g me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to
identify all
>the columns that are "in use" and create Transformations for them, maybe I
can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>
>

DTS: How to connect to hundreds of DBs dynamically

I need to release a few triggers and procedures to hundreds of databases
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>