Hopefully this is what you're looking for. You'd need to test it with a few of your emails to make sure it gives you the expected results.
For this example, I've got the following Excel Table.
And using the following email for testing.
You'll note that the email doesn't contain the Customer Age that exists as a column in the Excel Table.
See full flow below. I'll go into each of the actions.
When a new email arrives retrieves the email content.
Html to text strips out the HTML tags and leaves us with plain text for our email body.
Filter array takes in the output from Html to text, splits by new line, and trims the data. It then filters out any items within the array that are empty leaving us with only the property/values that we want to extract. The expressions used are:
//splits the text by new line into an array. decodeUriComponent('%0A') refers to new line.
split(outputs('Html_to_text')?['body'], decodeUriComponent('%0A'))
trim(item())
This will give us the following output. Note that it still includes the asterisks, colons, and single quotes in the properties. We'll remove these later when we build the objects. The important thing here is the data should be property, value, property, value, property, value, etc.
Initialize variable creates a variable called data of type Object and sets the initial value to an empty object {}. This will eventually contain all the properties and values.
Apply to each iterates over our Filter array, but in groups of two using the chunk expression. This means the first time through the loop we will get the first two items, the second time through the loop we will get the next two items, etc. This will allow us to get each of the properties and corresponding values. The expression used is:
chunk(body('Filter_array'), 2)
Compose Property adds a new property to our data variable. It also strips out any asterisks, colons and single quotes for the property names using replace, and trims both the property name and value. Because we are looping over two items at a time, we can use index 0 to get the property name, and index 1 to get the corresponding value. The expression used is:
addProperty(variables('data'), trim(replace(replace(replace(items('Apply_to_each')[0], '*', ''), ':', ''), '''', '')), trim(items('Apply_to_each')[1]))
Set variable then takes the output from Compose Property and sets it as the new value for our data variable.
And the end of the loop, our data variable will contain all the property/values it found in our email body.
//data variable content
{
"Date": "Jan 19, 2023",
"Customers Name": "Tony",
"Customers Phone Number": "5122930948",
"Electrical Company": "Eagle Electronics",
"Address": "123 Main St"
}
Add a row into a table takes our data we extracted and creates a new row in our Excel Table. The expressions used are below:
//It's important here that we use ? before our property name so it returns null if that particular propery doesn't exist, otherwise it would return an error.
//In this example, we don't have Customer Age, so that will return null
variables('data')?['Date']
variables('data')?['Customers Name']
variables('data')?['Customers Phone Number']
variables('data')?['Customers Age']
variables('data')?['Electrical Company']
variables('data')?['Address']
After running the flow, we would have a new row in our Excel Table as shown below. Note that Customer Age is blank.
----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
