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.
- validate with ‘keyword excel file’ whether the row contains keyword or not
- 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 !!
@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.
@prasath17 Hello, Thank you for your reply
Faced below error message. do you know what is the root cause of it?
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
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
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?
Hi @Dorothy_lee ,
Follow below steps.
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
Read target excel into Datatable (InputDT).
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
@poorna_nayak07 hello, thank you for your reply.
followed your process but faced below error message.
That I input in wrong way?
I have updated the linq expression please use it from my above reply.
Let me know if you are able to get output
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.
@poorna_nayak07 it resolved error but encountered different error on assign activity for String Keyword Array.
Before assigning to array please check if
Dt_keyword has values/rows in it
@poorna_nayak07 Thank you!!
above issue is resolved once I changed the read value from “A:A” to “A1:A40”
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.