Automating Email Text To Excel Rows- Issue With Splitting Text

[LCarpenter]

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:

[mattias1172]

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.  

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]

@mattias1172 

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

(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
City	Livin'
State	Of Denial
Email Address	john.doe@emailaddress.com

Thanks for the help!

[mattias1172]

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

Variable Secondary

Variable Legal

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:

[LCarpenter]

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 · Accepted solution]

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:

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

The very basic email I used:

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

[LCarpenter]

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