Exclude rows from Excel if value of column is Yes

Help Studio
, ,
1

I want to exclude lines from the Excel file depending on value of other file

I have to read column H value from Excel A
Read the corresponding “Exclude from value” from Excel B
If “Exclude from value” in column B is “YES”, remove the line from Excel A

Need only linq query for this

2

Hi @shilpashree.mohanty ,

1. Read Excel Files into DataTables

  • Use the Read Range activity to read Excel A and Excel B into two separate DataTables, for example:
    • dataTableA for Excel A
    • dataTableB for Excel B

2. Use Assign Activity for LINQ Filtering

You can use the following LINQ query in the Assign activity to filter rows from dataTableA based on the value in dataTableB:

  1. Assign (to filter the rows based on the condition):Assign:
  • To: filteredData

dataTableA.AsEnumerable().Where(Function(rowA) Not (From rowB In dataTableB.AsEnumerable()
Where rowA(“H”).ToString() = rowB(“H”).ToString() AndAlso
rowB(“Exclude from value”).ToString().Equals(“YES”)
Select rowB).Any()).CopyToDataTable()

Hope it help,

3

Hi @shilpashree.mohanty

Can you try below query

dtA.AsEnumerable().Where(Function(rowA) Not dtB.AsEnumerable().Any(Function(rowB) rowA("H").ToString().Trim() = rowB("H").ToString().Trim() AndAlso rowB("Exclude from value").ToString().Trim().ToUpper() = "YES")).CopyToDataTable()

Regards,

4

ok will try this and let you know

5

should this be in loop?
coz it should remove all rows which has yes value and it can be different values

6

should this be in loop?

7

@shilpashree.mohanty

it need not be in loop…it is already looping via linq itself

one more easier way

  1. Use join datatable and join both based on any unique column or multiple columns …and do a left join having excel A on left
  2. Now use filter datatable to filter rows without yes and you have your datatable ready to be written to excel…you can also filter unwanted columns in same filter activity

cheers