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.
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
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
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.
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
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
User | Count |
---|---|
94 | |
39 | |
24 | |
22 | |
16 |
User | Count |
---|---|
128 | |
49 | |
48 | |
32 | |
24 |