cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tcm692
Level: Power Up

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

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!!

4 REPLIES 4
Super User
Super User

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

Hi @tcm692,

 

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.

 

FlowParseEmail.PNGFlowParseEmail1.PNG

 

 

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

 

FlowParseEmail3.PNG

 

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

P.

Community Support Team
Community Support Team

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

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:

Capture.PNG

 

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:

Capture.PNG

 

The row would be inserted to the excel as below:

Capture.PNG

 

 

 

Regards,
Alice Zhang

NHNG
Level: Power Up

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

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
Level: Powered On

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

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"
        }
    }
}

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 311 members 5,657 guests
Please welcome our newest community members: