How to delete rows in the same sheet (Excel)?

[Tana20]

For instance, I would like to delete rows where cell “Amount”= 0 or “Amount”= Empty, from Sheet1:

Input:

Output Expected:

I tried using the method below but the filtered rows never were deleted…

Note: I cannot use unofficial activities

Regards,

[prasath17 (Guru)]

@Tana20 - Can you please show your filter Wizard?

[Tana20]

Here you go:

I think the filter works, but it is overwriting the previous data …

[Chinh_Le (Chinh Le Duc)]

@Tana20

“Amount”= 0 or “Amount”= Empty

“Amount” = “” is right. Not Empty

[prasath17 (Guru)]

@Tana20 - Also check the post below

How to delete Blank/Empty rows in an Excel

How to delete Blank/Empty rows in an Excel Attached input excel lMergeSheets.xaml (8.9 KB) ExlSheet.xlsx (14.5 KB)

[Mr_Meeseeks (Dan Fishkin)]

We can definitely do that! Here’s how

1. Wrap everything up in an excel application scope
2. Read range and output to a DataTable
3. Assign the first row number to a variable i
4. For each row in the DataTable
5. Check If the Amount is 0
   True - Use the Insert/Delete Row Activity to delete a row using the row number variable
   False - Increment the row number variable and go to the next row in the DataTable

NOTE - We do not Increment the row number variable after deleting a row because if we delete a row, the next row moves up

Here is a screenshot of the workflow I would use to delete rows where the Amount Field had 0 in it

[Tana20]

Thanks, @Mr_Meeseeks, but since I have to process files with more than ~20K rows it is difficult to use loops; however I’m curious about how you used the activity “Insert/Delete Rows”…my understanding is that it requires a starter position which must be provided by a number…

[Mr_Meeseeks (Dan Fishkin)]

Absolutely correct! Before looping through each row I assigned a variable equal to 2 for the row number and for the Position property of the Insert/Delete Rows activity I am plugging that variable in.

The Write Range activity would overwrite whatever data was in sheet 1. This is how I would expect the worksheet to look after performing the Write Range activity.

Invoice 4 is overwritten and Invoice 5 is seen twice

Would you consider Writing your filtered range to a new sheet and then replacing it? I agree that loops wont be very efficient for 20k+ rows

You could also use the Insert/Delete Rows activity to delete all of the rows in the worksheet, then perform the Write Range activity using your filtered range on the cleared sheet

[prasath17 (Guru)]

@Tana20 - if its 20K rows then you need a LINQ query.

[prasath17 (Guru)]

@Tana20 - Here you go…

LINQ Query in the Assign activity

dtInput.AsEnumerable().Where(Function(row) Not(row(“Z”).ToString.Trim.Contains(“0”) or row(“Z”) Is DBNull.Value Or row(“Z”).Equals(“”) )).CopyToDataTable

Input

Output

[Tana20]

@prasath17 Awesome! I was trying to understand how to use LinQ! I just realized that the approach provided by @Mr_Meeseeks will help me in most cases and LinQ will improve the performance of heavy transactions. Thanks to both of you

[prasath17 (Guru)]

@Tana20 - Glad it helped you. Did you already ran this against your 20K rows file?

I had 36K rows file, Filter table taking close 10-15 mins to run(along with the other activities)…Later I changed it to LINQ (after learning thru Forum) my job ran in less than 2 mins.

You refer this post for future filtering queries…

DataTable filtering with expressions

Hi All, Use below code to remove empty row from the table. DataTable name->DataTableName Use assign activity DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable() Re-usable Component pass Parameter as datatable you will get the answer. RemoveEmptyRows.xaml (8.5 KB) Remove rows based on particular column is empty. DataTableName.Select("Convert(column3, System.String)< >''"…

Also this

Four Ways to Filter Data Tables in UiPath Studio | UiPath

[Tana20]

Thanks for sharing that with me, and yes, I was almost there, I was just missing a parameter, but your script did the trick, it ran in 1m for 15K rows!