Help Create flow with EXCEL - Perform a data query.

[JuanManuelA]

Hello, I'm new to using Power Automate Desktop.

I would like to create a flow:

1. Open a text input where you can put text and hit ENTER.
2. Find that information in an excel like this:

3.a If it finds a value equal to the one provided to the Input text, a dialog box with the entire Row will be displayed on the screen.

3.b. It will show a message on the screen saying something "No field with that value was found."

Below is an example of my real practical case:

I provide any field in the input text, but in this case, for example, in the input text I put the IP (RED MARK) and I want the output message to be the entire row (BLUE MARK)  where that IP  is located.

[Agnius]

Have you tried doing anything yourself already? These forums are meant to help you when you get stuck, and not really for having other people build solutions for you for free. This is a community forum for finding answers to specific questions.

I'll provide some guidance, but please put in some effort yourself before asking further questions.

To provide inputs to your flow, you can use the Display input dialog action under the Message boxes actions group.

To find a value in an Excel file, you will first need to open the file via the Launch Excel action, and then use Find and replace cells in Excel worksheet. This will return a row index that you can use to check if it found anything (in an If condition action), and also can be used in a Read from Excel worksheet action to retrieve the other values of the same row and display them to you.

[JuanManuelA]

Hello @Agnius ,

Yes, I have tried to do everything I know with my little knowledge.
I exhausted options, I watched videos, I read posts, but I can't reproduce my case.

I always tend to fight with the problem.

But I wanted to make a simpler post so that it is well understood, since in previous posts of mine that you can see with Power Automate Cloud I tend to provide a lot of information and it does not seem to be very effective in helping to solve the problem.

I attach my current progress with my flow:

[Agnius]

Well, you are missing a few actions there. You have two options:

1. Since you are already reading the entire data table into %ExcelTable%, you could use Find or replace in data table instead of Find and replace in Excel worksheet. This will return row indexes for your data table. You can then use them to return the values to you and you can pass them into the message box.
2. If you removed the Read from Excel worksheet action, and simply used the Find and replace in Excel worksheet, you would then need to add another Read from Excel worksheet that only reads the one row, using the row index for the match, instead of reading the entire sheet.

[JuanManuelA]

Hi @Agnius 

This is my current flow.

Due to my limited knowledge, I do not understand the part of how to carry out this flow, but especially the part of getting the entire row of the value that matches the search.

Could you send a screenshot of what any of the cases would look like?
I really didn't understand how to do it, but maybe this way you can guide me.

[Agnius]

You will need to use the row index from %DataTableMatches%. It is a data table of its own that will contain column and row indexes for each match. So, you can get the first match as %DataTableMatches[0]['Row']%. 

Now, before you do that, you need a condition to check if it found any matches. So, use an If condition and check if %DataTableMatches.RowsCount% is greater than 0. If it is, then you found some matches and you can proceed further. If it's not greater to 0, it did not find any matches, and you should use the default message you defined earlier.

Now, if you do get some matches, use Convert text to number first on %DataTableMatches[0]['Row']% to convert it to a numeric index. No idea why the product people at Microsoft thought it was a good idea to store indexes as strings, but they did that, and we need to convert them to make any use of them.

After you have converted it and stored it, for example, in %RowIndex%, you can use it to retrieve your row. %ExcelData[RowIndex]% will return the entire row. You can push that to your Display message action.

[Deenuji]

@JuanManuelA 

I have slightly modified your workflow:

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Deenu\\Asset Details.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Display.InputDialog Title: $'''Enter''' Message: $'''Enter the text to search in excel''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> InputQuery ButtonPressed=> ButtonPressed
Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: ExcelData AllMatches: True ValueToFind: InputQuery MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: 1 DataTableMatches=> DataTableMatches
LOOP FOREACH CurrentItem IN DataTableMatches
    SET RowNo TO CurrentItem['Row']
    Text.ToNumber Text: RowNo Number=> RowAsNumber
    Display.ShowMessageDialog.ShowMessage Title: $'''Info''' Message: $'''Name PC: %ExcelData[RowAsNumber][0]%
SERIAL NUMBER: %ExcelData[RowAsNumber][4]%
MODEL PC: %ExcelData[RowAsNumber][4]%
''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
END

How to copy/paste the above code into your power automate desktop?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[JuanManuelA]

@Deenuji 

Hello @Deenuji 

The problem that arises is that I have several sheets in Excel, all with the same format but I need the query to be in the ENTIRE BOOK

Could you guide me again?

[VishnuReddy1997]

Hi @JuanManuelA ,

Please find the attached solution.

Please copy and paste the below code into you new flow.

Code:

Display.InputDialog Title: $'''Please Provide the IP''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> UserInput ButtonPressed=> ButtonPressed3
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Database\\Excel A.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.FindAndReplace.FindSingle Instance: ExcelInstance TextToFind: UserInput MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
IF FoundRowIndex <> 0 THEN
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: FoundRowIndex EndColumn: $'''M''' EndRow: FoundRowIndex ReadAsText: False FirstLineIsHeader: False RangeValue=> Output_Data
Display.ShowMessageDialog.ShowMessage Message: Output_Data Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
ELSE
Display.ShowMessageDialog.ShowMessage Message: $'''No field with that value was found.''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[JuanManuelA]

Hello @VishnuReddy1997 

The problem that arises is that I have several sheets in Excel, all with the same format but I need the query to be in the ENTIRE BOOK

Could you guide me again?

[VishnuReddy1997]

Hi @JuanManuelA ,

Please find the updated code for all sheets.

Please copy and paste the below code into you new flow.

Code:

Display.InputDialog Title: $'''Please Provide the IP''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> UserInput ButtonPressed=> ButtonPressed3
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Database\\Excel A.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
LOOP FOREACH CurrentItem IN SheetNames
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: CurrentItem
Excel.FindAndReplace.FindSingle Instance: ExcelInstance TextToFind: UserInput MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
IF FoundRowIndex <> 0 THEN
EXIT LOOP
END
END
IF FoundRowIndex <> 0 THEN
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: FoundRowIndex EndColumn: $'''M''' EndRow: FoundRowIndex ReadAsText: False FirstLineIsHeader: False RangeValue=> Output_Data
Display.ShowMessageDialog.ShowMessage Message: Output_Data Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
ELSE
Display.ShowMessageDialog.ShowMessage Message: $'''No field with that value was found.''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[JuanManuelA]

Hello @VishnuReddy1997 

And how could I make the information and specific fields look more user-friendly.

Maybe something like that?

In Deenuji's answer I saw that he attached something of a variable with the columns, how can I integrate something like that into the flow you propose.

[VishnuReddy1997]

Hi @JuanManuelA ,

Please replace the below updated code so it will display the results as shown in below image.

Please copy and paste the below code into you new flow.

Code:

Display.InputDialog Title: $'''Please Provide the IP''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> UserInput ButtonPressed=> ButtonPressed3
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Database\\Excel A.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
LOOP FOREACH CurrentItem IN SheetNames
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: CurrentItem
Excel.FindAndReplace.FindSingle Instance: ExcelInstance TextToFind: UserInput MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
IF FoundRowIndex <> 0 THEN
EXIT LOOP
END
END
IF FoundRowIndex <> 0 THEN
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: FoundRowIndex EndColumn: $'''M''' EndRow: FoundRowIndex ReadAsText: False FirstLineIsHeader: False RangeValue=> Output_Data
Display.ShowMessageDialog.ShowMessage Message: $'''Name PC : %Output_Data[0][0]%
IP: %Output_Data[0][1]%
EXT offi : %Output_Data[0][2]%
Extension 1 : %Output_Data[0][3]%
Extension 2 : %Output_Data[0][4]%
Ext. 3 : %Output_Data[0][5]%
Serial Number : %Output_Data[0][6]%
Model PC: %Output_Data[0][7]%
Proveedor : %Output_Data[0][8]%
Fecha Compra: %Output_Data[0][9]%
Processor : %Output_Data[0][10]%
RAM: %Output_Data[0][11]%
S.O Installation: %Output_Data[0][12]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
ELSE
Display.ShowMessageDialog.ShowMessage Message: $'''No field with that value was found.''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Deenuji]

@JuanManuelA 

You can also create custom form to show your result more user friendly. Please refer the below screenshot how to use that.

In case still if you require any support on your use case please let me know.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[JuanManuelA]

Hi, @VishnuReddy1997 

I am experiencing errors when searching for different Assets in my excel,

I attach tests carried out on the SAME SHEET searching for different PC NAMES examples.

Or it shows disordered or bad or empty data.
Some show well

On excel row 74  (WRONG):

On excel row 60  (GOOD):

On excel row 105  (WRONG):

[Deenuji]

@JuanManuelA 

With an assumption all sheets table columns are same, Please find the optimized flow and it will work for all sheets without an issue.

Code:

Display.InputDialog Title: $'''Enter''' Message: $'''Enter the text to search in excel''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> InputQuery ButtonPressed=> ButtonPressed
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Deenu\\NewAsset_Data.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
LOOP FOREACH Sheetname IN SheetNames
    Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: Sheetname
    Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
    Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: ExcelData AllMatches: True ValueToFind: InputQuery MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: 1 DataTableMatches=> DataTableMatches
    LOOP FOREACH CurrentItem IN DataTableMatches
        SET RowNo TO CurrentItem['Row']
        Text.ToNumber Text: RowNo Number=> RowAsNumber
        Display.ShowMessageDialog.ShowMessage Title: $'''Info''' Message: $'''Name PC: %ExcelData[RowAsNumber][0]%
SERIAL NUMBER: %ExcelData[RowAsNumber][4]%
MODEL PC: %ExcelData[RowAsNumber][5]%
''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
    END
END

Please let me know in case if you have any queries on the same.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[VishnuReddy1997]

Hi @JuanManuelA ,

Are you maintaining fixed template for storing details. If you maintain fixed template this issue will no occur.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy