Extracting text from email and filling a table in Sharepoint or Excel

(0) Share

Report

Posted on by MikeAA

Hi everybody

I have a recurrent daily email with information like this (is a short example):

0301: RESERVATION_DAILY_ELEMENTS , RESORT_ROOM_CAT_COMBINATIONS , TRX_CODES , PK_RESERVATION_SUMMARY

0305: RESORT_ROOM_CAT_COMBINATIONS , TRX_CODES

0312: RESERVATION_DAILY_ELEMENTS , RESORT_ROOM_CAT_COMBINATIONS , RESORTS_ROOM_CATEGORY , TRX_CODES

0313: RESORT_ROOM_CAT_COMBINATIONS , TRX_CODES

0322: RESERVATION_DAILY_ELEMENTS , RESORT_ROOM_CAT_COMBINATIONS , RESORTS_ROOM_CATEGORY , TRX_CODES

0705: RESERVATION_DAILY_ELEMENTS , RESORT_ROOM_CAT_COMBINATIONS , TRX_CODES

0706: RESORT_ROOM_CAT_COMBINATIONS , TRX_CODES , FIXED_CHARGES

0720: ALLOTMENT_DETAIL , ALLOTMENT_HEADER , EXTERNAL_REFERENCES , FINANCIAL_TRANSACTIONS , FOLIO_TAX , NIGHT_AUDIT_JOURNAL , PRODUCT_POSTING_RULES , REP_MANAGER , RESERVATION_DAILY_ELEMENT_NAME , RESERVATION_DAILY_ELEMENTS , RESERVATION_NAME , RESERVATION_PRODUCT_PRICES , RESERVATION_STAT_DAILY , RESERVATION_SUMMARY_LOG , RESERVATION_SUMMARY , RESORT_ROOM_CAT_COMBINATIONS , ROOM_CATEGORY_STATISTICS

and I need to extract this info and fill a table in this way with a Power Automate flow:

CODE	TABLE
0301	RESERVATION_DAILY_ELEMENTS
0301	RESORT_ROOM_CAT_COMBINATIONS
0301	TRX_CODES
0301	PK_RESERVATION_SUMMARY
0305	RESORT_ROOM_CAT_COMBINATIONS
0305	TRX_CODES
0312	RESERVATION_DAILY_ELEMENTS
0312	RESORT_ROOM_CAT_COMBINATIONS
0312	RESORTS_ROOM_CATEGORY
0312	TRX_CODES
0313	RESORT_ROOM_CAT_COMBINATIONS
0313	TRX_CODES
0322	RESERVATION_DAILY_ELEMENTS
0322	RESORT_ROOM_CAT_COMBINATIONS
0322	RESORTS_ROOM_CATEGORY
0322	TRX_CODES
0705	RESERVATION_DAILY_ELEMENTS
0705	RESORT_ROOM_CAT_COMBINATIONS
0705	TRX_CODES
0706	RESORT_ROOM_CAT_COMBINATIONS
0706	TRX_CODES
0706	FIXED_CHARGES
0720	ALLOTMENT_DETAIL
0720	ALLOTMENT_HEADER

etc, etc

I really appreciate some help to do this.

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Rhiassuring 8,690 Moderator on at

Like (0)

Report

Sure. Here's the output I achieved:

The flow will look something like this.

Initialize your variables. Email is just a copy of the text you gave me so I could use it. NewLine is just a string variable that I hit "enter" in. Array is an empty array for the new data.

Split your email content by the NewLine variable. Then, filter out the empty lines.

Enter into an Apply to Each on the body from the Filter Array.

Set your variable for the Code with the following expression:

first(split(items('Apply_to_each'),':'))

Then, split the current item on the commas (after removing the code from the line) using this expression:
split(substring(items('Apply_to_each'),5,sub(length(items('Apply_to_each')),5)),',')

Then, iterate through the newly split items. Here all we're doing is appending the vales to our array.

On the outside of your Apply to Each, you now have an array you can do whatever you need to with. I created an HTML table, which you saw at the beginning of this post.

Was this reply helpful? Yes No
MikeAA 42 on at

Like (0)

Report

Hi @Rhiassuring excellent solution, the only thing i didn't understand was: "New Line is just a string variable that I hit "enter" in". How do I hit "enter" I don't understand.
Thanks in advance

Was this reply helpful? Yes No
Verified answer

Rhiassuring 8,690 Moderator on at

Like (0)

Report

On your keyboard, the "enter" button. Carriage return.

You just click into the field, and press the enter key on your keyboard.

Was this reply helpful? Yes No