Extract text from body of an email and create new rows in excel spreadsheet

[Anonymous]

Hello - 

As the title says, I'm trying to extract text from body of an email and create new rows in excel spreadsheet.

I've used the 'HTML to text' to get the info, but when I go to push it to the spreadsheet, all of the data goes into one column (in the new row).

How can I get individual items from the body previously extracted using the HTML-to-text and insert each item into its own column  in the new row?

For example:

Date:

Unit Number:

Run Number:

Pick Up:

Drop Off:

similar to https://powerusers.microsoft.com/t5/Building-Flows/Extract-text-from-Body-of-an-email-in-Flow/m-p/51...

Parserr will work for what I need it to do...in fact it's perfect, but I'll need to run ~ 200 emails a month through this process and Parserr is just waaay to expensive for that. One would think this should be a fairly easy process using microsoft's own tools, but seeing as how someone else has built a better mousetrap, I'm wondering if it even IS possible to do so without a third party. (Plus I have regulations I have to follow, HIPAA, etc.)

Thanks!!

[vecerpa]

Hi @Anonymous,

As far as I know there is no way to parse email body except using Parserr. There is no function inside Flow that will do this.

In some cases there is possible to lets say converse html Body to Text and then split it by some splitter for example comma. But this request that incomming email is formatted somehow. For exmaple like on image below.

The result is that I have excel added one row in table:

But there is need to have specific input to be able to parse it so simple...

P.

[v-yuazh-msft · Accepted solution]

Hi @ tcm692,

I have made a test on my side.

The content in the email body as below:

Date:20180410

Unit Number:200

Run Number:180

Pick Up:100

Drop Off:80

You could refer to screenshot below to create the flow:

The expression in the "Date" field of "Insert row" action as below:

first(skip(split(first(split(body('Html_to_text'),'Unit Number')),':'),1))

The expression in the "Unit Number" field of "Insert row" action as below:

first(skip(split(first(split(body('Html_to_text'),'Run Number')),'Unit Number:'),1))

The expression in the "Run Number" field of "Insert row" action as below:

first(skip(split(first(split(body('Html_to_text'),'Pick Up')),'Run Number:'),1))

The expression in the "Pick Up" field of "Insert row" action as below:

first(skip(split(first(split(body('Html_to_text'),'Drop Off')),'Pick Up:'),1))

The expression in the "Drop Off" field of "Insert row" action as below:

first(skip(split(body('Html_to_text'),'Drop Off:'),1))

When the email with the body which I provided above arrives, the flow would run successfully to insert those value to excel rows as below:

The row would be inserted to the excel as below:

Regards,
Alice Zhang

[NHNG]

Hi Alice,

Bravos, tried your flows steps and it work but it also capture other text in the email.

So how  remove or ignore other text such as "Best Request"  and  email signature .

Best Regard,

NH

[bridgetwilson]

Hi Alice, new Flow user here!

I'm following your exact example and I have gotten my Flow to run successfully, however, it is not extracting the text properly for 2 lines.  I have checked and rechecked that all the references are typed exactly as they should be, I see nothing that says these lines should be extracted any differently than all the other ones that are working.  Any ideas?  Below is my code...lines that are underlined are the 2 giving me troubles.  Any help is greatly appreciated!

