Power Platform Community Forum Thread Details

Hello,

I'm stuck with this task of extracting specific information from the body of an recurring email (Attached) to MS excel. From the mock version of the email body, I want to extract each occurrence of the employee ID, Origin, Destination of each leg, and Departing, Arriving on dates for each leg. I want the spreadsheet to show each leg of the itinerary as a separate row. The fields repeat in the body of the email depending on the number of legs with value changing and I want each one to show in a separate row in Excel. For example, from the attached itinerary, the output spreadsheet should show

Emp ID Flight # Departed from Departure on Arrival at Arrival on

999999 QT123 Bangkok 9/29/2023 Doha 9/29/2023
999999 UA100 Doha 9/29/2023 Washington, D.C. 9/29/2023
999999 AF131 Washington, D.C. 9/29/2023 Paris 10/6/2023

How to achieve this using power automate flow? I tried creating a flow using Expression of and Index but it is not fetching the desired results in different rows.

Any help is much appreciated.

Regards,
Raam

Email body.docx

Categories:

General topics

Hi,

To extract specific information from the body of a recurring email and export it to an Excel spreadsheet using Power Automate, you can use the following steps:

Create a new flow in Power Automate.
Add a When a new email arrives trigger and configure it to trigger when the recurring email arrives.
Add a Parse JSON action to the flow and configure it to parse the email body into a JSON object. You can use the Compose action to test the JSON schema before parsing it.
Use the Apply to each action to loop through each occurrence of the employee ID, Origin, Destination of each leg, and Departing, Arriving on dates for each leg.
Add a Create row action to the flow and configure it to create a new row in the Excel spreadsheet for each leg of the itinerary. You can use the dynamic content from the Parse JSON action to populate the columns in the row.