Easiest way to search through excel files for keywords then copy them into a new folder

Help
1

Hello,

I have a folder of excel files that need to be searched for keywords then copied into another folder if they contain that keyword.

What is the easiest route to do so? Thanks

2

@sparkplug93

Can you let me know if the keywords you need are in the same column or from any ?

3

They keywords are spread throughout the sheet. Also, some workbooks have more than one sheet

4

I don’t think we have activities to do that, if they are under some columns, then you can read the data and filter that using filter data table activities @sparkplug93

1 Like
5

Could I go excel to text to read text file then move them like that?

6

I don’t understand what you mean?

7

Convert the excel file (not save) to text, read the text, then move the excel file

8

Yes ofcourse you can do that as well @sparkplug93, that was a great idea :slight_smile:

  1. Use read range and store in data table
  2. use output data table to convert it to string
  3. Then use if condition to check text.contains(“keyword”)
  4. If exists, then move the excel to your destination folder
9

How can I read more than one sheet?

Thanks for your help

1 Like
10

Hmm… yes thats possible
so sequence will be like
–use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”,“*.xlsx”)
where arr_filepath is a array of string variable which will hold the filepath of all excel in that folder mentioned
–then use a for each activity where change the variable from item to item1 and pass the above variable and inside the loop use a excel application scope and pass the file path as input like item1
–get the output from the excel application scope from property WORKBOOK as out_workbook (as you said there can be many excel sheets)
–inside the scope use a assign activity and mention like this
arr_sheets = out_workbook.GetSheets.ToArray
where arr_sheets is a array of string variable
–next use a for each activity where pass the above array variable as input and in the type argument as string inthe property panel
–inside the loop use a READ RANGE activity and get the output with a variable of type datatable named dt
–now use a OUTPUT DATATABLE ACTIVITY where pass this dt as input and get the output with a variable of type string named str_dt
–next use IF condition like this
str_dt.ToString.ToUpper.Contains(“your keyword in uppercase”)
if true it will go to THEN part where we can use a MOVE FILE activity inside that
followed by this use BREAK activity it will stop iterating within this inner for each loop

Cheers
updated the comments @sparkplug93

1 Like
11

Can you clarify the steps on how to read each sheet?

12
12589Ă—800 19.3 KB

1 Like
12

sure
in excel application scope activity in the property panel, mention the variable as out_workbook under property WORKBOOK
–then in the DO part of EXCEL APPLICATION SCOPE use a assign activity as a first activity like this
arr_sheets = out_workbook.GetSheets.ToArray
–then use a FOR EACH activity where pass the arr_sheets as input, change the variable name from item to item1
–inside the loop now use READ RANGE ACTIVITY where in the sheetname mention as item1 and in the range as “” and get the output with a variable of type datatable named dt
–now use a OUTPUT DATATABLE activity where pass the dt as input and get the output with a variable as str_dt
–now follow these from there on
–next use IF condition like this
str_dt.ToString.ToUpper.Contains(“your keyword in uppercase”)
if true it will go to THEN part where we can use a MOVE FILE activity inside that
followed by this use BREAK activity it will stop iterating within this inner for each loop

Cheers @sparkplug93

13

What variable type should out_workbook be?

I’m getting an error under the assign step11

14

Hi
uipath.excel.workbookapplication is the datatype of out_workbook variable
where arr_sheets is a variable of type string array

Cheers @sparkplug93

15

What should the range be for Read Range? When I use item1 I get an error

16

image

Cheers @sparkplug93

17

I’m not exactly sure what happened. All the program did was open all my sheets. 13

18

is the type argument in property panel of FOR EACH is set as STRING
@sparkplug93

19

13

15
15582Ă—805 19.8 KB

Is the workflow nested properly

1 Like
20

fantastic
–you were almost done
–this can be resolved
–so instead of move file activity use ADD TO COLLECTIONS activity
Where in the collections mention as list_filepath and in the item property mention as item

list_filepath is a variable of type System.Collections.Generic.List(of string) with default value as new list(of string) defined in the variable panel

–now all the file path will be added to this list now
ensure that we have used BREAK activity next to this ADD TO COLLECTIONS activity (while we are still inside the inner for each loop)

–atlast to all the sequence use a final for each loop and pass the input as as list_filepath as input and change the variable name from item to files and the type argument as string in the property panel of FOR EACH activity
–inside the loop use MOVE FILE activity and in the PATH property mention as files and in the destination mention the folder path where we want to move that file

Cheers @sparkplug93