Extracting specific information from the body of an recurring email

[power13]

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

[DarmonGill]

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:

1. Create a new flow in Power Automate.
2. Add a When a new email arrives trigger and configure it to trigger when the recurring email arrives.
3. 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.
4. 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.
5. 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.

Here is an example of how you can configure the Create row action:

• Table name: The name of the Excel table where you want to add the rows.
• Emp ID: The employee ID from the email.
• Flight #: The flight number from the email.
• Departed from: The origin of the flight from the email.
• Departure on: The departure date from the email.
• Arrival at: The destination of the flight from the email.
• Arrival on: The arrival date from the email.

Gill