Parsing Values from Email with Various Amounts of Data (Array)

[MDE94]

First of all, apologies for the poor explanation and terminology prior to illustrating my problem. By following a guide I've been able to parse values of an email by converting the body to plain text (html to text), then turned the plain text values into an array by which I can extract each value per line, and add this into a row within Excel.

The problem I'm faced with, is that every email I receive is in the same format, but the amount of information and therefore the number of lines in the array, would vary from one email to another. For example an email may contain:

Name: Full Name1 

Age: 20

Location: South

Name: FullName2

Age: 21

Location: North

This converted into an array may look like so:

[1] Name:

[2] Full Name1

[3] Age

[4] 20

[5] Location:

[6] South

[7] Name:

[8] Full Name2

[9] Age

[10] 21

[11] Location:

[12] North

The values pulled from the above email would be [2]. [4]. [6]. [8]. [10] and [12].

An email may contain only one set of data, or potentially 10 sets of data - in circumstances where only one set of data is received, the current array would document unnecessary values from lines that do not exist (or exist but are irrelevant). Or on the flipside of this, may not account for lines [14] and beyond if more than 2 sets of data is received. 

I'm happy to follow any existing tutorial on the issue at hand, again forgive my poor wording on the matter but I hope you can read between the lines and assist in any way. Please feel free to ask any questions, in a nutshell I'd like to know if an array can be produced which accounts for data that may or may not be there from one email to another. 

[v-dezhili-msft]

Hi @MDE94 ,

Do you want to correctly fill the content of the email into the Excel file?
I did a test for your reference. My email is as shown in the figure below. Every 3 lines in the email are information about one person, and there are 4 people in total. At first, I used Split() to divide them with '\n', and then use chunk() groups them in 3 rows, then stores each group in an array, and finally saves them in Excel.

chunk(split(outputs('Html_to_text')?['body'],decodeUriComponent('%0A')),3)

Best Regards,

Dezhi

[MDE94]

Hello @v-dezhili-msft , many thanks for this reply - I illustrated my problem poorly, your example is perfect however the format of the elements in my email aren't as I described. it is more like:

First Name

username1

Age

22

Location

south

First Name

username2

Age

23

Location

south

First Name

username3

Age

24

Location

south

First Name

username4

Age

25

Location

south

How could the Compose 2 in your example be configured so its 3 groups of information, but on 6 lines? I need to grab every value one line below 'Firstname', 'Age' and 'Location'. Thank you for your help, this will be accepted as solution regardless. I basically know that from each 'First Name' an array of grouped values should begin, capturing whatever is below this line. Then whatever is below 'Age', 'Location' etc, the first hurdle would be how to capture each value on a line below a certain string value in the email, I have seen plenty examples parsing data using split 'First Name:' for example - which is no good for my situation 

[MDE94]

Hello, many thanks for this reply - I illustrated my problem poorly, your example is perfect however the format of the elements in my email aren't as I described. it is more like:

First Name

username1

Age

22

Location

south

First Name

username2

Age

23

Location

south

First Name

username3

Age

24

Location

south

First Name

username4

Age

25

Location

south

How could the Compose 2 in your example be configured so its 3 groups of information, but on 6 lines? I need to grab every value one line below 'Firstname', 'Age' and 'Location'. Thank you for your help, this will be accepted as solution regardless

There are also lines of text beyond the final group I do not want to capture, such as weblinks that should not be included in the group/added to excel. 

[v-dezhili-msft]

Hi @MDE94 ,

Regarding this, we only need to modify three places. First, in Compose 2, we change them to a group of 6. Then in Append to array variable, we don't have to split them with ":". Finally, in Add a row into a table, we only need [1], [3], [5] of array A.

Best Regards,

Dezhi

[MDE94]

Hello @v-dezhili-msft , thank you again. Would this work if the person information started from say line 20? 

for example, if the email is: 

Hello XXXX,

The following contact information has been provided:

Name: Person1

Age 23

Location: South

Name: Person2

Age:24

Location: North

I need an array to run through each Name, Age and Location then add each into excel columns, once one group of information is added, another row in excel should be added for the next set of contact information (person2). and so on, an email may contain up to 10 person contact details.

I tried your original example and this returned and error, I'll do this again to show the error I receive