You’re offline. This is a read only version of the page.
Hello,
I am looking to build a flow that would automatically convert customer info submitted thru emails to excel.
the data I am working with has the following setting:
Date:
Jan 19, 2023
Customer Name:
Tony
Address:
123 Main St
How can I set up a condition that for example:
if the line contains "Customer Name" then get the next line which would return "Tony".
if the line contains "Address" then get the next line which would return "123 Main St".
the flow I have so far:
the output of the If statement, if true should return the next item ie. item()+1
I have done some research that suggests using a counter and adding 1 to represent the next item. but I got an error when I ran the test. is it because they are different variables?
thanks in advance
Tony
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:
Share
Report
All responses (
Answers (
//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.
A few questions:
I was able to figure it out by using expression : replace(first(skip(outputs('Split_body'),add(variables('Counter'),1))),' ','')
My end goal is to create a customer info database from the email submission. Ex: customer name, date, and address would be the columns and the info customer entered would be my rows. My plan is to write whole bunch of if statements (about 20) to retrieve the next item in the array if the conditions are met. ie. if item() contains "customer name" retrieve the next item() which would be "tony" then write "tony" to the excel sheet under " customer name" column.
are there any other easier method to complete this? set up cases?
thanks
Tony
A few questions:
#1
Michael E. Gernaey
497
Super User 2025 Season 2
#2
David_MA
436
Super User 2025 Season 2
#3
Riyaz_riz11
244
Super User 2025 Season 2
