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.
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
Filter the datatable based on your requirement and then use write range at last
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.
Can you help me with the syntax to filter data table. I am new to excel and data table things.
You want the rows which are not there in excel sheet2 but there in excelsheet1 right
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
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.
Can you please elaborate what exactly Function(field) field means here?
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
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