How to delete the row from datatable2 which has different value from datatable1

[shailesh1920 (Shailesh)]

In the excel there is two sheets sheet1 has one column names as “Service No” and sheet two has “Service Number”. In this i want match this both column value and if the value is not matched then the data row should be remove.
Please help regarding this.

[Anil_G (Anil Gorthi)]

@shailesh1920

Welcome to the community

Follow the steps

1. Create a new table dt3 and then use dt1.clone() in an assign activity
2. Loop through dt2
3. Inside loop use a filter datatable and filter dt1 on the currentrow("service number").tostring… and get to filterdt
4. Use a merge dstatable to merge filterd rows filterdt to dt3

At the end of loop dt3 will have only the matching rows

Cheers

[sunilch (sunil)]

Hi @shailesh1920

you can do this in multiple ways. you can achieve using LINQ, if you are not much familiar with LINQ. you can achieve it using below steps

1. Read both sheets and keep in 2 data tables
2. loop for all values in sheet 1
3. in loop use lookup data table activity provide service number of sheet 1 and check in sheet 2 is available or not.
4. Based on the response you can delete in sheet 1 dt or create a new data table(dt) to capture the required data

Happy Automation!!!

[shailesh1920 (Shailesh)]

how we can delete the data from sheet1 ?
could you please help me with linq query ?

[supermanPunch (Arpan)]

Hi @shailesh1920 ,

Could you provide us with a Sample Input data and the Expected Output for that data ?

It will be easier for us to understand logic needed and suggest you the approach accordingly.

[shailesh1920 (Shailesh)]

Above sheet having “Service Number” Column and below sheet having “Service No” Column if the service number not matched with below sheet then that row should be remove from below sheet.
Please help on this.

[supermanPunch (Arpan)]

@shailesh1920 ,

Could you try the below Steps :

1. Read the above sheet’s First Column using Read Column Activity. Assuming that the Service Number will always start from that specific position (A2). The Output will be a collection which will contain all the service numbers present in the column, let the output variable be named as serviceNoList.

2. Next, Read the below Sheet using Read Range Activity. Get the Output as Datatable, say DT.

3. Next, Use an Assign Activity and use a Linq Query to Filter only the rows which contains the Service numbers present in the above sheet.

DT = DT.AsEnumerable.Where(Function(x)serviceNoList.Any(Function(y)y.ToString.Trim.Equals(x("Service No").ToString.Trim))).CopyToDatatable

4. After the Filtering, The DT variable should contain only the rows that has the service numbers which match the service numbers in the above sheet. We could then Write this datatable value to a new sheet using Write Range Activity.

To handle the Errors on Direct CopyToDatatable you could check the below post :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

Let us know if you are still facing issues.

[shailesh1920 (Shailesh)]

Thank You so Much @supermanPunch