Delete a row in excel.

Question

Delete a row in excel.

h_zulfi

Mr or Mrs. 500

Points: 507
More actions
February 25, 2008 at 9:22 am

#120035

Hi gurus,
Could i delete a row in excel using ssis task and without using any com objects.
Cheers,
Zulfi.

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

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

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 1

If what you want to do overall is nothing complicated and you know which key to use to delete the row, then just use the execute SQL task transformation to add in your simple query! Thats all!

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

h_zulfi Mr or Mrs. 500 Points: 507 More actions · Answer 2

Sorry for short description.

I got a pre-formated excel sheet as destination. The header is formatted with color.

The requirement is to populate the excel sheet with different data type (name(string), no (int), sal(int), age(int)) from second row onwards. The source data is a SQL table and I am using SSIS to export data to the above excel destination.

Problem:

Scenario 1: Using SSIS data flow, populate the excel sheet. In this case, the format of the first row is used by all the new inserted records. So, I am not able to format as per the data type requirement.

Scenario 2: To populate data with required data types, I need to format the second row with the specified data types and then export data into the excel in the SSIS data flow. In this case, there is a blank row in the second row position as shown in the picture:

(see the picture on url)

http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040908106882370

To meet the requirement, I need to remove the blank row in the excel destination. It should look like below:

http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040912401849682

Is there any other method to achieve the export of data meeting the specified requirement?

If no, then is there any method to remove the blank row from excel apart from using com objects to remove the blank row and shift cells up right.

Any thought and help in this regard would be much appreciated.

Cheers,

Zulfi

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 3

Okay, I have tried it and it is working fine. Here is what you are supposed to do.

In Data flow:

First, drag and drop the excel source transformation and plug in all the neccesary connection.

Then an OLE DB destination to take your data to the SQL table.

Then back in Control flow:

Drag and drop Execute SQL Task and apply the connection and here plug in this SQL Command:

DELETE from [Your Table]

where [Column 1] IS NULL AND [Column 2] IS NULL AND [Column 3] IS NULL..........and so on...........

Of course there are lot more other ways to do it, but this one is pretty easy.

(I know how you feel when the task is difficult but to your thinking it is easy because all you wanted was removing one row. But this is gonna help you.):)

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

h_zulfi Mr or Mrs. 500 Points: 507 More actions · Answer 4

It seems that you are pointing to delete the record from the SQL table and then populate the excel again. I have already tried this and it does not work. 🙁

I have updated the problem description and made my requirement more clear. Could you pls look at and put your thoughts on?

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 5

so you mean your destination is excel file again?

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

h_zulfi Mr or Mrs. 500 Points: 507 More actions · Answer 6

Yes, In short, if i use preformated excel file as destination to export data, i get a empty row i.e. row 2

between the header and data.I would like to remove the empty row.

ganesh_.net SSC Rookie Points: 29 More actions · Answer 7

ganesh_.net

SSC Rookie

Points: 29

May 29, 2008 at 12:28 am

#821593

yes