You’re offline. This is a read only version of the page.
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,
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
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.
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],'"',''))
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')
Hi @fscutaro
After Converting your text get in Compose Action.
Then Use Another Compose Action In you flow rename to Split and use expression.
Share
Report
All responses (
Answers (split(outputs('Compose'),',')
Then Use Add row into table Action.
First Name
trim(replace(outputs('Split')?[4],'"',''))
Last Name
trim(replace(outputs('Split')?[6],'"',''))
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],'"',''))
trim(replace(outputs('Split')?[18],'"',''))
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.
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.
I do not have "Logo" as the first line of text.
My first line of text is "A visitor to www.website.com",
Hi @fscutaro
Please confirm this is your text Sample which always get after converting HTML to text.
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.
Please send me HTML to text conversion text
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,004
Most Valuable Professional
