How to filter excel based on list of keywords in a particular column

Help
1

Book1.xlsx (8.8 KB)

I have a excel with 3 columns consider Message, Technician, Severity and i need to filter based on message column.

The condition is message column might contain certain keywords and the filtering on the message column needs to be done on those keywords irrespective of the case.

Used filter datatable activity and getting the result by using “or” condition for the same (message)column, but i need to do it without filter datatable activity as it is time consuming and unable to perform ignore case. is there any other way to do this?

2

Hi @karthik_A
Use for each activity row(“Message”). Tostring.contains(“keyword”)

Thanks
Ashwin.S

3

Hi @karthik_A

Welcome to uipath community

well we can use select method in that case buddy
if your datatable is stored in a variable named out_dt then with select method like this,
Final_dt = out_dt.Select("[Message] = ‘your keyword’ ").CopyToDatatable()

or

Final_dt = out_dt.Select("[Message] like ‘%your keyword%’ ").CopyToDatatable()

This would work similar to Filter datatable activity buddy and is more reliable and faster as well
Cheers @karthik_A

4

Hi @karthik_A

You can do this inside a for each row loop activity

Steps to follow.

  1. Create another datatable and clone the structure of the source datatable to it. You can do it by

SourceDT.Clone()

  1. Now use the for each row

  2. Within the loop set an if condition. Command goes as row("Message").ToString().Contains("Keyword")

  3. Within the true part of the if, add either a add data row activity or a invoke method activity with (importRow method)

5

Thanks for the response Ashwin. The issue is i have multiple keywords for the same column

For ex: Message column itself i have like 4 to 5 keywords on which the filtering needs to be done.

6

Hi @karthik_A

Use or condition based on other keywords as well

Thanks
Ashwin.S

7

Thanks for the quick response.
Used Your command but getting error like copytodatatable() is not part of system.array

image

8

in that case we can mention like this
Final_dt = out_dt.Select(“[Message] = ‘your keyword1’ or [Message] = ‘your keyword2’ or [Message] = ‘your keyword3’ or [Message] = ‘your keyword4’”).CopyToDatatable()

Cheers @karthik_A

9

@Palaniyappan Tried with this but getting error like copyToDatatable is not part of system.array

The expression throws an vaildation warning.

1 Like
10

Main.xaml (8.5 KB)

Hi @Lahiru.Fernando ,tried with the steps mentioned by you but the workflow doesn’t seem to get into the IF condition while debugged. attaching the XAML for reference.

Kindly have look into it.

11

Is there any idea why i’m getting this validation error @Palaniyappan
Thanks in advance.

12

Fine, make changes as per, open the xaml in the notepad and add the assembly reference as mentioned in the below thread

Cheers @karthik_A

13

@Palaniyappan Now that error went of. Getting a new error now

image

the keyword is getting considered as column here… :frowning:

1 Like
14

Great
remove the single quotes buddy
we need to mention only [tyr] - if its a column name
or
if its a value of a column remove the brackets and mention as ‘tyr’ alone
Cheers @karthik_A

15

@Palaniyappan missed out the quotes somewhere redone the condition and it is working fine now…

Adding to the above solution … is there anyway thge keywords can be considered irrespective of the case?
if possible to filter based on keyword irrespective of the case that would be helpful…

Thanks in advance.

1 Like
16

Great
Yes of course thats possible
before using this assign activity with a select method use another assign activity before this
like
datatalename.CaseSensitive = True
this will set the casesensitivity of the table whether to take or not
then use a assign activity with the select method
for more info on this

Thats all buddy you are done
Cheers @karthik_A

1 Like
17

@Palaniyappan thanks a lot . You are the best

set the case sensitive to ‘False’ to extract columns irrespective of case and it worked well and good.

Really appreciate your effort and quick responses.

Cheers :slight_smile:

1 Like
18

Fantastic
Cheers @karthik_A

19

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