Delete row from xlsx file

Help
1

Hi,

Can anybody suggest a solution for below query,

Query - I wanted to delete last two rows of excel file.

  • I have tried to use insert/delete row activity but in that we need to specify the position, so for last two rows position how we can give, any idea?

Regards
Saurav

1 Like
2

@SauravYadav,

Check this xaml, it will delete last two rows in a excel dynamically, you have to change the excel file name before run it.
DeleteRowsInExcel.xaml (6.4 KB)

3

Hi @SauravYadav,

Check the below zip file along with the example excel sheet, it will delete last two rows dynamically with only one single activity…

forum.zip (25.5 KB)

Any doubts please let me know…

Cheers.
Vashisht.

4

Hi @sarathi125,

Your solution worked for my query, apprecaite the advice.

One another thing that I wanted to know if you could assist me on it,

  • I wanted to save the file from xlsx to xls, how we can implement it.

Regards,
Saurav

5

Fine
to delete the last two rows alone once after getting the dataatable variable with a simple assign activity that can be done
datatable = datatable.Asenumerable().Take(datatable.Rows.Count-2).CopyToDatatable()

and after this we can write this datatable to a excel with WRITE RANGE activity
and to convert the xlsx to xls we can use MOVE FILE activity, with path property mentioned as your xlsx file path and destination like this
“yourfolderpath\”+path.GetFilenameWithoutExtension(your xlsx filepath)+“.xls”

but using MOVE FILE may lead to file getting corrupted
so we can use READ RANGE activity and get the output of the datatable we want and use WRITE RANGE activity where pass that datatable as input and mention the filepath with filetype as .xls
that would work fine without any error…ensure that ADD HEADERS property is enabled in the property panel of write range activity
Cheers @SauravYadav

3 Likes
6

@SauravYadav

  1. Check row count after reading the excel file. Make sure you checked Add Headers option in Read Range activity.

           inputDT.Rows.Count

Then specify below fields to it.

NoOfRows - 2
Position - inputDT.Rows.Count-2
Change - Remove
Sheet - Sheet Name

7

Were we able to process this buddy
Kindly let’s know for any queries or clarification
Cheers @SauravYadav