How to check if an excel file contains a keyword in a particular row(not specified cell)

how to check if an excel file contains a keyword in a particular row. but the cell is not specified.

1 Like

@Chaitanya_podilapu - You can check following step:

  1. You can read excel file using “Excel application scope” that will output Datatable variable.
  2. Then use foreach row and then check you have any keyword inside ROW
1 Like

You can use this,
Dt.Rows(Index).ToString.Contains("")…
Or
You can check with column name inside for each row as,
row(“ColumnName”).ToString.Contains(“Value”)

1 Like

@sandipauti my excel sheet output scope is on workbook application not in data table. How can i convert it to data table. and there is a small correction in my question. i want to search complete column for a keyword. if its not there then the keyword will be added to that column. here i have many keywords. i was thinking if i achieve with one keyword then i will further process for every keyword i have

Fine
Hope these steps would help you resolve this
—use excel application scope and pass the file path as input
—inside the scope use read range activity and get the output with a variable of type datatable named dt
—now use a for each row activity and pass the variable dt as input
—inside the loop use IF condition like this
Not row(“yourcolumnname”).ToString.Contains(“your keyword”)
If true it will go to THEN part where we can use
Assign activity to include that keyword like this
row(“yourcolumnname”) = row(“yourcolumnname”).ToString + “ “ + “your keyword”
Or it the condition is false it will go to ELSE part where we can leave it empty

Cheers @Chaitanya_podilapu

2 Likes

how can i achieve the same using flow chart instead of if condition. because i have two workflows again based on true or false.

1 Like

Fine
Use FlowChart instead of sequence
And use the same set of activities

Cheers @Chaitanya_podilapu

there i can’t able to place the flow decision activity.

Usually only in FLOW CHART we can place the FLOW DECISION activity and inside the sequence we can use IF condition instead of that and place the same condition been used in FLOW DECISION and Vice versa in IF condition as well

Cheers @Chaitanya_podilapu

I’m getting the error, the range does not exist. my excel file is empty. right now i’m just tring to display the read data table. though my excel files empty it should at least display the empty msg box.

@Palaniyappan I’m getting the error, the range does not exist. my excel file is empty. right now i’m just tring to display the read data table. though my excel files empty it should at least display the empty msg box.

Kindly close the excel file if its opened already
or use KILL process activity before excel application scope with processname property mentioned as “EXCEL”

Cheers @Chaitanya_podilapu

I take this example for testing


I’m trying to display the whole contents of the background excel sheet but i’m only getting the output as datatable. you can see the dialog box above it. .
For suppose i want to read only A column and i don’t know the length/range of the column. then what should be the parameter to pass it on range . I’ve tried passing “A” as range but got this error “Range does not exist”

@Chaitanya_podilapu

If you don’t know range then mention Range as empty quotes and it will read whole data.

And what i observed in the above the screenshot is you are trying to print DataTable and we can’t print directly DataTable with Message box activity.

If you want to print DataTable then use Output DataTable activity to convert DataTable into String and then pass this to Message Box activity to print it.

@Palaniyappan it worked, suppose i have a blank sheet and i want to add column headers first, and then insert data, how can i achieve that

1 Like

In that case use a BUULD DATATABLE activity and create a table with column structure you want
And get the output with variable of type datatable named dt
—now use a clear datatable activity and mention the datatable as dt
—we can now use write range activity and mention the input as dt so that it will write only the column headers
—later if we want to append any datatable to that we can use append range activity

Cheers @Chaitanya_podilapu

I’ve done so far like this before but i couldn’t able to name the column headers. Were to do that

i didnt get this buddy
where you would like to add the headers
Cheers @Chaitanya_podilapu

first row of the excel sheet

Kindly share your xaml if possibble pls
Cheers @Chaitanya_podilapu