cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LCarpenter
Frequent Visitor

Automating Email Text To Excel Rows- Issue With Splitting Text

Good morning,

 

I'm trying to parse some text from email into an excel spreadsheet. You can see my first post here, the basic premise is we're sending out text forms for users to fill out and send back. We want to automate the responses into an Excel spreadsheet. Some of the questions are the same (first name, last name, etc.) but I want them to go into different rows. @ScottShearer was kind enough to give me a starting point.

 

After doing some more research and trying to integrate the split into my flow, I'm forced to admit that I have no idea what I'm doing, or what I'm supposed to be writing as an expression to get this to work.

Would someone please show me the expression I should use, and where it should go? I'd appreciate it. I'm not a programmer and work best with visual examples.

Here is the flow I have so far:

 

example2.png

1 ACCEPTED SOLUTION

Accepted Solutions
mattias1172
Responsive Resident
Responsive Resident

I was able to setup a very small scale test to get the gist of the expressions for you. One thing to note, special characters like new line (\n) are going to be a pain. I had to shorten some of the paragraphs because the html to text action was splitting them even though the email was not. This can likely be corrected but would take more time to get the right filters. ***Upon further research, it appears "html to text" action adds a \n character roughly every 80 characters. Meaning if a string of text (like a paragraph) is one single line and it goes over this point, it will line break it (\n). Mostly not a big deal with 1 major exception. If the string you are splitting on gets split (adds a \n), it will break the filter since its not looking for a \n in the string. you could try adding a \n in the string or simply reduce the size of the string you are splitting at. Back to my "Primary Contact", "Secondary Contact" idea.***

 

The paragraphs i used to split:

* Preferred delivery method for receiving notices for the association:

Alternate or secondary delivery method for receiving notices for the association:

 

The expression for getting the first section:

trim(split(split(outputs('Html_to_text')?['body'],'* Preferred delivery method for receiving notices for the association:')[1], 'Alternate or secondary delivery method for receiving notices for the association:')[0])

 

The expression for getting the first name:

trim(split(split(outputs('Primary_Section'),'First Name:')[1],'Last Name:')[0])
 
The flow layout:
mattias1172_1-1645217072942.png

 

The result (hard to get everything in a screenshot):

mattias1172_2-1645217146109.png

 

The very basic email I used:

mattias1172_3-1645217197765.png

 

It may be helpful to use some sort of "header" for each section rather than trying to split at the paragraph. On your form, if you started the sections with something like below, you could split at those to get the bulk data instead. From the example below, in the expression, replace "* Preferred delivery..." with "Primary Contact" and "Alternate or secondary..." with "Secondary Contact". 

form.png

 

View solution in original post

6 REPLIES 6
mattias1172
Responsive Resident
Responsive Resident

Can you give an example for how the email body will look? 

 

One expression ive used to filter information in a Teams message (should also work for an Email) is this:

 

trim(Split(Split(body('html_to_text'), 'Ticket #:')[1], 'Date/Time:')[0])

 

For this one, I am trying to get the Ticket #. It splits the text first at the string "Ticket#:". The [1] denotes the second item in the generated list. Basically everything after "Ticket#:". The second split then takes the "everything after" and splits it at the "Date/Time:" string. The [0] denotes everything before the split. This leaves just "141375" which is between Ticket#:[1] and Date/Time[0]. 

 

If your email body is structured like this, you can copy, paste and modify the above expression.  

 

 
flow1.png
 
 
 
 
 
 
My actual flow uses the below expression which includes 2 replace functions to remove things like new line characters. 
 
replace(replace(trim(Split(Split(body('html_to_text'), 'Ticket #:')[1], 'Date/Time:')[0]),decodeUriComponent('%0A'),''),'\n','')
LCarpenter
Frequent Visitor

@mattias1172 

 

Certainly. The form we are sending out looks like so:

 

example.png

(And so on...)

 

As you can see, the problem is several different sections have the exact same wording. I need to keep the information from all the different iterations of 'First Name, Last Name' etc. from going into the same row. They need to go in different rows.

We haven't sent the forms out yet so I can't tell you exactly what they will look like, but going by what we have sent out in the past, probably something like this:

 

First Name

John

Last Name

Doe

Street Number

28 - 85

Street Name

 Somewhere

CityLivin'

State

Of Denial

Email Address

john.doe@emailaddress.com

 

Thanks for the help!

mattias1172
Responsive Resident
Responsive Resident

As Scott said on your original post, this is going to be ugly and get pretty confusing...

 

It's going to be a combination of what Scott said to start on, and what I said above. 

 

At your headers, (the paragraphs with the asterisk) you'll want to capture the text between them to get all of the primary info, all of the secondary info, and all of the legal info. This will effectively give you 3 variables containing all of the text of each section. 

 

Something like:

Variable Primary

 

mattias1172_0-1645206255095.png

Variable Secondary

 

mattias1172_1-1645206289655.png

 

Variable Legal

 

mattias1172_2-1645206305694.png

 

You can use what Scott said in the previous post to get this info. Basically the same thing I typed above but using the Last() and First() functions instead of the indexes [0] [1]. 

 

After getting the info into the 3 separate sections, you then need to run expressions against those to get all of the individual info. This is where what I said above comes into play. An untested example I'm just typing out:

 

split(split(outputs('Primary_Section'), 'First Name:')[1], 'Last Name:')[0]

 

This should get you the text for "First Name". 

 

You'd need a separate compose action for each piece of data you are trying to capture. For this example, the above expression would be in a compose action you could name "Primary First Name". 

 

An example to how the layout might look:

 

mattias1172_3-1645206857943.png

 

LCarpenter
Frequent Visitor

Thanks so much for your help, @mattias1172 !

 

 I'm setting up the compose expressions, but for the first step (separating out the variables) I keep getting back an 'invalid' response. If it wouldn't be too much trouble, would you mind writing out the exact expression I should use, so I can copy and paste?

Thanks again for your patience; I've never worked with any sort of automation system before this job.
mattias1172
Responsive Resident
Responsive Resident

I was able to setup a very small scale test to get the gist of the expressions for you. One thing to note, special characters like new line (\n) are going to be a pain. I had to shorten some of the paragraphs because the html to text action was splitting them even though the email was not. This can likely be corrected but would take more time to get the right filters. ***Upon further research, it appears "html to text" action adds a \n character roughly every 80 characters. Meaning if a string of text (like a paragraph) is one single line and it goes over this point, it will line break it (\n). Mostly not a big deal with 1 major exception. If the string you are splitting on gets split (adds a \n), it will break the filter since its not looking for a \n in the string. you could try adding a \n in the string or simply reduce the size of the string you are splitting at. Back to my "Primary Contact", "Secondary Contact" idea.***

 

The paragraphs i used to split:

* Preferred delivery method for receiving notices for the association:

Alternate or secondary delivery method for receiving notices for the association:

 

The expression for getting the first section:

trim(split(split(outputs('Html_to_text')?['body'],'* Preferred delivery method for receiving notices for the association:')[1], 'Alternate or secondary delivery method for receiving notices for the association:')[0])

 

The expression for getting the first name:

trim(split(split(outputs('Primary_Section'),'First Name:')[1],'Last Name:')[0])
 
The flow layout:
mattias1172_1-1645217072942.png

 

The result (hard to get everything in a screenshot):

mattias1172_2-1645217146109.png

 

The very basic email I used:

mattias1172_3-1645217197765.png

 

It may be helpful to use some sort of "header" for each section rather than trying to split at the paragraph. On your form, if you started the sections with something like below, you could split at those to get the bulk data instead. From the example below, in the expression, replace "* Preferred delivery..." with "Primary Contact" and "Alternate or secondary..." with "Secondary Contact". 

form.png

 

LCarpenter
Frequent Visitor

Thank you very much! I really appreciate the help. c:

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,913)