Extract Email Text and Add to Excel

[fscutaro]

I am trying to extract text strings (entire lines of text) from emails that come in from prospective customers filling out a form. All the entered information is dumped into the body of the email as text. The format of the email is; first line is the header (e.g. 'name') and the line right below it is the information (in this case, the name the user has entered into the form). Then a space, then the next header line (e.g. 'address') and the line that follows is the address the user has entered.

I've created a flow that successfully extracts the text and enters it into an (pre-formatted) excel spreadsheet.

However, one of the questions is a conditional ('are you located in the United States') and if the user enters 'no', then the form (and subsequently email) adds two lines; 1) the header 'country' and 2) the country the user enters.

I can't figure out how to create the conditional expression that will either look for the 'no' and add the two extra lines, or calculate that there are two extra lines and parse the text appropriately so that it ends up in the correct column in the spreadsheet.

An added functionality I would like to include is after the conditional lines, there is information on the source page. If I can accurately capture this source page information, it would be very helpful.

I've included a couple of screen shots to help demonstrate what I am referring to (note that in order to mask the data, I mocked up the emails. In reality, all the spacing in the emails is the same, with the exception of the conditional question adding two lines and a space.

Any help would be appreciated!

[Amit_Sharma]

Hi @fscutaro 

Please Confirm this is your email body which you have received?

[fscutaro]

Yes, that is the body of the email. To mask the identity, I've changed the information and had to cut & paste into pdf. So the lines may not match. I can send you the HTML to text conversion in a notepad file, if you'd like.

[Amit_Sharma]

Please send me HTML to text conversion text

[fscutaro]

Here it is. Note that I've changed the data to mask the individual's private information. Hopefully that doesn't impact the file. Also note that I had originally deleted the first line (so the actual extract would have an additional line on top). Lastly, this platform does not support .txt files, so I had to copy/past into a .doc file.

[Amit_Sharma]

Hi @fscutaro 

Please confirm this is your text Sample which always get after converting HTML to text.

[fscutaro]

I do not have "Logo" as the first line of text.

My first line of text is  "A visitor to www.website.com",

[Amit_Sharma]

Hi @fscutaro 

Ok, I will create sample for you

[fscutaro]

FYI, the attached is the text output for the alternate condition (someone outside the US). It shows the extra lines in the text output that need to be addressed with the conditional expression.

[Amit_Sharma]

Hi @fscutaro 

After Converting your text get in Compose Action.

Then Use Another Compose Action In you flow rename to Split and use expression.

split(outputs('Compose'),',')

Then Use Add row into table Action.

First Name

trim(replace(outputs('Split')?[4],'"',''))

Last Name

trim(replace(outputs('Split')?[6],'"',''))

Email

trim(replace(outputs('Split')?[8],'"',''))

Phone

trim(replace(outputs('Split')?[10],'"',''))

Specialty

trim(replace(outputs('Split')?[12],'"',''))

Zip

trim(replace(outputs('Split')?[14],'"',''))

Facility Affiliation

trim(replace(outputs('Split')?[16],'"',''))

Healthcare Designation

trim(replace(outputs('Split')?[18],'"',''))

NPI Number

trim(replace(outputs('Split')?[20],'"',''))

Reasons for Contact

trim(replace(outputs('Split')?[22],'"',''))

Is your organization based within the United States?

trim(replace(outputs('Split')?[24],'"',''))

Output:

Complete Flow

--------------------------------------------------------------------
If I have answered your question, please mark post as Solved.
If you like my response, please give it a Thumbs Up.

Contact me if you are interested in custom Power Automate development.

[fscutaro]

Hi Amit,

Thanks for sending this information. I was unable to get the automation to run properly, however I played around with what you sent and was able to streamline the flow a bit. The issue is that the output into Excel was not what I was looking for.

I've include the new flow below and the emails/output as attachments. Please let me know if you can take a look and let me know what I am doing incorrectly. Thanks.

Frank

Flow:

fx = split(outputs('Html_to_text')?['body'], outputs('NewLine'))

Outputs = SplitIntoLines

fx = length(trim(item()))

First Name

fx = trim(replace(outputs('SplitIntoLines')?[4],'"',''))

Last Name

fx = trim(replace(outputs('SplitIntoLines')?[6],'"',''))

Email

fx = trim(replace(outputs('SplitIntoLines')?[8],'"',''))

Phone

fx = trim(replace(outputs('SplitIntoLines')?[10],'"',''))

Specialty

fx = trim(replace(outputs('SplitIntoLines')?[12],'"',''))

Zip

fx = trim(replace(outputs('SplitIntoLines')?[14],'"',''))

Facility Affiliation

fx = trim(replace(outputs('SplitIntoLines')?[16],'"',''))

Healthcare Designation

fx = trim(replace(outputs('SplitIntoLines')?[18],'"',''))

NPI Number

fx = trim(replace(outputs('SplitIntoLines')?[20],'"',''))

Reasons For Contact

fx = trim(replace(outputs('SplitIntoLines')?[22],'"',''))

Country US?

fx = trim(replace(outputs('SplitIntoLines')?[24],'"',''))

Country (other)

fx = trim(replace(outputs('SplitIntoLines')?[26],'"',''))

Date Received

fx = formatDateTime(parseDateTime(split(triggerOutputs()?['body/receivedDateTime'], '+')[0]), 'MM/dd/yyyy', 'en-US')

[Amit_Sharma]

Hi @fscutaro 

Please Create a Flow Same as to me After Converting the html text take Output in Compose Action then follow my flowing steps as shown in my below reply.

- If you want to apply condition on mail body to check is empty or not you can use trigger condition for that for now first create your flow as same to me. 

[fscutaro]

Amit,

OK, I was able to replicate the flow. However, I discovered that in some cases, multiple responses are entered and that results in an incorrect extract. See the below:

1 line:

2 lines:

My thought is to search the field names and have Power Automate select all the text between names (terms), after I’ve converted the email body to text from HTML, instead of returning the line of text.
So my questions are; 1) Is that a sound approach and 2) what expressions should I use for that action?

Please let me know your thoughts. Thanks.

Frank