How to search index of a string/value from data table but without knowing the column

Help Activities
, ,
1

Hi i need your help for my case,
i have string = “123”
and a data table of = dt_Original

i want to search for a cell/index from the dt_Original which match with my string “123”
if its found then return the index , if not then it will return -1

also i dont know in which column i need to search , so i want to search in every column in the data table.

an asnwer using LinQ will be great

Thank you

2

Hi @StevenIsRobotOnline ,
You can use for each row or for each loop,
you can even get with assign activity, the exact value of cell of a datarow in a datatable…like this
assign activity(for datatable named dt_Original), for first row and 10th column
out_value = dt_Original.Rows(0)(9).ToString
if out_value = “123”–>return
the reason for 0 and 9 instead of 1 and 10 is the rows and columns are usually taken with 0 index.
Hope this would help you
regards,
LNV

3

what if the cloumn keep expanding in the future ,
is there a way to make this dynamic ?

also will this only check the column 0 and 9 ?
caused i need to check all column

thank you

4

Hi @StevenIsRobotOnline

Try this:

Assign activity:
ResultIndex = dt_Original.AsEnumerable().Select(Function(row, index) New With { .Row = row, .Index = index }).FirstOrDefault(Function(item) item.Row.ItemArray.Any(Function(cell) cell.ToString() = "123"))                      
(DataType of ResultIndex: System.Object)

Assign activity:
IndexToReturn = If(ResultIndex IsNot Nothing, ResultIndex.Index, -1)    
(DataType of IndexToReturn: System.Int32)

Check out this thread it might help you.

Hope it helps!!

5

@StevenIsRobotOnline ,

Please refer this thread for your problem

6

Yes, I think can dynamic
find each row, each column

7

Hi,

Can you try the following expression?

keyword ="123"

Then

dt.Rows.IndexOf(dt.AsEnumerable.Where(Function(r) r.ItemArray.Contains(keyword)).FirstOrDefault)

Sequence.xaml (7.8 KB)

Regards,

8

Hey @StevenIsRobotOnline,

You can try

(From row In dt_original.AsEnumerable
Let rowFound = If(row.ItemArray.Contains(input),row,Nothing)
Let idx = If(rowFound IsNot Nothing,dt_original.Rows.IndexOf(rowFound),-1)
Select idx).Max
9

Hey @StevenIsRobotOnline ,
I’ve replicated your scenario with my own dataset
And successfully solved it.

Refer below screenshot for my input data
image

So in the above data i am finding the index of the word "Sara"

Below is the code i used
(
From row In dt_1
Let rowIndex=dt_1.Rows.IndexOf(row)
Let a = If(row("City").ToString.contains("Sara"),rowIndex,-1)
Select a
).Max

Below is the output screenshot
image

Refer below xaml
Main.xaml (10.0 KB)

Hope it helps you out!

2 Likes
10

Hi Yoichi i tried using your code

dt_Product.Rows.IndexOf(dt_Product.AsEnumerable.Where(Function(r) r.ItemArray.Contains(Keyword)).FirstOrDefault)

but it send me -1 as result

11

Hi,

Does the above sample : sequence1.xaml work?

The above expression returns exact matched row index. If you need partial match (String.Contains), the following will work.

dt.Rows.IndexOf(dt.AsEnumerable.Where(Function(r) r.ItemArray.Any(Function(o) o.ToString.Contains(keyword))).FirstOrDefault)

OR

it might be column type matter. Can you also try the following?

dt.Rows.IndexOf(dt.AsEnumerable.Where(Function(r) r.ItemArray.Any(Function(o) o.ToString=keyword)).FirstOrDefault)

Regards,

1 Like
12

i tried the suequnce you give me and it works,

but i still unable to apply it to my main workflow

13

Hi,

If possible, can you share your table as file? It’s no problem if dummy data.

Regards,

14

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