Remove data row activity is not working in for each loop

datatable
excel
activities

#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