Extracting data form email body

Hi,

An email comes in to an inbox a few times a day (example below). The body of the email has an introductory line, a couple of carriage returns, and then a section of JSON data I want to insert into a table. (I now can parse the data nicely thanks to Mike's separate feedback!).

I need to separate out the JSON data from the remainder of the email body.

Is the best way to do this to use Python? I can't seem to do it using the POP3 reader or package body elements. 

Kind regards, Andrew

Example email body:

Below are the registrants for our product.

[{"odata.etag":"W/\"datetime'2023-06-28T14%3A41%3A17.5015013Z'\"","PartitionKey":"6:2F28:2F2023","RowKey":"1609359378900:2E:5F47FED415:2D1E46:2D4F25:2DAFA6:2DFE573FB009FF","Timestamp":"2023-06-28T14:41:17.5015013Z","ProductId":"1609359378900.xxxx","CustomerInfo":"{\"FirstName\""xxxx\",\"xxxx\""xxxx\",\"Email\""xxxx\",\"Phone\""xxxx\",\"Country\""xxxx\",\"Company\""xxxx\",\"Title\":null}","LeadSource":"xxxx","ActionCode":"INS","PublisherDisplayName":"xxxx","OfferDisplayName":"xxxx","CreatedTime":"06/28/2023 14:41:17","Description":""},{"odata.etag":"W/\"datetime'2023-06-28T14%3A01%3A43.0817537Z'\"","PartitionKey":"6:2F28:2F2023","RowKey":"1609359378900:2E:5F8205ECB0:2D5E7D:2D4E0F:2D8E86:2DD80B211A24B1","Timestamp":"2023-06-28T14:01:43.0817537Z","ProductId":"1609359378900.xxxx","CustomerInfo":"{\"FirstName\""yyyy\",\"LastName\""yyyy\",\"Email\""yyyy\",\"Phone\""yyyy\",\"Country\""yy\",\"Company\""yyyy\",\"Title\":null}","LeadSource":"yyy","ActionCode":"INS","PublisherDisplayName":"yyyy","OfferDisplayName":"yyyy","CreatedTime":"06/28/2023 14:01:42","Description":""}]
 

Can we ask you to forward us a couple of emails?

Working with an email body can be tricky because of how it is encoded.

IMAP4 reader may work but it is not 100% guaranteed

Sure - to which email address Peter?

support@etl-tools.com

Hi Andrew

I have created a transformation which extracts JSON data from emails message

Please see the screenshots below.

You would need to be careful with it because if the supplier changes the format it will stop working