Remove data row activity is not working in for each loop

Help
, ,
1

Hi Guys,
I am working on an RPA project which suggests copying data from one excel to another on the basis of already present data in the excel sheet in which data will be copied; all the data rows has a unique column value say “ID”. If data with an “ID” already exists in the second sheet , no data should be copied.
But when I am trying to remove data row from the data table in a for each loop after check on “ID” it is throwing me an exception :
Collection was modiefied; enumeration might not execute.
Please help with that.

2

Hi @Simi,

No Need to use for each

If you want to remove the column from the Data table-> Remove Data Column activity
If you want to remove the column from the Excel sheet-> Delete Column activity

Regards,
Arivu

3

@simi
Filter the datatable based on your requirement and then use write range at last

Regards,
Mahesh

4

I need to delete the complete row having the duplicate ID from the data table not just a cloumn. Please suggest something I can do with data row.
Regards
Simi

5

Hi @simi,

Refer this post,

Regards,
Arivu

6

Can you help me with the syntax to filter data table. I am new to excel and data table things.

7

@simi
You want the rows which are not there in excel sheet2 but there in excelsheet1 right
Regards,
Mahesh

9

Should the formula be like this :-
dtFinal.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is existingId)).CopyToDataTable()

existing id is the id to be checked
dtfinal is the data table from which I need to remove rows having ID equal to existingId

10

I want the rows which are in both the excel sheets… and remove those rows from excel sheet 2… then copy the data (after removing duplicate rows from excel sheet 2) to excel sheet 1.

11

Can you please elaborate what exactly Function(field) field means here?

12

Hi @simi,

LINQ Concept

Regards,
Arivu

13

@simi
Then you want the rows which are there in excel sheet 2 but not in excel sheet 1
I think lot of posts are there regarding this topic go through it, still if you face any issues post it :slightly_smiling_face:
Regards
Mahesh

15

Can you refer any … I have implemented lot of things posted regarding the issue. Please share link if you find any. Thanks for the help

Regards
Simi

16

@simi
ok let us take ur excel sheet 2 as dt2 and excel sheet 1 as dt1

dt3=(From p in dt2.Select
where (From q in dt1.Select where string.Join(“,”,q.ItemArray).Equals(string.Join(“,”,p.ItemArray))
Select q).ToArray.Count<1
Select p).ToArray.CopyToDataTable

Regards,
Mahesh

17

Hi All,
what if i want to remove the row when ever a condition is satisfied. like
|Column1|Column2|
|A| |1|
|B| |3|
|C| |4|
|D| |5|
|E| |6|
|F| |7|

if Column 2 is even number, then remove the row. how do i do it in for each loop.

I tried the below method, not working
method1: Created a duplicate DT and for each of orginal DT, i remove the row index of Duplicate DT. but after every for each, the row index of duplicate DT changes so the removing value is not right after the one loop.
method2: Is there a way to create a array in the for each loop of orginal DT and remove the row index of the array after the for each of orginal DT.