How to perform a keyword search in Excel


#1

Hi,

I have a column of defect description which needs to be searched for a set of particular keywords. If the keyword is found, a counter for each keyword to be set and updated.

How to perform a Keyword search using UIpath (defining keywords and comparing them against the description in excel column, set counter and updated). Need suggestions.


#2

Hi @Pandi

You can copy the excel data into a data table variable by Read range activity, Since you know the column name/Column Index ,you can loop through the every rows of the “defect description column”.
Inside the for each row loop you can set the IF statement with condition as row(column_index).contains(“Keyword”) and if it is true then increment the counter.

Regards,
Ranjith.


#3

Thanks @ranjith for the response. Would try to program as suggested.

Is it possible to have a demo/sample project for implementing the if loops and counters.


#4

Can you share your worksheet ,that you would be easy to work on


#5

ranjith’s solution is awesome (I didn’t know the method he use), but I have another option.

How about using DataTableName.Select method for each keyword to filter it and count the number of selected rows after Read Range?
DataTable.Select returns an array of DataRows, so you can know how many rows matched the criteria by ArrayName.Length.

I’ll try to create sample later.

Both must work fine, but I wanted to share this option because I think this would be better way when the Excel file becomes huge; it took me about 30 min when I iterated through an Excel file which contains 4000+ rows by using for each row to check if certain column matches condition, while DataTable.Select took only a few.


#6

Using LINQ is always a better option, You may proceed with Linq itself ,it will be efficient.

Regards,
Ranjith.


#7

Thank you Ranjith, I tried to use LINQ for the first time and finally got expected result!!

Here’s the sample file :slight_smile:
count defect list with keywords.zip (10.6 KB)


#8

Congrats :slight_smile: :cake:

One thing to note - String.Contains is case sensitive, so be careful there :wink:


#9

Thanks!
In this case I think the query should not be case sensitive.
Then we should use ToLowerInvariant so that we can ignore case :slight_smile:

(After a quick search, now I know we can ignore case by using .Compare, but not sure I can use it for filtering data with “like” or “contain” condition.)