{
    "inputs": {
        "host": {
            "connection": {
                "name": "@parameters('$connections')['shared_excelonlinebusiness']['connectionId']"
            }
        },
        "method": "post",
        "body": {
            "Request Date": "@{first(skip(split(first(split(body('Html_to_text'),'Sales Initials')),'Request Date'),1))}",
            "Sales Initials": "@{first(skip(split(first(split(body('Html_to_text'),'Client Name or Customer Code')),'Sales Initials'),1))}",
            "Client Name or Customer Code": "@{first(skip(split(first(split(body('Html_to_text'),'Project Address')),'Client Name or Customer Code'),1))}",
            "Project Address": "@{first(skip(split(first(split(body('Html_to_text'),'Bill To If Different')),'Project Address'),1))}",
            "Bill To If Different": "@{first(skip(split(first(split(body('Html_to_text'),'Floors or Suite Numbers')),'Bill To If Different'),1))}",
            "Floors or Suite Numbers": "@{first(skip(split(first(split(body('Html_to_text'),'Project Size and Complexity')),'Floors or Suite Numbers'),1))}",
            "Project Size and Complexity": "@{first(skip(split(first(split(body('Html_to_text'),'Major Systems Line')),'Project Size and Complexity'),1))}",
            "Major Systems Line": "@{first(skip(split(first(split(body('Html_to_text'),'Due Date for Design')),'Major Systems Line'),1))}",
            "Due Date for Design": "@{first(skip(split(first(split(body('Html_to_text'),'Designer Assigned')),'Due Date for Design'),1))}",
            "Designer Assigned": "@{first(skip(split(first(split(body('Html_to_text'),'_')),'Designer Assigned'),1))}"
        },
        "path": "/codeless/v1.2/drives/@{encodeURIComponent('b!4GvXZb_9JkaawUdl3L1dfsVz_dNQC3tNhS635iDouJTqvha8iTCgRqSFWYIPETWu')}/items/@{encodeURIComponent('01EXCIRQFR4K3VYZXTYBGLKVW3NDZYGJCG')}/workbook/tables/@{encodeURIComponent('{9072CD82-AADE-4CF7-BB94-C76DC549B63C}')}/rows",
        "queries": {
            "source": "me"
        },
        "authentication": "@parameters('$authentication')"
    },
    "metadata": {
        "01EXCIRQFR4K3VYZXTYBGLKVW3NDZYGJCG": "/New Design Requests.xlsx",
        "flowSystemMetadata": {
            "swaggerOperationId": "AddRowV2"
        }
    }
}

[CashMaker]

I had a similar task to do it but I have more row in a text file after extracting from HTML to text. I'm trying to understand the rules of expressions to solve my task by my self but I don't really understand it If some of you can help me with that task I would be appreciate of.

[ashler]

@vecerpa i am exactly looking for this and will ensure the input sent by email is structured so that it can be processed.

one question: the excel file (output) with the email info can be stored anywhere or it goes only to a sharepoint folder? I would need the first. thanks.

[Anonymous]

When I use these formulas and my data is inserted into a share point list , there is an additional leading space inserted with the text. Is there a way to strip the leading space?

[GrandMaZzZBOY]

Here is something that worked for me. What you do is put your mouse cursor into that value field, press Enter on the keyboard so it adds the line/carriage return. Use PeekCode to verify, you should see character return code

[GrandMaZzZBOY]

Sorry this was my first post. Responded to wrong thread in wrong window.

[javkhaa0523]

Cool, thanks 

[jsurrette]

redacted due to sensitive information

[c20916]

Alice - If there is text that is not needed on the spreadsheet that is listed below the last field on the email, is there a way to prevent that from showing up?  my flow works except it adds additional text to the last field that isn't needed and I have to go in manually and delete it. 

[marco_za]

Hi 

I need help to extract from the following email:

"

From: Michael Ferreira <txxxxxxxxxx@gmail.com>

Subject: [your-subject]

Message Body:

I'm interested in buying the house

Phone Number: 000000000

--

This email was sent from a contact form on"

i need to extract the nme, email phone number and message.

thank you.

[SSKS]

Thank you for the example, I was able to create a power automate flow which works well for new emails arriving in my inbox.

Please suggest if there is a solution to create a similar power automate flow to extract data from emails in an outlook folder, to a spreadsheet? 

[bvargas]

This solved my problem 100% - thank you for sharing!!!!

[Anonymous]

Hi Alice,

Your examples and solutions are wonderful! However, I am still struggling to get my head around the expressions. I tried using your method, but it does work on mine. Maybe because the email format is different. May I please request for you help to get the right expressions?