To find if the variable/string is present in any 3 excels

Help Studio
, , , , , ,
1

Hi,
The problem sttement-

I want to find if a variable is present in the first Excel file (excel1), if present then perform some activities; if not present in excel1,
then check if the variable is present in second file (excel2), if present then perform some activities; if not present in excel2,
then check if its present in the third excel (excel3), if present then perform some activities; if not present in excel3,
then return a status saying- “variable not present in any excels”

Thanks in advance!

2

Hi @ray_sha :wave:

Can you please share any sample Excel files so I can give you a proper solution?

Thanks!

3

Input excels
excel 1:
image

excel 2:
image

excel 3:
image

@monsieurrahul
Thanks in Advance!

4

While I work on the solution, kindly tell me if this is right:

  1. You have 3 Excel files.
  2. You have a variable which will have a value say “a3”.
  3. I have to check if the variable is present in all the Excel files (only column available is name)
  4. I return the result in a message box.

Is this okay for you?

5

Hi @monsieurrahul,

Yes, this must work
The loop must not run to check the remaining nodes after the output is obtained.
Thanks in Advance!

6

@ray_sha
read in the excel into a datatable with read range

define the variable with the searchterm: e.g. SearchKey = “FindMe”

use an assign activity:
left side = isFound (DataType: Boolean)
right side:

(From d in YourDataTableVar.AsEnumerable
Select d.ItemArray.Any(Function(x) x.ToString.Contains(SearchKey))).Any(Function (b) b)

it will check all rows and columns for the search term and is returning true if it was found at least 1 time

find some starter help here:
ContainsSearchTerm_AnyRowAnyCol_CheckAll.xaml (6.7 KB)

1 Like
7

Hi @ppr,

I’ll have to have 3 such conditions, for I’ll have 3 data tables?

Thanks in Advance!

8

@ray_sha
get noticed updated post with demo xaml

Maybe I did get you wrong. Checking 3 different datatables will not change the statement.

  • statement will executed for each datatable/excel data
  • Only the datatablevariable is different or will represent the different data
  • if each datatable is to search for different keywords then just modify the content of SearchKey

More clear so far or do I still misunderstand you?

9

Hey @ppr,
Thanks for the reply.

Just wanted to confirm the given condition:
(From d in YourDataTableVar.AsEnumerable
Select d.ItemArray.Any(Function(x) x.ToString.Contains(SearchKey))).Any(Function (b) b)

Here YourDataTableVar: this will have my data table variable from just one excel file, correct?
but I have 3 different excel files, which will give me 3 different data table variables.

I’ll go through the xaml file.
Thanks.

10

execute the statement three times
for each execution use the corresponding datatablevariable

(From d in YourDataTableVar1.AsEnumerable
Select d.ItemArray.Any(Function(x) x.ToString.Contains(SearchKey))).Any(Function (b) b)

(From d in YourDataTableVar2.AsEnumerable
Select d.ItemArray.Any(Function(x) x.ToString.Contains(SearchKey))).Any(Function (b) b)

(From d in YourDataTableVar3.AsEnumerable
Select d.ItemArray.Any(Function(x) x.ToString.Contains(SearchKey))).Any(Function (b) b)

more clear now?

11

Hi @ppr this works.
Thanks so much

we’ll have to use it along with if condition.

12

Hi @monsieurrahul

Thanks so much for replying
If you have a solution, Please post it as well.

13

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