Post reply

Need to delete last row in Excel sheet using SSIS script task

  • July 9, 2012 at 11:49 am

    #392697

    Hi to all.

    I need to delete last row in excel in sheet using ssis task. can any help me regarding this?

    Please do let me know in case of any additional information required.

    Thanks in advance.

  • July 9, 2012 at 11:57 am

    #1510477

    1. Connect to the excel sheet using JET oledb.

    2. Query the whole sheet and create a datatable which contains the excel data now.

    3. Delete the first row of Datatable.

    4. Update the datatable using Dataadapter (This will update the change to excel)

    OR

    Use update command as below:

    update `Sheet Name`

    set [Column Name]=null where [Condition Column Name] = 'Sample'

  • July 9, 2012 at 12:16 pm

    #1510490

    Thanks jitendra. i will try your solution.

    But i am looking for Script Task.

    Thanks in advance.:-)

  • July 10, 2012 at 5:01 am

    #1510815

    Hi to all.

    i didnt got any reply . am i missing any information ?

  • July 10, 2012 at 5:35 am

    #1510838

    try using sql script task-

    Dim filename As String

    Dim appExcel As Object

    Dim newBook As Object

    Dim oSheet1 As Object

    Dim cell As String

    Dim rowcount As Int16

    appExcel = CreateObject("Excel.Application")

    filename = "C:\Documents and Settingsbkrrov\Desktop\test.xls"

    appExcel.DisplayAlerts = False

    newBook = appExcel.Workbooks.Open(filename)

    oSheet1 = newBook.worksheets("Sheet1")

    rowcount = oSheet1.UsedRange.Rows.Count()

    cell = "A" + rowcount.ToString

    oSheet1.Range(cell).Entirerow.Delete()

    With newBook

    .SaveAs(filename, FileFormat:=56)

    End With

    appExcel.Workbooks.Close()

    appExcel.Quit()

    Thanks

    Sneh

  • July 10, 2012 at 6:04 am

    #1510855

    Thanks sneh.

    it is working fine....

  • October 10, 2012 at 12:39 pm

    #1547782

    I am trying to do something similar, but I cannot figure out how to close the excel instance from task manager. Any assistance? code snippet below

    Public Sub Main()

    '

    Dim objExcel As Object

    objExcel = CreateObject("Excel.Application")

    objExcel.Workbooks.Open("*.xls")

    objExcel.displayalerts = False

    objExcel.Sheets("*").Select()

    objExcel.rows("2:50000").delete()

    objExcel.Workbooks(1).Save()

    objExcel.Workbooks.Close(False)

    objExcel.displayalerts = True

    objExcel = Nothing

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply