How to split the email body which has different ev...

YuvaraniD · ‎06-10-2023

Hi Team,

I have a Email body which has different events in it and which does not have unique content to extract using that into excel.

For Eg: Below is my reference Email body which needs to be extracted into excel.

Maintenance1:(This is Event name under this we have the event details in it).

----------------

Maintenance2:(This is Event name under this we have the event details in it).

---------------

Maintenance3:(This is Event name under this we have the event details in it).

--------------

Maintenance1:(This Event also has same name as 1st but this has different event details).

---------------

Output Required(whichever(1or 2) is possible)

1. Need to extract each event data into excel

2. Need to split each event separately and need to extract the each event details into excel.

Challenges am facing: Since the Email body contains similar event names, am unable to extract using the unique content whereas I didnot have any unique content to trim/split using that. the expected output is not getting.

Is there any other functions we have to get this?

Note: Each events are splitted using space only.

Please help me on this to get the expected output. Thanks!!

Regards,

Yuvarani D

SanmeshG · ‎06-10-2023

Hi @YuvaraniD ,

Can you share the html of the email body ?

YuvaraniD · ‎06-12-2023

Hi..
Please find the HTML sample of the mail.

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="Generator" content="Microsoft Word 15 (filtered medium)"><style>

</style></head><body lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word"><div class="WordSection1">Merchant Services  Image  Image     MERCHANT SERVICES  STATUS DASHBOARD Maintenance Software Maintenance Scheduled No Action Required   Context on what happens during Maintenance   Reference Number: Start Time: End Time:  Components Affected Components Affected1 Components Affected2   Hardware Maintenance Scheduled No Action Required   Context on what happens during Maintenance   Reference Number: Start Time: End Time:  Components Affected Components Affected1   Software Maintenance Scheduled No Action Required   Context on what happens during Maintenance   Reference Number: Start Time: End Time:  Components Affected Components Affected1 Components Affected2 Components Affected3   Hardware Maintenance Scheduled No Action Required   Context on what happens during Maintenance   Reference Number: Start Time: End Time: Components Affected Components Affected1 Components Affected2 Components Affected3     ABOUT THIS MESSAGEMerchant Updates Disclaimer Manage your subscription.Link   </div></body></html>

SanmeshG · ‎06-12-2023

Thanks for sharing , can you please paste it in the Code view , so that it's easy to copy. Use </> to paste it in code view.

YuvaraniD · ‎06-12-2023

Please find the code attached. Is that fine?

SanmeshG · ‎06-12-2023

Hi @YuvaraniD ,

That helps , but it is very inconsistent , because if everything is an event , then it needs to have Start Time and End Time, which is not the case.

Are we missing something? What is the source of this ?

I thought we can try it with an xpather but not sure about the consistency of the HTML.

YuvaraniD · ‎06-12-2023

Hi,

Here, everything is an event and each event has its own Reference Number, Start and End date.
but we dont have any unique item in each event in the mail to extract.

AlexEncodian · ‎06-13-2023

@YuvaraniD

Are the vent names unique (even if its just the suffix Event2)? And are they always the same or different email to email?

YuvaraniD · ‎06-13-2023

Hi... The event names are different for each email. and also the same email contains a similar event name as well.

sandeepstw · ‎06-13-2023

To extract each event data separately from the email body and export it to Excel using Power Automate, you can follow these steps:

Start your Power Automate flow with a trigger that indicates the receipt of an email, such as "When a new email arrives (V3)".
Add a "Compose" action to extract the email body content. In the "Inputs" field of the "Compose" action, enter the expression triggerBody()['body'].
Use the "Split" function to split the email body into separate events based on the "Maintenance" keyword. Add a "Compose" action after the previous "Compose" action and set its "Inputs" field to split(outputs('Compose'), 'Maintenance').
Add a "Filter array" action to remove any empty elements from the split result. In the "From" field of the "Filter array" action, enter outputs('Compose_2'). In the "Condition" field, enter item()?['Value']?['Length'] > 0.
Now, you have an array of events, and each event can be accessed using the expression body('Filter_array')?[x]['Value'], where x is the index of the event (starting from 0).
To export the events to Excel, you can use the "Create table" action or "Add row to a table" action from the Excel Online (Business) connector. Configure the action by providing the necessary details, such as the Excel file, worksheet name, and the event details to be added as rows in the table.
To iterate through the array of events, use the "Apply to each" control action. Place the "Create table" or "Add row to a table" action inside the "Apply to each" control, and set the input to body('Filter_array').
Save and test your flow with sample emails that contain multiple events to verify the extraction and export process.

By following these steps, you should be able to split the email body into separate events and export the event details to an Excel file using Power Automate.