How to search an Excel file and retrieve data

(0) Share

Report

Posted on by JL-21120807-0

Hello

Please assist me. Thanks

I have an Excel file say on OneDrive, with columns as below:

Company Manager Administrator

ABC Bob Rick

XYZ Bill Sue

UVW Jill Jack

When I receive a mail, if the Subject or Body contains the Company ( ABC or XYZ or UVW as per above table)

1: Look for the Manager and Administrator in the Excel Sheet

2: Get their Email Addresses

3 Send a mail to the Manager with the original mail as attachment

4: Forward the original mail to the Administrator

Thank you.

Categories:

Using flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (1)

Report
This is not something I would recommend doing since using contains on the body and subject of an e-mail could cause false positives. It would be better to check if the domain of the e-mail address is the one from the company you want.

But, you can do the following:

Create a flow that triggers when a new mail arrives.

Configure it with a trigger condition where the subject or body contains one of the companies.

It would be something like this: @or(contains(triggerOutputs()?['body/subject'], 'ABC'), contains(triggerOutputs()?['body/subject'], 'XYZ'), contains(triggerOutputs()?['body/subject'], 'UVW'), contains(triggerOutputs()?['body/body'], 'ABC'), contains(triggerOutputs()?['body/body'], 'XYZ'), contains(triggerOutputs()?['body/body'], 'UVW'))

The initialize a string variable named Company.

Add a condition to check if the body or subject contains ABC.

For example, or(contains(triggerOutputs()?['body/subject'], 'ABC'), contains(triggerOutputs()?['body/body'], 'ABC'))

If yes, set the company variable to ABC.

In no, add a condition to see if the body or subject contains XYZ.

If yes, set the company variable to ZYZ.

If no, set the company variable to UVW. This assumes you trust your trigger condition, so it only runs if the company is one of the three values specified. Otherwise, add another condition here.

Note: you can only go eight conditions deep, so if you really need this for more than the three fictitious companies you listed, you could have problems.

Add a list rows present in a table action with a filter on the company column in your spreadsheet is equal to the variable.

The flow will look like this:

This is the section with the conditions:

This is one of the expressions in the conditions: or(contains(triggerOutputs()?['body/subject'], 'ABC'), contains(triggerOutputs()?['body/body'], 'ABC'))

This should return the row(s) in your spreadsheet corresponding to the company.

If you make it this far and have more questions, just submit a new post specific to where you're getting stuck.

Was this reply helpful? Yes No
JL-21120807-0 73 on at

Like (0)

Report

David_MA

Thank you. The solution is technically feasible but If we have a limitation of 8 searches, it will not fit to my business case. I did not know this limitation, I thought it would loop in the Excel sheet ( some kond of For each...).

Real case:

Sql table name Contacts, with 2 columns: Company Code, Email address. Can contain 1000 or more records.

I need to search the email address of the sender of the mail in the table Contacts and retrieve the company Code in a variable. I will then use this variable to look for other static data in other tables.

I would like to optimize the search, once we get the company code, no need to further continue searching the table.

Could you guide me on the getting the Company Code? I highly appreciate

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

You will probably want to submit another post asking specifically about your question for searching a SQL database. However, I would imagine it will not be much different than I listed below with using a query filter to get a row from an Excel spreadsheet. I don't have any workflows that connect to SQL directly. For SQL data, I synch the data to SharePoint and use the SharePoint actions.

If the SQL database contains the e-mail address to lookup in SQL, the workflow should be rather easy. I found this on the topic: (6) Execute a SQL Query Action in Power Automate | LinkedIn. It looks rather straightforward and utilizes the SQL WHERE command to filter.

Also, here is a link on the limitation in Power Automate regarding nested actions: How to solve the nesting limit of '8' error in Power Automate. This would have affected my suggestion below with using conditions to check the body and subject of the e-mail for the company. But you will not need to do that if you use the sender's e-mail address since there can only be one sender of an e-mail.

Was this reply helpful? Yes No
JL-21120807-0 73 on at

Like (0)

Report

Thank you David_MA.

I appreciate

Basically the query is Select CompanyCode from table1 where EmailID = SenderEmailID.

Was this reply helpful? Yes No