Read Specific Range in Excel then move file if it contains specific word

Help
, ,
1

Hello,

This is similar to another question I’ve asked but I now want to refine the search to just a few rows. The folders I need to search contain 1000s of different excel files with different sheet names.

I know the name of the sheet that has the key info I’m looking for. How can I search for a keyword in the first 3 rows then if the file contains it, copy it to another location? Thank you

1 Like
2

Hi , use
For each loop for Directory.getfiles(“Folderpath”,"*.Xlsx)
Then inside for each loop body

Use excel application scope
And excel file path as Item
Now use get sheets activity which will give you the list of Sheets in each excel file ex:Sheetslist

Now use another for each loop for Sheetslist , with the argument type is string

Now use if condition
With condition as
Item = your confirmed sheet name
Then use read range , get DT as output ex:DT1
Now with in the same for each body
Use for each row of DT1
Now inside body of for each row
Use get row item activity for the column where you are searching for a value , and take the output as a variable ex: FirstName
Use a if condition
With condition as
DT1.rows.index(row) < 3 and Firstname = “Your searching value”

Then block
Put ‘Copy File’ Activity

1 Like
3

Hope these steps would help you resolve this
–use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”,“*.xlsx”)
where arr_filepath is a variable of type array of string
–now use a for each activity and pass the above variable as input and change the type argument as string in the property panel
–inside the loop use EXCEL APPLICATION SCOPE and mention the file path as item.ToString
–inside the scope use READ RANGE ACTIVITY and mentiont he sheet name you know and get the datatable variable as output named dt
–now use a FOR EACH ROW activity and mention dt as input
–inside the loop use a IF CONDITION like this
row(“yourcolumnname”).ToString.Equals(“yourkeyword”)
–if true it will go to THEN part where use a ADD TO COLLECTIONS activity and mention the collection property as list_files where it s a variable of type system.colllections.generic.list(of string) with default value as new list(of string) defined in the variable panel
–and in the item property of add to collections activity mention as item.ToString
–next to this IF condition use another IF condition like this
Not dt.Rows.IndexOf(row) < 3
if this is true it will go to THEN part where use a BREAK activity

–now next to the FOR EACH activity within which we had the above mentioned activities, use again a FOR EACH activity where mention the input as list_files and change the type argument as string in the property panel of for each loop
–inside the loop use a MOVE FILE activity and mention the path property as item.tostring and in the destination mention the folder path where we want to move

hope this would help you

Cheers @sparkplug93

1 Like
4

How do I use get row item to search in the second row

1 Like
5

Then put if condition
If
Datatable.row.index(row) = 1
Then

Use get row item

And use if condition to check the value of that column in the 2nd row

2 Likes
6

Excel Document Search.xaml (11.8 KB)

1 Like
7

See workflow

1 Like
8

What if my column isn’t named and is just row 2 on the sheet

1 Like
9

that fine we can use column index instead of column name
row(columnindex).ToString.Equals(“yourkeyword”)

where column index starts from 0 for first column

Cheers @sparkplug93

2 Likes
10

so how would it be if i want to search the second row of an excel sheet named report

1 Like
11

this step would do that buddy
Cheers @sparkplug93

1 Like
12

I’m getting an error at read range since there’s files in the folder that do not have a sheet named report

1 Like
13

Fine
do all the files have that sheet report in it
Cheers @sparkplug93

1 Like
14

It isn’t working without reading each sheet

1 Like
15

Please check any case sensitive mistakes or Spaces are there for sheet name

1 Like
16

I’m having trouble with errors in the condition and get row item. The sheet name is correct
excel%20search

1 Like
17

see below

1 Like
18

Hi, for Get row item , you use only ‘row’
Please remove row =1

1 Like
19

in that case include this inside the excel application scope
–in excel application scope property panel get the output from the property workbook named
out_workbook
–then use a assign activity like this
arr_sheets = out_workbook.GetSheets.ToArray
–then use a for each loop and pass the arr_sheets as input and change the type argument as string in the property panel of for each loop, also the cchagne the variable name from item to sheet
–inside the loop now use a IF condition like this
sheet.ToString.Contains(“Report”)
if true it will go to THEN part where use our READ RANGE Activity with sheet name mentioned as sheet.ToString
rest of steps will be same

Cheers @sparkplug93

1 Like
20

I’m getting an error at For Each Row when the excel doc doesn’t have a sheet named Report

1 Like