Question

How to delete specific rows in a Datatable ?

  • 18 January 2023
  • 13 replies
  • 2803 views

Badge

For example : Here I need to delete rows contain “bbb” and “ccc”
 

The output should contain only rows with “aaa”

 


13 replies

Userlevel 7
Badge +13

Hi @sree 1855 ,

 

If you are using the Data Table package, there is an option called Delete row for this requirement. You just need to pass the Data Table variable and the Row Index number. Since you need to clear 2 different rows, you can repeat this process 2 times and provide the required Row index. 

 

Note: Row index is starting from 0.

 

 

I hope this will help.

Badge +3

I actually have the same question - write now I’m looping through each datatable row, evaluating one of the fields from my tablerow variable, and if the condition is met, calling ‘Delete Row’ - however  I’m unsure of how to provide the Delete Row action with the index of that current data row that just passed the condition (which might be the loop counter - 1 since I believe the table rows are zero indexed).  Thanks.

 

 

Userlevel 7
Badge +13

Hi @JustinK ,

 

You can use a counter variable inside the loop and an If condition to check whether the row is fulfilling your criteria or not and use the Delete Row action if not. You can pass the counter variable there as the row index.

 

I hope you get the logic.

Userlevel 2
Badge +8

I think the issue with deleting as you loop is that the index changes so your counter can get off. For example, if I have 20 rows and your counting (starting with a counter at 0) and I find my criteria for a delete at “counter row 4” and I delete that row, I should NOT advance the counter because my table is now one row less than it was before the delete.

If I advance the counter after a delete, when I find another row to delete and I use the counter as the row number, I will actually delete the row AFTER the one I want to delete.

So, either advance the counter only when you DO NOT delete the row, or start the loop over (and the counter) each time you delete a row.

Userlevel 4
Badge +14

Hi @JustinK ,

You can test with NOT ADVANCING the counter when you delete the row as in like : If row is deleted then decrement the counter by 1 and it will automatically increment at the end of the loop.

I think in above way you don’t have to start over the loop or counter.

Userlevel 2
Badge +8

@Zaid Chougle I’ve been trying to do this using the method of not advancing the counter but there seems to be an issue when you delete a row while looping. I’m not sure where the ‘curser’ is in the loop after you delete. I think may actually be moving to the next row. I’ve got a use case where I need to remove every row that has a certain value and I’m getting unexpected results, like it’s removing the wrong rows on subsequent loops.

Below I’m looping the data table and only removing certain rows and only incrementing the iRow number variable when we don’t remove a row. But as it progresses it leaves behind some rows that it should have removed and removes others that it should not.

Lines 28 down I’m just re-looping and outputting to a log file to check the data.

Can anyone confirm the behavior of the loop when you remove a row? Like if the very first row is removed from a data table while looping are you still on row 0 or are you now on row 1?

Userlevel 2
Badge +8

So I checked the values of the current row before and after the deletion and what’s happening is that if you delete any row the rows are moved UP but the looping internal counter DOES NOT change so it essentially skips a row. Example:

  1. First row (index 0) meets the criteria to be deleted and is removed. We loop back to the top.
  2. We are now on the second row (index 1) which was originally the THIRD row (index 2) in the dataset. The record now sitting in the 0 index position is never evaluated!

Same hold for items removed in the middle of the table:

  1. Row 10 (index 9) meets the criteria to be deleted and is removed. We loop back to the top.
  2. We are now on the 11th row (index 10) which was originally the 13th row (index 12) in the dataset.

So it seems the only way to delete the correct rows in a loop is to exit the loop each time you remove a row and start over. OR create a list of indexes that need to be removed and remove them in reverse order in another loop so the indexes do not get off.

@Zaid Chougle I’ve been trying to do this using the method of not advancing the counter but there seems to be an issue when you delete a row while looping. I’m not sure where the ‘curser’ is in the loop after you delete. I think may actually be moving to the next row. I’ve got a use case where I need to remove every row that has a certain value and I’m getting unexpected results, like it’s removing the wrong rows on subsequent loops.

Below I’m looping the data table and only removing certain rows and only incrementing the iRow number variable when we don’t remove a row. But as it progresses it leaves behind some rows that it should have removed and removes others that it should not.

Lines 28 down I’m just re-looping and outputting to a log file to check the data.

Can anyone confirm the behavior of the loop when you remove a row? Like if the very first row is removed from a data table while looping are you still on row 0 or are you now on row 1?

 

Userlevel 3
Badge +6

Create a new empty table and add desired rows to it. Overwrite original table with this table if needed.

This avoids index and other tracking and is simple to understand.

Pseudo Code:

filtered_table= empty table

Loop over table_to_be_filtered

  if  current_row matches deletion criteria

  continue

add current_row to Filtered_table

End Loop

 

Userlevel 2
Badge +8

@Sumit.K7  I’m actually trying that with the rows that are being removed (inserting them into a blank table for later use). The issue with that is that it seems you can no longer refer to the column indexes from a row record when you loop that table. It’s really strange behavior.

Here’s what I’m doing:

  1. Before removing the row I’m taking the record object and using the Insert Row command to put that row in a blank table (no schema defined)
  2. Later I’m looping that table and trying to output the data using the column indexes $StatementTableRow[0]$
  3. I get the error “Invalid argument supplied to 'record', index '1' is invalid or out of bound.”

Given this behavior I DO NOT want to try this with my main table if I can’t get the data out of it! Any suggestions?

Userlevel 2
Badge +8

@Sumit.K7 what it looks like is happening is when I use Insert Row with a record object it only creates ONE column in the target blank table instead of the 9  columns from the record object. I’ve confirmed this by using Get Total Columns and Get Totals Rows after inserting a row. I’m starting to think these table objects are pretty useless when it comes to adding records, rows, columns etc.

 

Userlevel 2
Badge +8

@Sumit.K7 ok I was able to use your method BUT I had to first use the Assign command to create the columns needed in the target table:

  1. Add data to table 1 using Excel Advanced
  2. Add data to table 1 using Assign command (assign table 1 to table 2)
  3. Use the Clear Content command on table 2
  4. Loop table 1 and add rows to table 2 when certain criteria is met

This successfully adds rows to table 2 from table 1 with the same columns.

Badge

Esto es lo que me funciono, saludos a todos

 

 

Badge

I have a successful logic to delete specific rows in a data table.

Now, we are aware that the counter starts from 0. Now you can follow the steps to achieve the outcome.

  1. Loop through the data table, with String if condition (Table[0] != aaa) and store the row numbers into a list say “tempList” 
  2. now set a counter value to 0
  3. Now loop through the Temp list which is created, store the current list iten in current row, in that loop add the following:
    • Current = Current row - Counter 
    • Delete Current row
    • counter + 1

That's it.!!!

 

Explanation is below for second loop:

In the above example you know that the row numbers that has to be deleted are: 1, 4 & 5

TempList = 1, 4, 5

Once we start the loop, first value of current row = 1 and counter = 0. so when you subtract counter with current row you get current row value as 1. so you use delete row at 1. (see fig “original table”)

on next iteration, current row = 4 and counter = 1, Hence, on subtraction current row = 4-1=3, which is deleting row at 3 (see fig “when row number 1 is already deleted from the original)

and similarly, 5 - 2 = 3 and so on..

AA code sample:

 

I hope this helps!😄

Reply