How to Extract Data from an unstructured email body?

[Hann]

I am currently struggling on how I can extract data from an unstrctured email, becuase whenever the sender's email format arrives is not corresponding to the format of the flow that I created when the data is extracted the values are then jumbled because it is placed on different index values etc.

Is there a way to be able to extract data from an email body even if it is from an unstructured format, wherein  the flow will just find the Value from the email and then populate it on excel, or is there any other better approach for it.

Any suggestions will be a big help thank you so much

[v-yetonggu-msft]

Hi @Hann ,

Could you please show me the body of your email and tell me what information you are trying to extract?

Best Regards,

Sunshine Gu

[Hann]

Sure here it is.

What I am trying is getting the value of the Vendor Name, HC point person, PO number, GR number, Invoice Number, payment terms. But the problem of the scenario is that it is not always in this format, sometimes it is like this 

and other kinds of format possible. But the constant identifiers is the Vendor Name, HC point person, PO number, GR number, Invoice Number, and Payment Terms. Thank you!

[v-yetonggu-msft · Accepted solution]

Hi @Hann ,

OK, thank you.

I can implement your needs, but the premise is to ensure that the letter case of each field does not change. Otherwise, the index cannot be located and flow will report an error.

I did a test for your reference.

My outlook Inbox:

My Excel Table:

My Flow Overall Preview:

My Flow Parameters Configuration:

trim(split(split(outputs('Html_to_text')?['body'],'Vendor Name:')[1],'HC Point Person:')[0])
trim(split(split(outputs('Html_to_text')?['body'],'HC Point Person:')[1],'PO Number:')[0])

trim(split(split(outputs('Html_to_text')?['body'],'PO Number:')[1],'GR Number:')[0])
trim(split(split(outputs('Html_to_text')?['body'],'GR Number:')[1],'Invoice Number:')[0])
trim(split(split(outputs('Html_to_text')?['body'],'Invoice Number:')[1],'Payment Terms:')[0])
trim(split(split(outputs('Html_to_text')?['body'],'Payment Terms:')[1],decodeUriComponent('%0A'))[0])

Best Regards,

Sunshine Gu

[Hann]

Thank You so much this is a great help!

[v-yetonggu-msft]

Hi @Hann ,

My mistake was that I didn't take into account the format of the content fields in the email you received.
I observed that in the table format, there is no ';' after the field, but in the List format, there is a ';' after the field, so I added a ';' when indexing in each expression. This is wrong.

Table Format:

List Format:

I suggest you Use these expressions to get field value.

These expressions can obtain the field value regardless of whether there is ';' or not.

trim(replace(split(split(outputs('Html_to_text')?['body'],'Vendor Name')[1],'HC Point Person')[0],':',''))
trim(replace(split(split(outputs('Html_to_text')?['body'],'HC Point Person')[1],'PO Number')[0],':',''))
trim(replace(split(split(outputs('Html_to_text')?['body'],'PO Number')[1],'GR Number')[0],':',''))
trim(replace(split(split(outputs('Html_to_text')?['body'],'GR Number')[1],'Invoice Number')[0],':',''))
trim(replace(split(split(outputs('Html_to_text')?['body'],'Invoice Number')[1],'Payment Terms')[0],':',''))
trim(replace(split(split(outputs('Html_to_text')?['body'],'Payment Terms')[1],decodeUriComponent('%0A'))[0],':',''))

Best Regards,

Sunshine Gu

[Hann]

How about if for example there are some emails that I received did not start directly to the values in which I need extraced. For Example:

Hello this is your invoice,

Vendor Name	Johann Gabriel A. Sapitula
Invoice Number	123456
HC Point Person	Hann G.
Gr Number	123456

will the flow that you showed me will still extract the right values or it will be jumbled since the value of the indexes changed?

[v-yetonggu-msft]

Hi @Hann ,

Unfortunately, it will cause index confusion.

But it doesn’t matter, I re-improved the expression in my flow.

I did another test for your reference.

In my outlook inbox:

My Excel Table:

My Flow:

if(contains(outputs('Html_to_text')?['body'],'Vendor Name'),trim(replace(split(split(outputs('Html_to_text')?['body'],'Vendor Name')[1],decodeUriComponent('%0A'))[1],':','')),null)

if(contains(outputs('Html_to_text')?['body'],'HC Point Person'),trim(replace(split(split(outputs('Html_to_text')?['body'],'HC Point Person')[1],decodeUriComponent('%0A'))[1],':','')),null)

if(contains(outputs('Html_to_text')?['body'],'PO Number'),trim(replace(split(split(outputs('Html_to_text')?['body'],'PO Number')[1],decodeUriComponent('%0A'))[1],':','')),null)

if(contains(outputs('Html_to_text')?['body'],'GR Number'),trim(replace(split(split(outputs('Html_to_text')?['body'],'GR Number')[1],decodeUriComponent('%0A'))[1],':','')),null)

if(contains(outputs('Html_to_text')?['body'],'Invoice Number'),trim(replace(split(split(outputs('Html_to_text')?['body'],'Invoice Number')[1],decodeUriComponent('%0A'))[1],':','')),null)

if(contains(outputs('Html_to_text')?['body'],'Payment Terms'),trim(replace(split(split(outputs('Html_to_text')?['body'],'Payment Terms')[1],decodeUriComponent('%0A'))[1],':','')),null)

Best Regards,

Sunshine Gu

[Hann]

noted will look into it thank you so much!

[v-yetonggu-msft · Accepted solution]

Hi @Hann ，

If I solve your problem, can you mark my response as a solution so that my answer and your answer can help more people who encounter this challenge in the future.

Best Regards,

Sunshine Gu