Power Automate Pulling info from email body into Excel
I'm trying to grab info from email bodies and put them into an Excel sheet. I found a way to do it, but it's not working for all the questions. Here's what's going on:
You are receiving this notification because a Contact has filled out the form
requesting a license upgrade.
Lead Name: Jhon Doe
Company Name: Jhon Doe Enterprises Ltd.
Email Address: jhon.doe@enterprises.com
Do you have a Specific date you would like the upgrade to be completed by? Yes
Upgrade Date: 2024-06-21
Are you FIPS Compliant or FIPS Certified? Neither
SIEM/NetMon: SIEM
What version are you currently on? 7.14.x
What version SIEM would you like to upgrade to? 7.15.x
What version NetMon would you like to upgrade to?Not Provided
Are you upgrading an appliance or a Software Solution? Appliance
What Operating System is your PM or XM licensed for? Windows Server 2016
DR or HA Environment: No
Upgrade for 9 or more nodes: No
Are you an MSSP/SI that will be applying this license to a deployment that
manages other customers? No
What company are you requesting a license for? Not Provided
What is the Master License ID? 1902286
https://nam11.optumfinancial.email.outlook.com/?url=http%2F%2.onmicrosoft.com%7C2c74e1b69d914d17810f08dc91637b5d%7C64b2e3a0db367e1%7C1%7C0%7C63854510366CUnknown%7CTWFpbGZs4wLjAwMDAiLCJQIjoiV2B%3D%7C0%7C%7C%7C&sdata=n2%2Fe3n35OlPW2wAdpUiwhyxUrxGlkLDwmZQOs%3D&reserved=0
What I did is below
trim(first(split(last(split(body('Html_to_text'), 'Company Name:')), 'Email Address')))
but above trim is not working for the below questions
trim(first(split(last(split(body('Html_to_text'), 'Upgrade for 9 or more nodes:')), 'Are you an MSSP/SI that will be applying this license to a deployment that manages other customers?')))
trim(first(split(last(split(body('Html_to_text'), 'Are you an MSSP/SI that will be applying this license to a deployment that manages other customers?')), 'What company are you requesting a license for')))
first(skip(split(first(split(outputs('Html_to_text'),'What is the Master License ID?')), '')))
Could someone please help me identify what I'm doing wrong? My code is working fine for everything else except these three cases. Any assistance would be greatly appreciated.
Sample out put for above falling 3 (I only need 'No' for the first one and any number except the last one. It's displaying a link that isn't necessary.)
No
Are you an MSSP/SI that will be applying this license to a deployment that
manages other customers? No
What company are you requesting a license for? Not Provided
What is the Master License ID? 3297936
3297936
https://nam11.optumfinancial.email.outlook.com/?url=http%2F%2.onmicrosoft.com%7C2c74e1b69d914d17810f08dc91637b5d%7C64b2e3a0db367e1%7C1%7C0%7C63854510366CUnknown%7CTWFpbGZs4wLjAwMDAiLCJQIjoiV2B%3D%7C0%7C%7C%7C&sdata=n2%2Fe3n35OlPW2wAdpUiwhyxUrxGlkLDwmZQOs%3D&reserved=0
Thank you
so i would do it in a 2 step process. first split all answers into an array and then extract the data.
Please note that my expression is splitting by'?' and i can see some of your questions follow ':' so replace the expression accordingly or adopt a structure that is more consistent.
Results:
All three questions you mentioned are now being read. you can combine the expressions as a select statement or a JSON to combine the results and then use them as per your convenience.
