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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Administrators
Points: 18595 Visits: 222
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Administrators
Points: 18595 Visits: 222
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
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2040 Visits: 615
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
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 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
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 34

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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

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



Francis
Yelena Varshal
Yelena Varshal
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18734 Visits: 608

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