dup check while import from excel

There have been some pretty advanced stuff here I've gone through, but I am
a
.NET programmer by trade, so T-SQL is a bit different for me.
I have an outside list of items with ID codes that I need to import to an
existing table, but I need to have the procedure check for dups in the excel
list BEFORE inserting them, if the ID already exists in the database, then
skip and move onto the next one.
(See the OOP concept here?)
I was thinking about something along the line of an If Exist followed by a
skip record or insert, but I think I am making this too complicated.
can someone give me a down-to-earth example of how to accomplish this?
Environment:
From: Excel File named InItem
ID field: ItemID
to:
SQL Database named dbo.tblInItem field ItemID
Thank you for any assistance...
-Tobias Mazzei
Wraith SystemsI prefer adding a linked server to the excel file (look up sp_addlinkedserve
r
in Books Online) to bring the two data sources to common ground.
Building an INSERT query is pretty simple from there:
insert <SQL_table>
(
<destination_column_list>
)
select <source_column_list>
from <Excel_table>
where (not exists (
..the excluding query is
dependent on the actual data
..basically you need to list
items that already exist in the destination table
))
For better help, please post DDL and sample data.
ML|||This is ussually how I check if a record already exsists, you can change the
creteria
IF EXISTS (SELECT 'True' FROM tb_Alerts WHERE ID = @.AlertID)
BEGIN
UPDATE tb_Alerts
SET AlertName = @.AlertName
WHERE ID = @.AlertID;
SELECT @.AlertID;
END
ELSE
BEGIN
INSERT INTO tb_Alerts (AlertName)
VALUES (@.AlertName)
SELECT @.@.IDENTITY;
END
-Mark
"Wraith Systems" <WraithSystems@.discussions.microsoft.com> wrote in message
news:AD088624-7F0F-4F88-BD8C-5C0AE09F754D@.microsoft.com...
> There have been some pretty advanced stuff here I've gone through, but I
> am a
> .NET programmer by trade, so T-SQL is a bit different for me.
> I have an outside list of items with ID codes that I need to import to an
> existing table, but I need to have the procedure check for dups in the
> excel
> list BEFORE inserting them, if the ID already exists in the database, then
> skip and move onto the next one.
> (See the OOP concept here?)
> I was thinking about something along the line of an If Exist followed by a
> skip record or insert, but I think I am making this too complicated.
> can someone give me a down-to-earth example of how to accomplish this?
> Environment:
> From: Excel File named InItem
> ID field: ItemID
> to:
> SQL Database named dbo.tblInItem field ItemID
> Thank you for any assistance...
> --
> -Tobias Mazzei
> Wraith Systems
>|||I am getting closer. Thanks for the response, I tried looking up "not exists
"
in the help files, and with the lack of definition, it brought me here.
where (not exist (exceltableID=SQLtableID))
This was my initial concept, but it didn't look like SQL understood what I
was trying to tell it, the words not and exists were greyed.
So the concept was to select the items where the SQLtable did not have (or
equal to) what was the current record the datareader was at, right?
is this accurate useage? and can this be done in a stored procedure?
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> I prefer adding a linked server to the excel file (look up sp_addlinkedser
ver
> in Books Online) to bring the two data sources to common ground.
> Building an INSERT query is pretty simple from there:
> insert <SQL_table>
> (
> <destination_column_list>
> )
> select <source_column_list>
> from <Excel_table>
> where (not exists (
> ...the excluding query is
> dependent on the actual data
> ...basically you need to list
> items that already exist in the destination table
> ))
>
> For better help, please post DDL and sample data.
>
> ML|||On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:

