Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question of the Day for 17 Aug 2004 Expand / Collapse
Author
Message
Posted Tuesday, July 27, 2004 12:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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.
Post #128547
Posted Friday, July 30, 2004 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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!
Post #129325
Posted Tuesday, August 17, 2004 12:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 3, 2012 11:06 AM
Points: 659, Visits: 56
> 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.



Post #132178
Posted Tuesday, August 17, 2004 1:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:26 PM
Points: 157, Visits: 374
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"
Post #132183
Posted Tuesday, August 17, 2004 1:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 3, 2012 11:06 AM
Points: 659, Visits: 56
Thanks a lot. I know how to code such a script.


Post #132187
Posted Tuesday, August 17, 2004 8:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 7, 2005 11:54 AM
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?
Post #132288
Posted Tuesday, August 17, 2004 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 29, 2012 10:45 AM
Points: 248, Visits: 14

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.




Post #132293
Posted Tuesday, August 17, 2004 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2005 4:40 PM
Points: 13, Visits: 1
Actually, I just post to a .csv file. It opens in excel but replaces instead of appends.
Post #132327
Posted Tuesday, August 17, 2004 10:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 2,185, Visits: 2,003
see http://support.microsoft.com/default.aspx?scid=kb;EN-US;319951



Francis
Post #132333
Posted Tuesday, August 17, 2004 1:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 3,475, Visits: 584

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

Post #132394
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse