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
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12595 Visits: 16
Comments posted to this topic are about the Question of the Day for 17 Aug 2004 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=316.
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12595 Visits: 16
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
chopeen
chopeen
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 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 Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 Visits: 575
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
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 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
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 30

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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

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



Francis
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8754 Visits: 600

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

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