Parsing email data

[Anonymous]

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

[edgonzales]

@Anonymous 

Hi there.  Will the eMails always be formatted exactly like this?  If so, you could use a split() function to parse the subject at "customer " (note the space) and the body at " on ", and then again on the period (after your time).

Here's a quick video on that function to get you started:

Keep us posted.

-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.

[Anonymous]

Ok.  I think my problem starts before the actual parsing.  

The template I'm working with requires that I add Html to text as the action before Inserting a row.  Should I be selecting both the subject & the body as dynamic content?  Then in the insert row field, I created the split function, but i'm still getting the entire text from the email as the result on the google sheet.

[edgonzales]

@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.

[Anonymous]

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.

[Anonymous]

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.

[edgonzales]

@Anonymous 

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

-Ed

[Anonymous]

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

Compose Action#2   split(triggerBody()?['body'],' on ')

[edgonzales]

@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.

[Anonymous]

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

[edgonzales]

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.

[Anonymous]

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

[edgonzales]

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