cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hlw1980
Regular Visitor

Parse multiple lines of email with same data structre into excel

Hello!

 

I'm currently pulling the data I need into an excel spreadsheet (parse data into new row), but there are times where there are multiple lines of data I need to grab. I don't know how to make it loop/ or repeat until it reaches the end of the email. The format is always the same, but I'm a bit lost on how to configure it (or it can even be done). I'm still new to Power Automate, and I think it's something simple I'm missing.

 

Here's how the data comes over:

 

hlw1980_0-1661117951387.png

 

Here is how I'm parsing it into the spreadsheet:

 

hlw1980_1-1661118059920.png

 

and here is my current process flow:

 

hlw1980_2-1661118184250.png

 

Here is my current formulas for retriving the info:

 

trim(first(split(last(split(body('Html_to_text'),'Shipment Client Keys:')),'Post failed with a message:')))
trim(first(split(last(split(body('Html_to_text'),'Post failed with a message:')),'This message')))
 
I'm missing something, and I've searched the boards here. There was only one other post about a situation like this but the post didnt provide anyone any infomation in order to help. If it can't be done, I can accept that and will have to figure out another process for capturing this info. But I thought I'd at least ask the question... 🙂
Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
WillPage
Solution Sage
Solution Sage

What you're asking here is doable, but it's not really straightforward.

You can approach it with the split() function like you have already, or you can use a combination of substring() and indexOf()

Using split() you would already know that if you split on "Shipment Client Keys" you'll have an array of strings that start with the text you want, except the first line which you'll need to discard.

So, for example, split on that text, then you can filter out the first row using skip():

 

skip(split(body('Html_to_text'),'Shipment Client Keys:'),1)

 

Now you have an array of strings that starts with the text you want, you can add a Select action.

 

Your key-value pairs will need to be the Shipment Client Keys and the Invalid address book entry

So, call the first key-value pair "shipmentKeys" add this expression as the value.

 

trim(substring(item(),0,indexOf(item(),'Post failed'))

 

This will effectively strip off everything from "Post failed" onwards for each row of the array, leaving you with the client keys.

The next key-value pair you can call "addressBookEntry" or whatever and use this formula to get the text out of the string:

 

substring(
		item(),
		add(
			indexOf(
				item(),
				'Post failed'),
			29),
		sub(
			indexOf(
				item(),
				' is not a valid'),
			add(
				indexOf(
					item(),
					'Post failed'),
				29)
			)
		)

 

This will parse out the relevant text (of variable length) from the array item. You may need to wrap trim() around that too.

What you're left with when the flow runs is an array like this

 

[
    {
        "shipmentKeys": "3362344, M356231",
        "addressBookEntry": "5030383"
    },
    {
        "shipmentKeys": "3362388, I035715",
        "addressBookEntry": "DDM0619"
    }
]

 

You can then do what you want with this array, such as insert a new row within an Apply to each. If you want the dynamic content then run it through a Parse JSON action with the above code as the sample payload.

View solution in original post

1 REPLY 1
WillPage
Solution Sage
Solution Sage

What you're asking here is doable, but it's not really straightforward.

You can approach it with the split() function like you have already, or you can use a combination of substring() and indexOf()

Using split() you would already know that if you split on "Shipment Client Keys" you'll have an array of strings that start with the text you want, except the first line which you'll need to discard.

So, for example, split on that text, then you can filter out the first row using skip():

 

skip(split(body('Html_to_text'),'Shipment Client Keys:'),1)

 

Now you have an array of strings that starts with the text you want, you can add a Select action.

 

Your key-value pairs will need to be the Shipment Client Keys and the Invalid address book entry

So, call the first key-value pair "shipmentKeys" add this expression as the value.

 

trim(substring(item(),0,indexOf(item(),'Post failed'))

 

This will effectively strip off everything from "Post failed" onwards for each row of the array, leaving you with the client keys.

The next key-value pair you can call "addressBookEntry" or whatever and use this formula to get the text out of the string:

 

substring(
		item(),
		add(
			indexOf(
				item(),
				'Post failed'),
			29),
		sub(
			indexOf(
				item(),
				' is not a valid'),
			add(
				indexOf(
					item(),
					'Post failed'),
				29)
			)
		)

 

This will parse out the relevant text (of variable length) from the array item. You may need to wrap trim() around that too.

What you're left with when the flow runs is an array like this

 

[
    {
        "shipmentKeys": "3362344, M356231",
        "addressBookEntry": "5030383"
    },
    {
        "shipmentKeys": "3362388, I035715",
        "addressBookEntry": "DDM0619"
    }
]

 

You can then do what you want with this array, such as insert a new row within an Apply to each. If you want the dynamic content then run it through a Parse JSON action with the above code as the sample payload.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (4,231)