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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Need to delete last row in Excel sheet using...

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

Rate Topic

Display Mode

Topic Options

Author

Message

raghavatgk

Posted Monday, July 09, 2012 11:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:00 AM
Points: 38, Visits: 249

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.

Post #1327014

jitendra.padhiyar

Posted Monday, July 09, 2012 11:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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'

Post #1327019

raghavatgk

Posted Monday, July 09, 2012 12:16 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:00 AM
Points: 38, Visits: 249

Thanks jitendra. i will try your solution.

But i am looking for Script Task.

Thanks in advance. Smile

Post #1327035

raghavatgk

Posted Tuesday, July 10, 2012 5:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:00 AM
Points: 38, Visits: 249

Hi to all.

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

Post #1327424

snsingh

Posted Tuesday, July 10, 2012 5:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:09 AM
Points: 24, Visits: 170

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

Post #1327452

raghavatgk

Posted Tuesday, July 10, 2012 6:04 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:00 AM
Points: 38, Visits: 249

Thanks sneh.

it is working fine....

Post #1327471

sbkotcd

Posted Wednesday, October 10, 2012 12:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 12, 2012 5:40 AM
Points: 2, Visits: 10

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

Post #1371083

« Prev Topic | Next Topic »

Permissions