Search a folder containing multiple spreadsheets for substring and return the entire string

Hello. Great forum, I’ve learned quite a bit and have had some success in automating some browser and file directory processes. But I’m having trouble translating a concept to application for the the following requirements, I’ve played with Use Excel File, For Each Excel Row, For Each File in Folder and Find/Replace Value actions but can’t seem to figure out the correct combos. I’ve looked at several existing topic threads but I’m not sure that any scenarios are the same. Any help would be greatly appreciated.

• I have a list of keywords in an excel worksheet
• I have a folder containing many Excel workbooks in .xls and .xlsx formats. Each workbook can have multiple worksheets, with no standard worksheet naming conventions
• I’d like to understand if there’s a way to search each of these workbooks for each keyword/substring. For each workbook, the keywords could occur multiple times on multiple worksheets. Ideally, for any given matches on the keywords, I’d like to capture the following:
o The Keyword (substring) that matched (required)
o The string that the substring occurs in (required)
o The filename that includes the substring (required)
o The worksheet on which the substring occurred in (very nice to have)
o The cell in which the substring occurred (nice to have)

The input would need to be an excel file using a simple list. And the output would ideally be able to be recorded in the same excel file on another sheet (note the red text is just to demonstrate the keyword matches, sheet formatting is not needed). An example of spreadsheet input and output is below:

Many thanks in advance for any guidance!

Hi @wadger
Welcome to UiPath Community :slight_smile:

You need first to read all files in the folder. To do that, you need a loop for the folder. This video could help you further: UiPath | Files in Folder to Data Table | How to get files from folder | VB.Net | Loop For Each File - YouTube
or this video: UiPath | Get all Files from Directory and Subfolders | Get File information | Get Size of file - YouTube

Then you need to create a loop. In this loop you need to open Excel. For Each Excel File you need to get the worksheets of the Excel file. And for Each Sheet you need to read the data. This data you need to compare with your keywords and if something matches, save the name of the excel file and the name of the sheet in a new DataTable.

At the end you can write this DataTable in Excel file and you have your data.

Watch the videos in this channel. They would help you with your request.

Best regards
Mahmoud