Taking multiple pieces of data from an email body to run conditions on

[snydertl]

Hi all,

I've searched for a couple of days now but haven't found anything that matches what I am trying to do.  We have someone that gets a weekly email with a bunch of information about new hires.  This person wants to take the name of each new hire and send them a welcome email.  The issue arises when there are multiple (can be over 50) names in the email.  Here is a sample (not actual data).

I know I need to do a split from the Name: text and take the content after the Name: and put it in an array variable, but I can't make it work.

Any help would be appreciated!

[AlexEncodian]

@snydertl 

You could do the following:

1) Use split() function using delimiter "Name:"

Power Automate Split Function Explained With Examples (zeitgeistcode.com)

This will return an array of strings, each representing a new hire just with the "Name:" missing at the start

2) Encodian's Utility - Extract Text between Values action allows you to easily parse the name:

Set up the parameters with Start Value Blank (i.e. start value is the beginning of the string) and the end value as "Personnel No.:"

This will extract the string. You can choose to trim the result (removes whitespace incl line breaks from the result):

Loop through the array of strings and repeat step 2.

Note, Encodian will soon release an action: "Utility - Extract All Texts between Values" so you don't have to do a split() or loop through the split() result after. You could do everything in one step.

You can also do step 2 with native parsing string expressions in Power Automate.

[grantjenkins]

Is there more content before and/or after this new hire data in the email? It would be good to see an example of the full email that would come through to ensure we get what you're after.

Also, will it always include 13 rows of data for each person, and would the data always be in the same order? Name, Personnel No, Sub Personnel No, Home Phone, etc.

[snydertl]

@grantjenkins  what you see in the email snippet is just repeated for each new user, so those two sections are the full information provided for each user with ORCID being the last "field".  Here is an example of the text itself.

*** Automated E-Mail - Please do not reply ***

Report-To-Work List
November 06, 2023

BUSINESS PERSONAL
Name:	Smith, Joe Allen
Personnel No.:	01234567
Sub Personnel No.:	N/A
Home Phone:
Status/Employee Group:	New Hire / 1 REGULAR FULL TIME
Work Site Type:	ON-SITE
Employee Subgroup:	E1 EXEMPT
Directorate:	Wild Systems Sci 52525252
Division/Org Unit:	Egg Sciences Division 50000000
Job Title:	Technical Assistant Staff Member (15151515)
Position:	Software Developer (300000000)
Citizenship:	USA
ORCID:
Name:	Jones, Betty Ann
Personnel No.:	0235678
Sub Personnel No.:	N/A
Home Phone:	(865)111-2222
Status/Employee Group:	New Hire / 1 REGULAR FULL TIME
Work Site Type:	ON-SITE
Employee Subgroup:	E1 EXEMPT
Directorate:	National Kitten Directorate 65498754
Division/Org Unit:	Kitten Proliferation Division 12345857
Job Title:	Technical Assistant Staff Member (5546789)
Position:	Full Kitten Developer I (11111111)
Citizenship:	USA
ORCID:
Name:	Doe, Martha
Personnel No.:	03456789
Sub Personnel No.:	N/A
Home Phone:	(865)222-3333
Status/Employee Group:	New Hire / T TEMPORARY FULL TIME
Work Site Type:	ON-SITE
Employee Subgroup:	E1 EXEMPT
Directorate:	CC Sciences Dir 12345678
Division/Org Unit:	Really long name Scienc 55555555
Job Title:	Post Doc (51515151)
Position:	Postdoctoral Research Associate - Advanc (20000000)
Citizenship:	Peru
ORCID:	0000-0003-1111-2222

[snydertl]

@AlexEncodian 

Thank you for the answer, however, my cyber security department will not allow third party connectors. 

[snydertl · Accepted solution]

One of my coworkers came up with a solution that was a bit crazy but works!

1. When new email arrives - find based on Subject

2. HTML to Text - convert body to text

3. Initialize variable - string to hold the names 

4. First Compose - expression - last(split(body('Html_to_text'),'BUSINESS PERSONAL')) 

Business Personal was a line in the email before the table with the name information.

5. Compose - expression - 

uriComponentToString(replace(uriComponent(outputs('Compose')), '%0A', ''))

Believe this is to get rid of spaces from the output of the Compose above.

6. Compose - expression - 

split(replace(outputs('Compose_4'),'    Name',';Name'),';')

This is modifying the output from the Compose in step 5

7. Filter Array - Take output from Compose in step 6.  Filter out anything that is empty - 

empty(item())

8. Apply to Each - take the body from the Filter Array.

9. Compose - expression - 

replace(first(split(items('Apply_to_each'),'Personnel')),'Name:','')

10. Set Variable - set the variable you initialized in Step 3 to contain the Output of the Compose in step 9. 

11. Use the names as you need.

I then took the names and did a lookup in Azure for their email addresses.  Then sent them an email.  That's it! Hope this helps someone else!

[grantjenkins]

Hi @snydertl. Sorry for not getting back to your post. Just for reference, this is how I'd have built it.

See full flow below. I'll go into each of the actions.

When a new email arrives triggers on new email.

Html to text converts the body of the email to text.

Filter array extract the data and splits on new line, then removes any empty rows of data.

//From
split(last(split(outputs('Html_to_text')?['body'], 'BUSINESS PERSONAL')), decodeUriComponent('%0A'))

//Condition
trim(item())

Compose contains an expression to extract out all the names into an array. It converts the data to XML then uses XPath to get the names.

xpath(
    xml(json(concat('{"root": { value:', body('Filter_array'), '}}'))), 
    '//root/value[. = "Name:"]//following-sibling::value[1]/text()'
)

After running the flow we would get the following output.