Deleting data from Excel File

Question

Deleting data from Excel File

bhavika.chauhan

Mr or Mrs. 500

Points: 523
More actions
April 20, 2011 at 3:34 am

#145495

Can i delete data from excel file , after i have copied the file into another folder using SSIS (File SystemTask)

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Bhavika

I don't think so. The File System Task is for performing actions on files, not for manipulating data within files. I've never tried it, but you should be able to set up a Connection Manager to your Excel file and use that to delete data as if it were a SQL Server table.

John

bhavika.chauhan Mr or Mrs. 500 Points: 523 More actions · Answer 2

Not necessary i need File System task.Can i use any other control to Delete Data ???

steveb. SSC-Forever Points: 46733 More actions · Answer 3

you could possibly use a Script task and use the office connectivity to delete data from your spreadsheet.

However if would be far easier to use John's suggestion as setup excel as a destination to delete data.

bhavika.chauhan Mr or Mrs. 500 Points: 523 More actions · Answer 4

"However if would be far easier to use John's suggestion as setup excel as a destination to delete data."

Can you let me know how can we achieve this ???

steveb. SSC-Forever Points: 46733 More actions · Answer 5

I would setup a staging table and then read all the data from the spreadsheet into it,

then delete the neccesary data from the table and then setup another data flow from this table to a excel destination and send the data there.

So effectively rather than deleting data, you are actualy inserting the data that you want to keep.

There may be other ways that actaully involve deleting data but I have not used them

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

There is another way.

Create an Excel spreadsheet which contains only field headings/formatting, but no data. This is your template - put it on a network share somewhere.

Then, rather than deleting from Excel, just use a FST to copy this template over the top of the Excel file from which you want to delete.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

bhavika.chauhan (4/20/2011)
Can i delete data from excel file , after i have copied the file into another folder using SSIS (File SystemTask)

The standard Excel adapters doesn't support data deletion. Using Office automation requires programming skills and deployment of Office on your server.

If you can use third-party solutions, check the commercial CozyRoc Excel Task. It supports data deletion and many other actions.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

Create an Excel spreadsheet which contains only field headings/formatting, but no data. This is your template - put it on a network share somewhere.
Then, rather than deleting from Excel, just use a FST to copy this template over the top of the Excel file from which you want to delete.

I agree with this. I had this problem recently and tried to programatically delete the data, but the new data would simply be appended where the old data had left off. In the end I used the above technique to solve it. Not quite as elegant as manipulating the file, but it is simple and effective.