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 /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.
First I tried creating a power automate flow using Expression of and Index and it did not work and I kept getting an error.
Next, I followed the steps from different threads from PowerAutomate Community that used Html to text > Compose and another flow using Parse JSON too.... but none of them gave me the desired output.
Finally I did an AI builder to capture the required information from the body of the email but even then the accuracy score was only 73% and so I did not proceed.
Currently I'm working on a flow that goes like When a new email arrives>Html to text>Compose (to add empty line)> Compose2(to split body of text into line)> filter array to show line with more than 0 character.... now I'm stuck to choose the value from the nth line. For example, if I have Emp ID in line 6, departure in line 7 and arrival in line 8 in one email body, these information will be a different line in another email. How do I write the flow to accommodate this change?