SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 17 Aug 2004


Question of the Day for 17 Aug 2004

Author
Message
chopeen
chopeen
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 57
> You need to remove the existing data,
> using the Excel automation model
How to do that?

> copying a blank spreadsheet onto the target spreadsheet
This is what I do, but I think it is a pretty primitive.

> executing a drop table, create table on your spreadsheet
How to do that?

I would be really grateful, if someone could explain me how to use the 1st and the 3rd way of solving thsi problem.



corey lawson
corey lawson
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 564
One way to do it is before doing the datapump task to load data to the workbook, set up an ActiveScript task.

Then, do some VBScript to

open Excel
open the workbook
select the right worksheet
select the ActiveRange
delete the ActiveRange.
Save the workbook
close Excel

Sorry I don't have the code off the top of my head right now, but you should be able to find some VBScript examples to do the bulk of it. Just google for it w/o specifying DTS, maybe something like: "VBScript Excel"
chopeen
chopeen
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 57
Thanks a lot. I know how to code such a script.



Dave Gallagher
Dave Gallagher
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
Can someone expand on the drop table/create table method - does this have to be done using vbscript and the excel object model?
Don Millhouse
Don Millhouse
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 29

If you use a data transformation task to populate the excel spreadsheet, your destination table name is your sheet name in excel.

You can create an Execute SQL Task to perform the drop table (drop sheetname) from your excel datasource. Note: close the spreadsheet before executing this

You then create the sheet with an Execute SQL Task to create the table (sheetname). You can get the code for this by going to your transformation destination tab and selecting create (it will pop up a window with the create table script). Copy this and paste it into your Execute SQL Task.

Connect the tasks with workflow and you are ready to roll.





Susan H. Lepp
Susan H. Lepp
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
Actually, I just post to a .csv file. It opens in excel but replaces instead of appends.
fhanlon
fhanlon
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2671 Visits: 2302
see http://support.microsoft.com/default.aspx?scid=kb;EN-US;319951



Francis
Yelena Varshal
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4618 Visits: 595

Very good question! I actually have a similar job running. The business need in our case is to append data and I am using the export into Excel spreadsheet. Thanks everyone for your explanations, now I better understand why and how it works.

Yelena




Regards,
Yelena Varshal

Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63091 Visits: 19113
Finally one question that people like

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10448 Visits: 11961

Actually there is no right answer in the list.

There is a little difference between "drop" and "truncate" table. And DTS package really drops the table - it removes spreadsheet from Excel book. It's easy to check.

So there is no "the equivalent of a truncate table on the spreadsheet", there is an equivalent of a drop table in the spreadsheet.

All answers are wrong, no point must be granted to anybody.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search