How to check sub-string of the String is present in data table or not?

Help Studio
, , ,
1

I have a String say variable1= " Hello how are you doing today" which i need to compare with table data.

Assume datatable
Column 1 Column2
xxxa hello
xxxb hi
xxxc yesterday

now i need to check any of the sub-strings of variable1 is present in column2 of the data table.

How can i do this? using For Each row may take more time. Any better way to address this?

2

@Manuthejusui

Check the below thread.

3

@Manuthejusui

Welcome back to our UiPath community.

  1. Use Read Range activity to read the data from excel file and it will give output as DataTable. Let’s say ‘InputDT’.

  2. Then try below expression.

        InputDT.AsEnumerable().Any(Function(x) Variable1.Contains(x("Column2").ToString)

It returns True if value exists else False.

1 Like
5

Thank you Lakshman its working

One more query can we extended to multiple columns check
example
variable1= " Hello how are you doing today"
Assume datatable
Column 1 Column2 Column3
xxxa hello how
xxxb hi
xxxc yesterday you

for the same string we need to check in both column 2 and 3 based on that give output True or False?
because sometimes column 3 data will be empty for few row items and i need to ensure that column2 “hello” and column3 “how” is from the same row
if column 3 how is in diffrent row like below table it need to give False as output

Assume datatable
Column 1 Column2 Column3
xxxa hello me
xxxb hi
xxxc yesterday how

if i use InputDT.AsEnumerable().Any(Function(x) Variable1.Contains(x(“Column2”).ToString) and InputDT.AsEnumerable().Any(Function(x) Variable1.Contains(x(“Column3”).ToString)
its giving output as True instead of False

6

Hi

You are almost done try to use both the expression together at once

Like this

InputDT.AsEnumerable().Any(Function(x) Variable1.Contains(x(“Column2”).ToString AND Variable1.Contains(x(“Column3”).ToString)

Usually AsEnumerable will iterate more like for each row loop
So if you keep both the conditions together with and when it is iterating through each row this expression will check in one row and both the columns

And thus it will return boolean value accordingly

Hope this would help you

Cheers @Manuthejusui

7

No its giving wrong output which is True instead of False

in the above condition its checking for “hello” in row 1 and “how” in row 3 and giving output True but i need output as False because “hello” and “how” are not part of same row

8

Fine check in this manner

InputDT.AsEnumerable().Where(Function(x) Variable1.Contains(x(“Column2”).ToString AND Variable1.Contains(x(“Column3”).ToString).CopyToDataTable.Rows.Count>0

Cheers @Manuthejusui

9

getting syntax error
dtfilter.AsEnumerable().Any(Function(x) variable1.Contains(x(1).ToString And variable1.Contains(x(2).ToString).CopyToDataTable.Rows.Count>0)

10

@Manuthejusui

With a shift to query syntax we can handle multiple columns without heavy concatening it manually

arrCheckCols | DataType: String() - String array
Values: your Columnnames of interest
E.g. arrCheckCols = {“Col1”. “Col3”, “Col4”}

LINQ:
(From d in dtData.AsEnumerable
Let chk = arrCheckCols.Any(Function (x) variable1.Contains(d(x).toString.Trim))
Select b=chk).Any(Function (x) x)

We can integrate this request alternate by using row index info additional for checks, but the given sample looks like, that is not covering all scenarios.

Example
Column 1 Column2 Column3
xxxa hello me
xxxb hi
xxxc yesterday how
xxxd hello how
xxxe you today

now a&c not true as different rows, d&e true, But d&e compared to Var1 different rows.
Maybe the requirment has to be adopted

A requirement, that matches has to be in same row and we want to know how many rows did match for this we can do:

(From d in dtData.AsEnumerable
Let chk = arrCheckCols.All(Function (x) variable1.Contains(d(x).toString.Trim))
Select b=chk).Count

will return 1 in your second case from above, will return 2 in the alternate stress test case

11

Is it possible to fetch that column data? if the condition is true for the expression InputDT.AsEnumerable().Any(Function(x) Variable1.Contains(x(“Column2”).ToString))

12

Issue is resolved
InputDT.AsEnumerable().Any(Function(x) variable1.Contains(x(Column Index).ToString) AND variable1.Contains(x(Column Index).ToString))

13

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