Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: DTS to truncate excel and load fresh data

    I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you try again?

    I'm not sure I follow you...

    Can you put the steps you do down in bullets...and what the final; result would be

    1. I DTS in to a table
    2. ect


    I expect: x
    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
    Feb 2002
    Posts
    2,232
    You want to "truncate" the records in the excel spreadsheet... correct ?

  4. #4
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    1 - I truncate TableA in SQL
    2 - I select few fields from TableB and insert into TableA
    3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)

  5. #5
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this

  6. #6
    Join Date
    Jan 2004
    Posts
    65
    I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?

  7. #7
    Join Date
    Dec 2003
    Posts
    61

    DTS to Excel (Data being appended instead of refresh)

    I'm also facing the same kind of situation..plz let me know if you've found a solution

  8. #8
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: DTS to truncate excel and load fresh data

    I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.

    '************************************************* *********
    ' Visual Basic ActiveX Script
    '************************************************* *********

    Function Main()
    Dim oFSO
    Dim xlApp
    Dim wkbNewBook
    Dim strBookName
    Const xlNormal = -4143
    Const READ_WRITE = -1


    ' ****************
    ' Remove existing Excel Workbook
    ' **************
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    If Not oFSO.FolderExists("C:\folderName") Then
    oFSO.CreateFolder ("C:\folderName")
    End If

    If oFSO.FileExists("C:\folderName\FileName.xls") Then
    oFSO.DeleteFile ("C:\folderName\FileName.xls")
    End If



    ' ****************
    ' Create new Excel Workbook
    ' **************

    ' Create object
    Set xlApp = CreateObject("Excel.Application")

    ' Add new workbook to Workbooks collection.
    Set wkbNewBook = xlApp.Workbooks.Add

    ' Specify path to save workbook.
    strBookName = "C:\folderName\FileName.xls"


    ' ****************
    ' Format new Excel Workbook
    ' **************
    With wkbNewBook

    .Sheets(1).Select
    .Sheets(1).Name = "SheetName"
    .Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
    .Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
    .Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
    .SaveAs strBookName, xlNormal ,,,READ_WRITE
    .Close
    End With

    Set wkbNewBook = Nothing
    Set xlApp = Nothing

    End Function

    ##############################33

    Originally posted by msenoelo
    I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this

Posting Permissions

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