>I am getting closer. Thanks for the response, I tried looking up "not exist
s"
>in the help files, and with the lack of definition, it brought me here.
>where (not exist (exceltableID=SQLtableID))
>This was my initial concept, but it didn't look like SQL understood what I
>was trying to tell it, the words not and exists were greyed.
>So the concept was to select the items where the SQLtable did not have (or
>equal to) what was the current record the datareader was at, right?
>is this accurate useage? and can this be done in a stored procedure?
Hi Tobias,
No, this is not a correct query. The argument to an EXISTS clause should
be a subquery. And in this case, the subquery should be correlated to
the main query.
Try adapting the code below to your tables:
INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT *
FROM DestTable AS d
WHERE d.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. I think Microsoft finally answered the question as well, but
using a <not in> statement. This seems to work as well. If there are
performance factors, I have not found them yet, but I am only inserting
around 1,000 items at a time per batch.
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> No, this is not a correct query. The argument to an EXISTS clause should
> be a subquery. And in this case, the subquery should be correlated to
> the main query.
> Try adapting the code below to your tables:
> INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
> SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
> FROM SourceTable AS s
> WHERE NOT EXISTS (SELECT *
> FROM DestTable AS d
> WHERE d.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:

>Thank you Hugo. I think Microsoft finally answered the question as well, bu
t
>using a <not in> statement. This seems to work as well. If there are
>performance factors, I have not found them yet, but I am only inserting
>around 1,000 items at a time per batch.
Hi Tobias,
There are two things to consider when you use NOT IN with a subquery:
1. If one of the rows in the subquery returns a NULL, the NOT IN will
never evaluate to True. You have to make sure that the subquery can't
return NULLs, else your query will fail.
If you use NOT EXISTS, then you don't have this problem.
2. The NOT IN can only be used if you have a sigle-key column. For a
multi-key column, NOT IN can't be used, since SQL Server doesn't
implement the ANSI standard row constructor.
With NOT EXISTS, the number of columns in the key doesn't matter.
For these reasons, I recommend to always rewrite NOT IN with a subquery
to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is what I am using currently, but based on your responses, I anticipate
issues.
Insert Into <server>.tblInItemAddDescr
select ItemId,AddlDescr,ts
from xclItemAddDescr...[tblInItemAddDescr$]
where itemid not in(select itemid from <server>.tblInItemAddDescr)
go
I am not sure what multi-key vs. single-key would be considered, would you
be able to give a bit more detail on that?
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> There are two things to consider when you use NOT IN with a subquery:
> 1. If one of the rows in the subquery returns a NULL, the NOT IN will
> never evaluate to True. You have to make sure that the subquery can't
> return NULLs, else your query will fail.
> If you use NOT EXISTS, then you don't have this problem.
> 2. The NOT IN can only be used if you have a sigle-key column. For a
> multi-key column, NOT IN can't be used, since SQL Server doesn't
> implement the ANSI standard row constructor.
> With NOT EXISTS, the number of columns in the key doesn't matter.
> For these reasons, I recommend to always rewrite NOT IN with a subquery
> to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
> in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 16:27:57 -0800, Wraith Systems wrote:

>Here is what I am using currently, but based on your responses, I anticipat
e
>issues.
>Insert Into <server>.tblInItemAddDescr
>select ItemId,AddlDescr,ts
>from xclItemAddDescr...[tblInItemAddDescr$]
>where itemid not in(select itemid from <server>.tblInItemAddDescr)
>go
Hi Tobias,
If the column itemid in <server>.tblInItemAddDescr can't be NULL, then
you won't have any issued with this. You couls still compare it to the
corresponding NOT EXISTS version to test for performance differences,
though.
OTOH, if the itemid column can hold NULLs, then you *will* have issues.

>I am not sure what multi-key vs. single-key would be considered, would you
>be able to give a bit more detail on that?
My bad. I messed up when typping. I meant multi-column vs single-column
keys.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dump Excel Sheets

I need to make a gigantic collection of excel sheets searchable from a web interface.
I needentire rows fetched into a webpage depending on the users query.

Mind you I'm not searching the names (filenames) of these excel files, but
the contents inside the excel file. E.g. in a file test.xls, if I search for the word
"test1" from the web interface, the entire row in the excel file containing
the word test1 should be displayed.

One way out is to dump the contents of the excel sheets into a database, and
search the database via ASP.

For this, can anyone tell me how to efficiently dump the contents of an excel
sheet into SQL server?

Or if anyone can suggest an alternate strategy for searching this mammoth
collection of excel files' contents, if would be great.

Thanks a lot.

To dump excel data into database, you have serveral options: DTS (Import/Export Wizard), bcp utility, BULK INSERT, etc. Seach with these key words in SQL Books Online, you can find how to use them.

Dummy Question

It seems every month when I'm diluged by lots of data I fall back on ole
reliable, Excel. But, I get over 50,000 rows of data to scrub. A colleague
of mine suggested I use a database. Seems simple so far, but having dabbled
in Access, it has always not so intuitive to understand. My question is
two-fold, is SQL a database. I believe its the language of some other
database. If I'm corrent on the later, what database(s) use SQL?

TonyTony,

There's a lot of ground to cover with that one simple question :) SQL
is an abbreviation for Structured Query Language, and it's a language
that is used primarily to retrieve and manipulate data that is stored
in a database system.

There are as many dialects of SQL as there are databases; Access uses
Jet-SQL, SQL Server and Sybase both use Transact-SQL, Oracle has
PL/SQL, and so on. Most databases adhere to a form of generic SQL know
as ANSI-SQL, but there adherance varies.

You may want to start here:
http://en.wikipedia.org/wiki/SQL

HTH,
Stu

ajocius wrote:
> It seems every month when I'm diluged by lots of data I fall back on ole
> reliable, Excel. But, I get over 50,000 rows of data to scrub. A colleague
> of mine suggested I use a database. Seems simple so far, but having dabbled
> in Access, it has always not so intuitive to understand. My question is
> two-fold, is SQL a database. I believe its the language of some other
> database. If I'm corrent on the later, what database(s) use SQL?
> Tony

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: EXCEL TO SQL: Returns an empty recodset

Hi there,

I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.

The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.

This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.

Any kind of input is welcome

Thanx
/bs26Originally posted by bs26
Hi there,

I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.

The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.

This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.

Any kind of input is welcome

Thanx
/bs26

It sounds as if you are experiencing a locking problem with the two Transform Data tasks. I have had similar problems with importing and exporting to Access databases.

Try it with only one TDT and see if it fixes it.

If you need the additional speed of multiple TDT's, you might try splitting up or duplicating the data over several excel workbooks.

DTS: excel to sql server

I posted this earlier, but it did not seem to get any views. Please help!

I'm trying to import an excel spreadsheet into SQL Server. I keep getting an error saying that 2 columns cannot except NULL values. Here's the setup of each file.

SQL TBL:
My table has 9 columns. the first column is a Primary Key and IDENTITY(1,1). My last column is a dateCreated column with a default of getDate(). Both values are set to NOT NULL. The 7 inside columns are just plain varChar datatypes.

XLS:
My spreadsheet has 7 columns which corelate to the 7 inside columns of my SQL TBL. I do not want to specify my first and last columns because they should be unique to the SQL TBL (identity and getDate()).

Any help with how to do this would be great. If you need more info, please let me know.

Thanks,
TimTim:

If this table is already full of data, edit the DTS package Transformation properties to NOT Copy to EITHER your PK or Date field.

Otherwise, if the table's empty, drop the PK & Date fields for the initial import THEN re-add them back.

Hope this helps

RobbieD

DTS: Data from Excel to Table

I'm having some trouble with import data from an excel document to a table. The scenario is as follows:
The excel document has two columns A and B. Col A has to be copied into the table (table has 3 columns Col 1 - Primay key with autoincrement feature, Col 2 - is Col A from the Excel document, Col 3 - irrelevant)

Depending on Col B from excel doc the corresponding Col A data should be copied to the table. I tired using a DTS pacakage to do this but it complains becuase Col 1 (table) gets a valus of NULL for every value in Col A. I mapped only Col A to Col 2 in the Transformaton tab.

Any ideas? Seems like a simple job?!?

Thanks for any inputWhy not just DTS it in to a work table, the use sql to do what you want to do?

It'll be easier and more powerful.|||Originally posted by vmlal
I'm having some trouble with import data from an excel document to a table. The scenario is as follows:
The excel document has two columns A and B. Col A has to be copied into the table (table has 3 columns Col 1 - Primay key with autoincrement feature, Col 2 - is Col A from the Excel document, Col 3 - irrelevant)

Depending on Col B from excel doc the corresponding Col A data should be copied to the table. I tired using a DTS pacakage to do this but it complains becuase Col 1 (table) gets a valus of NULL for every value in Col A. I mapped only Col A to Col 2 in the Transformaton tab.

Any ideas? Seems like a simple job?!?

Thanks for any input

I had that idea, I was just looking for another idea involving some ActiveX script while Transformation columns.

Thanks newayz|||Originally posted by vmlal
I had that idea, I was just looking for another idea involving some ActiveX script while Transformation columns.

Thanks newayz

It just slows down the transfer in my experience, because it has to handle a row at a time...

If you use sql, you can perform set operation, which will be faster in the long run.

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.
Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>
|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napísal v správe
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
>> hour. I created DTS (Data Transformation Services) packages by wizard.
>> It works fine first time but when DTS runs next time it ends with error:
>> table 'xxxx' already exist. How to manage the overwrite of this Excel
>> Sheet 'xxxx' every hour automatically?
>>
>> Thanks,
>> Martin.
>>
>

DTS, Excel, and ActiveX

Hi-
I have an excel spreadsheet that I want to poulate with data via DTS. What
I need is the previous data to be cleared from the spreadsheet starting
with the second row(The first row is going to be column headers). Can that
be accomplished via an ActiveX? Any help is appreciated.Hi
You could either start with a template file without any data and then
copy/create the final data file before you start see
http://www.sqldts.com/default.aspx?292 for help.
Alternatively this may help to delete the whole sheet
http://www.sqldts.com/default.aspx?245
If not then issue a SQL delete statement againsta range in your sheet.
John
"Jeff York" wrote:

> Hi-
> I have an excel spreadsheet that I want to poulate with data via DTS. Wha
t
> I need is the previous data to be cleared from the spreadsheet starting
> with the second row(The first row is going to be column headers). Can tha
t
> be accomplished via an ActiveX? Any help is appreciated.

DTS will not send attachment using sp_send_cdosysmail

I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
> > I have created a DTS that exports the results of a query to an excel
> > sheet and the delivers the xls to a group of users.
> > The DTS will send the the email but not the attachment; however, if I
> > execute the same statement using QA, the attachment works fine. I have
> > the DTS authenticating to the location of the file using xp_cmdshell
> > earlier in the process.
> > Below is the SQL statement, I am using:
> > DECLARE @.Body varchar(4000)
> > DECLARE @.EmailUsers VARCHAR(1024)
> > DECLARE @.Attmt VARCHAR(1024)
> > SELECT @.Body = 'Attached is the most recent CustCD customer
> > listing.'
> > SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> > FROM EmailUsers
> > WHERE (CustCD = 1)
> > SELECT @.Attmt='\\Server1\C$\c3.xls'
> > EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> > XLS',@.Body, @.Attmt
> >
> > Does anyone have any ideas?
> > Thanks,
> > Chris
> >
> >

DTS vs Excel numeric conversion

I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:

1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron

None of these options work. Any idea anyone?

Don VonderBurgFormatting the cells as text after the data are there won't help.
Copy the cells to another location which is PREformatted as text, then
copy the copy back onto the original cells and try again. :)

On Thu, 13 May 2004 22:03:51 GMT, Don.Vonderburg@.nospam.com wrote:

>I am having a problem importing an Excel spreadsheet. I have a column in
>an Excel sheet with alphanumeric text and some of the cells are numeric.
>Some of the cells contain numbers like 12345.6 and when DTS is done
>importing it into a field that is nvarchar the results are
>"12345.600000000001". I have tried:
>1. Changing the format of the Excel column to text
>2. Using the formula =text(a1,0) which only truncates the .6
>3. Using the formula =t(a1) which will remove some numeric representations
>4. Exporting the sheet to CSV or TXT first which will not enclose the cell
>contents with ""
>5. Beating the computer with a nine iron
>None of these options work. Any idea anyone?
>Don VonderBurg|||Never thought of that one. Thank you.

Don|||Hi,

I vaguely remember that when you import the Excel file through DTS
that you can set the data type somewhere. Perhaps that helps.

When you look at the numbers that are wrongly imported in the Excel
formula bar, do you see the error as well? I guess when this is the
result in an Excel calculcated cell you may expect these rounding
errors. Remember that in Excel you never actually see the underlying
value. All values are always displayed using some kind of a display
mask. You can use the round function in Excel to round your results.
That should take care of it.

Just to make you aware of another way to pump your data into the
database. I wrote an addin for Excel called SQL*XL. Its goal is to
remove these hassles from the end user. You can use SQL*XL to get data
from the database into Excel or to pump data from Excel into the
database. It even lets you change retrieved data in Excel and post the
changes back.

If you are interested, have a look at SQL*XL at www.oraxcel.com

Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

DTS Transform issue

I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.

Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?

If there is could you be specific in how to setup the DTS transformation script.

TIA
Jeffmoving thread to SQL Server forum (the SQL forum is for the SQL language itself)|||Import everything to a raw table first.

Then you can make all the validation you want before inserting the data into your system.|||I'd probably just suck the data from the external source into a working table that had pure Unicode (NVARCHAR) character columns. Once it was there, you can "sanitize" it any way you need to using Transact-SQL.

Another option that saves on disk and keeps the package conceptually "atomic" would be to handle the exceptions within the DTS package itself. Instead of using a default "flow" transformation within the DTS column mapping, you could use script to do whatever validation suited your needs.

-PatP|||HHmmm, well it seems like you both are saying the same thing.
"raw table" and "working table" are they the same thing?
All fileds are nvarchar correct?
and use a copy column to column transform correct?

JR

DTS to truncate excel and load fresh data

I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve thisCan you try again?

I'm not sure I follow you...

Can you put the steps you do down in bullets...and what the final; result would be

1. I DTS in to a table
2. ect

I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.

'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********

Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1

' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")

If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If

If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If

' ****************
' Create new Excel Workbook
' **************

' Create object
Set xlApp = CreateObject("Excel.Application")

' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add

' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"

' ****************
' Format new Excel Workbook
' **************
With wkbNewBook

.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With

Set wkbNewBook = Nothing
Set xlApp = Nothing

End Function

##############################33

Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this

DTS to import data

Hi All,
I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?I think you'll need to get the provider of the Excel file to at least include the field even if it doesn't have any data. Then, if in your destination table you can specify that columns allow NULL values, when you receive a file that doesn't have any data for a particular column in your destination table, it won't matter.
You could also set a default value on those columns. Are you working with a native Excel file or a CSV file?

Lempster

DTS to export formated date to excel

Hello,

I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually. I tried to do this when I select the date from the table to spreadsheet, "select convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy. Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excelOk, i manage to answer myself, you need to format the cells onto the excel file itself

DTS to Excel, $0

I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?

Jim,

When I try a straight export with money data equalling zero

or '$0' as strings, either from SQL Server 2005 or 2000 (using

the Import/Export Data wizard in both cases), I get $0.00

or $0 in the Excel file.

Can you be more specific about what you are seeing? What is the

view definition? Is the problem column a table column declared

as money/smallmoney in SQL Server, an expression, or what? Are

you using a wizard or another method to do the export? When you

select from the view in SQL Server, do you see .0000, or something

else?

You haven't given us much to go on.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> I am exporting some data from a view to an excel file though a DTS. $0

> amounts come as -0, what is the reason for that an dhow can I fix it?

>

>

|||

DTS is using the view. When I use the view I do not see -0. But the exported data has -0. If I format the column in excel for 2 decimals, it becomes 0.00 so the negative drops. Maybe DTS considers a long decimal after the point and assumes it is negative.

|||

You haven't really answered some of the relevant questions:

Is the problem column a table column declared as money/smallmoney

in SQL Server, an expression, or what? Are you using a wizard

or another method to do the export?

SK

JIM.H.@.discussions.microsoft.com wrote:

> DTS is using the view. When I use the view I do not see -0. But the

> exported data has -0. If I format the column in excel for 2 decimals, it

> becomes 0.00 so the negative drops. Maybe DTS considers a long decimal

> after the point and assumes it is negative.

>

>

|||Thanks for your help. The column is a float in the table. I used the DTS wizard.|||

Apparently the number is a very small negative number. When such a number

is represented in Excel with only a few decimal places, Excel can't show

its absolute value differently from zero, but Excel could display its

sign correctly, yet doesn't. Excel displays the number as negative for

some formats and not for others. I would consider this a

bug, and you might wish to report it. (I don't know where, but if you

post this in an Excel newsgroup someone will probably tell you.)

I reproduced the fleeting - as follows:

In an Excel cell, type -0.00000001, the format the cell in these ways:

Number (2 decimal places). The result is 0.00 [not negative]

Currency (2 decimal places, with () for negatives. The result is $(0.00) [negative]

Currency (2 decimal places, with - for negative values. The result is $0.00 [not negative]

In particular, the currency marker shouldn't affect whether or not the number is shown as negative.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> Thanks for your help. The column is a float in the table. I used the DTS

> wizard.

>