Unanswered

Extract Email Text and Add to Excel

(0) Share

Report

Posted on by 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!

Categories:

Building flows

Contact Info_SAMP...

Power Automate In...

I have the same question (0)

All responses (12)

Answers (0)

Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
fscutaro 16 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like (0)

Report

Please send me HTML to text conversion text

Was this reply helpful? Yes No
fscutaro 16 on at

Like (0)

Report

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.

HTML to text.docx

Was this reply helpful? Yes No
Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
fscutaro 16 on at

Like (1)

Report

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

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

Was this reply helpful? Yes No
Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like (0)

Report

Hi @fscutaro
Ok, I will create sample for you

Was this reply helpful? Yes No
fscutaro 16 on at

Like (0)

Report

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.

HTML to text_cond...

Was this reply helpful? Yes No
Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like (0)

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

Was this reply helpful? Yes No
fscutaro 16 on at

Like (0)

Report

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')

Contact Info_SAMP...

Emails and Output...

Was this reply helpful? Yes No