Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Overwrite Excel spreadsheet

    Hi,

    I have a DTS package that imports a file with data,
    performs several checks,
    puts all invalid rows in a table,
    exports the table to a excel spreadsheet.

    My problem is that it appends the data every time. I want it to overwrite the existing spreadsheet. I can't find any option for that.

    I use the "Transform Data Task" to do this.

    Any suggestions?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do an EXECUTE PROCESS Task and supply a DELETE Command...

    But I didn't think it did an append....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    24

    Re: Overwrite Excel spreadsheet

    I had the same problem this week. I made a template of the excel sheet, selected the columns needed and created a named range on the excel sheet, and added it as a linked server. Once it's linked like this you can treat the named range like a table and use a delete from statement to clear the sheet.

    Run this command from QA to make the excel sheet a linked server, 'excelsource' can be any name you want to give the connection.

    sp_addlinkedserver 'excelsource', 'jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
    'c:\temp\filename.xls', null, 'excel 5.0'

    If the named range on the excel sheet is also named excelsource use

    delete from excelsource...excelsource

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •