Skip to main content

Notifications

Power Automate - Using Flows
Answered

How to search an Excel file and retrieve data

(0) ShareShare
ReportReport
Posted on by 12
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.
  • JL-21120807-0 Profile Picture
    JL-21120807-0 12 on at
    How to search an Excel file and retrieve data
    Thank you  David_MA.
     
    I appreciate 
     
    Basically the query is Select CompanyCode  from  table1 where  EmailID = SenderEmailID. 
  • David_MA Profile Picture
    David_MA 9,039 on at
    How to search an Excel file and retrieve data
    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.
  • JL-21120807-0 Profile Picture
    JL-21120807-0 12 on at
    How to search an Excel file and retrieve data
    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
  • Verified answer
    David_MA Profile Picture
    David_MA 9,039 on at
    How to search an Excel file and retrieve data
    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:
    1. Create a flow that triggers when a new mail arrives.
      1. Configure it with a trigger condition where the subject or body contains one of the companies.
      2. 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'))
    2. The initialize a string variable named Company.
    3. Add a condition to check if the body or subject contains ABC.
      1.  For example, or(contains(triggerOutputs()?['body/subject'], 'ABC'), contains(triggerOutputs()?['body/body'], 'ABC'))
      2. If yes, set the company variable to ABC.
      3. In no, add a condition to see if the body or subject contains XYZ.
        1. If yes, set the company variable to ZYZ.
        2. 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.
        3. 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.
    4. 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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard

Featured topics

Restore a deleted flow