Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / Parsing email data
Power Automate - Building Flows
Unanswered

Parsing email data

(0) ShareShare
ReportReport
Posted on by Community Power Pla...

I am using Microsoft flow to bring email data into a spreadsheet. I need to pull customer # from the subject (alpha &/or numeric string always at the end) & date/time stamp from body. Completely ignorant as to how to use expressions to parse the data I need. Can someone help?

Below are examples of said subject & body:

Subject: 00:02:52 - Michael Weslosky truck 803690(610) SN: G85A210B2E5C arrived at customer V7880

Body: David Shiflett, 796969(613) SN: G8FC2109E436 arrived at 62519 - 1046 Millwood Pike, Winchester, VA 22602, USA on 06/12/20 20:56:46. The odometer reading was 160,566 miles at the time of arrival.

 

 

Thank you

  • Ed Gonzales Profile Picture
    Ed Gonzales 4,531 Most Valuable Professional on at
    Re: Parsing email data

    That may be a function of the spreadsheet program you're using (Excel, Google Sheets).  Sometimes, they'll use a leading apostrophe to indicate text rather than a number.

     

    To verify, look at past the past runs of your flow and check the output of each of the Compose actions.  If you don't see the apostrophe before the data, then it's probably happening in the spreadsheet.  If it is there, then you can wrap the whole expression in a replace() function.  It will get a little weird because JSON (the 'language' Power Automate is using to pass the information) will use single quotes to isolate the values and I'm not sure how you'd get around that. 

     

    Check the output of your past runs, maybe post a pic.  But it might be better to start a new thread with that one.

    -Ed

     

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Parsing email data

    One last question, Ed.  I see there is a leading ' apostrophe in each cell containing the result.  Can this be avoided?

  • Ed Gonzales Profile Picture
    Ed Gonzales 4,531 Most Valuable Professional on at
    Re: Parsing email data

    It will come over time.  As you get more comfortable with expressions, you'll be able to branch out and try new things...the video above covers split pretty well, I think...but here's a link to all of the functions and how to use them:

     

    Reference guide to using functions in expressions for Azure Logic Apps and Power Automate

     

    Also, there are lots of third-party tools that offer a friendly interface to do the same sort of thing.  If your messages start getting much more complex, check out things like Parserr and the like.  They interact with Power Automate but make getting data a bit easier for the "non-coder".

     

    Good luck!

    -Ed

     

    If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Parsing email data

    Awesome!  Thank you for your help, Ed!

     

    Now I just need to understand what the language means and then I can create my own.

     

    This helps tremendously, thank you

  • Ed Gonzales Profile Picture
    Ed Gonzales 4,531 Most Valuable Professional on at
    Re: Parsing email data

    @Anonymous 

    Great work!  Ok, so it looks like "RYDER" is the customer number you want, ya?  Change your first expression to this (I bolded the new part):

     

    split(triggerBody()?['subject'],'customer ')[1]

     

    This will tell the split() that you are only interested in the second part of the original subject.

     

    For the date/time bit, we can do the same thing:

     

    split(triggerBody()?['body'],' on ')[1]   - But that will still leave some things to trim off.  Here's what I expect you'll get:

     

    "06/15/20 20:42:11. The odometer reading was 164,851 miles at the time of arrival.", so we'll need to split this again at the period and take the first part this time.  Here's what the whole expression might look like:

     

    split(split(triggerBody()?['body'],' on ')[1],'. ')[0]

     

    Same idea applies here, we are splitting the result of the first split at the period, and telling it we only want the first part this time.

     

    Give those a try and let's see what happens.  

     

    Good luck!

    -Ed

     

    If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Parsing email data
    Compose Action #1  split(triggerBody()?['subject'],'customer ')
     
    Compose Action#2   split(triggerBody()?['body'],' on ')
  • Ed Gonzales Profile Picture
    Ed Gonzales 4,531 Most Valuable Professional on at
    Re: Parsing email data

    @Anonymous 

    You are very close.  Can you post the expressions that you're using inside the compose actions?  

     

    -Ed

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Parsing email data

    the long and short of it is, I need the 1st column to produce the customer #, the 2nd column, the date time stamp, like below:

     

    56880 6/11/2020 17:13:39

     

    I thought it was pretty straight forward, but I've learned in short time, it is not.

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Parsing email data

    Thanks, Ed.  I appreciate the help.  I just tried what you described in the 4 steps.  I created the compose actions, then added the outputs to the google sheet.  These are the results I'm getting on the sheet.  Still missing something critical, but I'm learning, I think.

     

    customer  ["20:42:11 - Judson Brunot truck 795292(604) SN: G848210B3656 arrived at ","RYDER"]

     

    date/time    \\r\\nJudson Brunot, 795292(604) SN: G848210B3656 arrived at RYDER - 2216 N Magnolia Ave, Ocala, FL 34475, USA\",\"06/15/20 20:42:11. The odometer reading was 164,851 miles at the time of arrival.

     

    there is more html, but the site won't let me post it.

  • Ed Gonzales Profile Picture
    Ed Gonzales 4,531 Most Valuable Professional on at
    Re: Parsing email data

    @Anonymous 

    So, you probably won't need the HTML bit.  Here's what I am picturing:

     

    1.  Your trigger - When an email arrives?  Not sure what that is, but you'll probably want a way to make sure this isn't triggered with every email, right?

    2.  A Compose action to capture the customer number from the Subject.

    3.  A second Compose action to capture the date/time from the body.

    4.  Your Google Sheet action to add/update a row using the data from the above Composes.

     

    I'll build a quick thing to show the exact expressions, but that's a general idea.  Sound good?

    -Ed

     

     

    If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

     

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

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans