Filter excel data by keyword

hi,

I have 2 excel one is having complete records with header and second excel is having keywords with header(only one column is there).
I have to segregate records with keyword search(keywords is in 2nd excel) from the 1st excel Description column and create 2 separate excels one will have the filtered records and another with have rest of the records.

In summary - I have to do keyword search in the 1st excel Description column and the keyword stored in 2nd excel.

Please find attached is the sample excelkeywords.xlsx (7.2 KB) records.xlsx (8.6 KB)

Read both the excels and store them in data tables. Then looping through the second data table, use filter data table activity and pass the value of second excel to filter and then get another data table.

Let me know if it is confusing

Thank you @HareeshMR for replying.

Yes, it is bit confusing and i am looking for a breakthrough or a solution other than filter data table.

I hope i am able to express my query.

Yes you can do that using select of data table as well. I mean,datatable.select(“column name = value”).copytodatable which will give you another datatable and assign it directly to a datatable. So that you can write it or use it directly

Hi
Hope this would help you

Cheers @indrajit.shah

Hi @indrajit.shah,

I got it, I think this is exactly what you’re looking for,

@samir sir, i am not comparing two excel sheet, instead my use case is below -
1st excel with records but my prime focus is Description column
2nd excel with keywords(in the excel there is one column keywords, i have to use these column and filter which contains the keywords in the excel1 Description column)
write 2 excel, one with filtered record and 2nd with rest of the records.

I am attaching sample excel filekeywords.xlsx (7.2 KB) records.xlsx (8.6 KB)

@Palaniyappan sir, actually in my case i am using 2nd excel sheet as and keywords to search the contains value in excel1.

like in my 2nd excel the record is Wire so i will search the word wire in the 1st excel Description column if its contains the word then it will write in a different excel let say filtered record and if the word is not there then also it will write in another excel let say unfiltered.

Hope am able to can explain myself
I am attaching sample files keywords.xlsx (7.2 KB) records.xlsx (8.6 KB)

@HareeshMR sir, can you please explain this a bit.
-Reading both the excel one by one using Read Range and storing in Recorddt1 and Keyworddt2
-Using For each Row item in Keyworddt2

Then how you suggesting??

Hi @indrajit.shah,

Yeah it was just to give you an idea, there’s slight change in method, so as you have to search Keyword in every Description and find out whether it’s there or not, .Contains should be there.

Use following code in assign to get description containing keywords.

MatchedDescription (DataTable)= dt2.AsEnumerable().Where(Function(row)  dt1.AsEnumerable().
Select(Function(r) Convert.ToString(r.Field(Of String)("Keyword"))).Any(Function(x) (Convert.ToString(row.Field(Of String)("Description"))).Contains(x))).
CopyToDataTable()

AND

Use following code in assign to get description not containing keywords.

UnMatchedDescription (DataTable)= dt2.AsEnumerable().Where(Function(row) Not dt1.AsEnumerable().
Select(Function(r) Convert.ToString(r.Field(Of String)("Keyword"))).Any(Function(x) (Convert.ToString(row.Field(Of String)("Description"))).Contains(x))).
CopyToDataTable()

Note -
here, dt1 (datatable) having Keyword column &
dt2 (datatable) having Description column.

Here, I’m attaching sample workflow —> containsMatched.xaml (10.2 KB) (If you need )

1 Like

@samir, thank you.

Can you help with this below error
Assign: The source contains no DataRows.

@indrajit.shah
The Error does mean that a particular Statement is using a Datatable that is empty.

It ist recommended to Debug and to Check If used datatables are Not empty and that any Other Statements are Not empty when the result ist used for a CopyToDataTable

ya i did debugged and found both my datatable have records.

What about the result that is used for datacopy. A quick Analysis can be done aß following. Change the result Datatype to object. Remove CopyToDataTable and corresponding paranthesis and Check If the result ist empty or Not.

Maybe you can Post the Statement throwing the erreor Message

RemoteException wrapping System.InvalidOperationException: The source contains no DataRows.
at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable1 source, DataTable table, Nullable1 options,
FillErrorEventHandler errorHandler)
at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable1 source) at lambda_method(Closure , ActivityContext ) at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context)
at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context) at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity)
at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor) at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath) at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides,
Location resultLocation,
Boolean isDynamicUpdate)
at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor,
IDictionary`2 argumentValueOverrides,
Location resultLocation,
Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)

@indrajit.shah,

Usually, this exception is thrown when argument provided to a method is not valid.
So in UiPath, it occurs when there are no returned rows OR if the datatable is empty.

So check your datatable variable using output datatable activity.