Replace Multiple Values in Excel Based on List of Input Values

[UiPath]

I want to open an excel file go to second sheet use the txt or .xlsx file to find specific values in Column A rows and replace the value in the Column L row.

e.g I find the Number1 in the excel file from a list of input values and replace the value in column L in that row with custom text that is in the input file. Then I will look for Number2 and replace the value with custom text in input file, after all numbers are replaced in the sheet switch to sheet1 save and close the excel.

Excel File:

txt.File

Is it possible to do that with Power Automate Desktop?

[ryule]

Yes, use this action to get your row index where your "Number1" value exists, and then you can then use the other Excel actions to update values using that row index and the columns you want to update

[UiPath]

How to switch between sheets?

[ryule]

[UiPath]

I started to create the flow. 

First step is to open excel files in a folder. I created an input variable %ExcelFilesFolder% that opens a .txt file. The txt file has list of excel files path. e.g C:\5 Sep 2022\Testing\From\names of excel.xlsx
I refereed the variable in Launch excel.

But, It opens the .txt file list in excel rather than opening each individual excel file.

Should I create a For Loop?

Step 1

Step 2

[ryule]

I'm not really following your question. But if you are saying that you have a list of Excel files in a text file, yes, the Launch Excel action can open a tab delimited text file, you can then Read from Excel for that new Excel instance, and then use that output to get started.

[UiPath]

I mean this now it opens each excel file. Now I get to the hard part of find and replace.

I don't see find and replace in my desktop 

[ryule]

You have to expand the "Advanced" section

[UiPath]

I found it now. 

I managed to replace by entering single number to Text to Find then followed by write action.
I am stuck at Find and Replace >> Text to Find 
In my case, I have input as a list of values and associated new text.
I need to check one value at a time, if excel value has a match with the values in input file. I would replace the text in column L with new text that is specific to that number. I do that for all the numbers in the excel agaist my input list finally save and close.

How to I refer my input file that has list of values and associated new text.

Do I need to loop through the input file?

[ryule]

Yep, loop through it.

If your input file is two columns (one with the value you are searching for and the other the value you are going to write), you'd use %CurrentItem[0]% as the value to search for in Excel, and then %CurrentItem[1]% in your write action

[UiPath]

I created an input list variable with default value as C:\path\files.txt it won't accept. Input variable has to be List or Object or Data table. Actually, I want the input files to be an excel file because replace text has line breaks after each line. 

This is my flow so far. Excel file would be my input file.

[ryule]

You first have to open C:\path\files.txt in a separate excel instance and read the data into a datatable/list

[UiPath]

But both List and Database are accepting single line of text. If I have Text with line break second line won't appear?

[ryule]

The text file needs to be properly delimited. So assuming you are configuring this prior to the bot running, I would suggest you update it in Excel, then save it as a Tab delimited TXT file, or just leave it as an excel file.

[UiPath]

Delimiter will separate columns, but my issue is with cell content. e.g see screenshot below 

[ryule]

I just mocked up a spreadsheet and it returned the full cell