Parsing multiple lines of an email?

[Seuadr]

Hi all,

I am looking to build a flow to scrape text out of emails and either create, or update items in a sharepoint list.

as i think through this, one of the big challenges i am unsure of how to take on is that the content that i want from the email will be the same, but the location may differ.

so basically i'll get emails from a legacy system (not customizable, i checked. 😞 ) that could come in 1 of two flavors:

original recipe flavors (subject line and message content are the same): 

Monday, 04/05/2021 at 08:22:49. The Point Blah Blah Blah is at ALARM PRI 3

or

Monday, 04/05/2021 at 08:22:49. The Point Blah Blah Blah is at NORMAL

i think i can do an HTML to text off these to split out what i want

we also have new, bolder multiple lines per email (subject line is "X new messages":

Message 1:

Friday, 04/02/2021 at 18:00:12. The Point blah blah blah is in Alarm at PRI3.

Message 2:

Friday, 04/02/2021 at 18:00:12. The Point blah blah blah is in Alarm at PRI3.

Message 3:

Friday, 04/02/2021 at 18:00:12. The Point blah blah blah is in Alarm at PRI3.

Message 4:

Friday, 04/02/2021 at 18:00:12. The Point blah blah blah is in Alarm at PRI3.

what i'd like to be able to do is read down through each line after message #: and then have everything after each"The" be used for a conditional for create/update - so the 4 points here need to each be considered separately to see if they are existing items that need updating, or new items that need to be created in sharepoint.

i'm not really sure where to start with that.

[ChristianAbata]

hi @Seuadr  what you need to use is expressions: substring to get the data.

[Seuadr]

@ChristianAbata i'll look in to that, thanks! 

i am guessing that i'll need to have a substring expression for each "message #" - the subject line will say how many there are - recommendation on how to use that to generate the correct number of substring expressions, or is there a way to have them just iterate through the body until there are no more?  

[ScottShearer]

@Seuadr 

In addition the the suggestion from @ChristianAbata I believe you can achieve your goal by using the split() expression.

[ScottShearer · Accepted solution]

@Seuadr 

See my example below using split() and let me know if you have any questions.  I could have combined Steps 1, 2 and 3 but have them in separate Compose actions for clarity.  You can insert your logic to conditionally update the list after Step 3 in the loop.

[Seuadr]

@ScottShearer wow, this is a great start, thanks! i'll give this a go and follow up if i run into any trouble, thanks!

[Seuadr]

@ScottShearer this works great for separating them out, thanks! 

[Seuadr]

Oops. detail i forgot for include - the points in question may have '.' in them multiple times.  is there a way to have it only include the first one? is that how trim(first()) works?

[ScottShearer]

@Seuadr 

You could use first() assuming that it is the first element in the array.  Otherwise you can use square brackets and the element that you want to reference.  Note in my example that I am using [1] in Step 3 to designate the second element in the array.  The first element in the array is [0].

[Seuadr]

@ScottShearer ok, i have this working for the most part, couple questions:

if i use the "step 3" compose, the create item in sharepoint function creates 3 copies of the same item instead of the 3 separate messages. i discovered that if i use the "Current Item" from "apply to each" then i get instance of the apply to each from the message inserted as a separate item in the sharepoint list, but, the test email that i am using for the trigger only has 3 messages and it applies 4 times, putting an empty line as the first item created.

here is the email i have been using for testing:

Message 1:

Monday, 04/05/2021 at 08:45:23. The Point ANT.REF 1370A POULTRY.ALARM is in Alarm at PRI3.

Message 2:

Monday, 04/05/2021 at 08:45:23. The Point ANT.REF 1345C CARCASS.ALARM is in Alarm at PRI3.

Message 3:

Monday, 04/05/2021 at 08:45:29. The Point ANT HVAC11 SFAN is in Alarm at PRI3.

now, using the current item instead of the compose means steps 1 - 3 are for naught, so i'd prefer not to have to do that - i also tried adding another apply to each with the create item function under that, but, that doesn't work because it appears to be expecting the step 3 output to be an array. 

not sure how to approach this?