How to filter the excel row with dynamic key words?

Help Activities
, , ,
1

Hello, all

I’d like to filter the row if the cell contents contains various value.
Is it possible to filter like this? Please refer to below example.

Current status is I can filter with specific keyword with below script! (Had some helps from this forum :))
(from x as DataRow in Dt2_HSBC_001 where x(“Name”).ToString.Contains(“Joy”) select x ).CopyToDataTable

The problem is key words are multiple like below(Iist is stored in excel file)
So, If target row contains one of key word among this excel list, I’d like to remain.
image
[Process]

  1. validate with ‘keyword excel file’ whether the row contains keyword or not
  2. Only remains the rows which contains keywords.

It’s a bit complicated,so please let me know if you need further information !
Thank you for helps always !!

2

@Dorothy_lee - You can try like this…

First Read the column where you have the keywords to an String Array using the below code…

 Dt2_HSBC_001.AsEnumerable().Select(Function (a) a.Field(of string)("Column Name").ToString).ToArray

After that use the below code to check whether Name column contains any value in the array…

  (from x as DataRow in Dt2_HSBC_001 where Array.Contains(x("Name").ToString) select x ).CopyToDataTable

Hope this helps.

2 Likes
3

@prasath17 Hello, Thank you for your reply
Faced below error message. do you know what is the root cause of it?
image

Stored keyword list on ‘Dt_keyword’
and assigned in array with 'St_Arr_keyword
Dt_keyword.AsEnumerable().Select(Function (a) a.Field(of string)(“Name”).ToString).ToArray

image
image1040×366 15 KB

Compare the target row with below script
(from x as DataRow in Dt2_HSBC_001 where St_Arr_keyword.Contains(x(“설명”).ToString) select x ).CopyToDataTable

image

4

Additional Q is how to read the column with above code?
I stored column data to Datatable variable and using Assign activity to convert String Array.
Is there another way?

5

Hi @Dorothy_lee ,

Follow below steps.

  1. image
    Read KeyWord Excel into one datatable(KeyWordDT) and convert it to Array of Keywords as below
    KeyWordArr=KeyWordDT.AsEnumerable().Select(Function (a) a.Field(of string)("Column Name").ToString).ToArray

  2. Read target excel into Datatable (InputDT).

  3. Assign ResDT=InputDT.Clone

  4. Use below expression in assign activity.

ResDt=(From row in InputDT.Asenumerable
Where KeyWordArr.AsEnumerable.Where(Function(x) row("ColName").ToString.Trim.Contains(x.trim)).Count>0
Select row).CopyToDatatable()
1 Like
6

@poorna_nayak07 hello, thank you for your reply.
followed your process but faced below error message.
That I input in wrong way?

image
image1366×679 64.2 KB

7

I have updated the linq expression please use it from my above reply.
Let me know if you are able to get output

1 Like
8

@Dorothy_lee

You can use the same query with collection of keywords in where clause.

(from x as DataRow in dtInptut where listKeywords.Any(function(k) x("Name").ToString.Contains(k))).CopyToDataTable

In listKeywords variable (list<string>) I have saved all the keys which needs to validated against the target rows.

1 Like
9

@poorna_nayak07 it resolved error but encountered different error on assign activity for String Keyword Array.
image

10

Before assigning to array please check if Dt_keyword has values/rows in it

1 Like
11

@poorna_nayak07 Thank you!!
above issue is resolved once I changed the read value from “A:A” to “A1:A40”
image

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.