cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!!

1 ACCEPTED SOLUTION

Accepted Solutions
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

View solution in original post

6 REPLIES 6
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

View solution in original post

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"
        }
    }
}
CashMaker
Level: Power Up

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

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.

Highlighted
ashler
Level: Power Up

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

@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.

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,898)