Unanswered

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

(3) Share

Report

Posted on by Community Power Pla...

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:

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

Categories:

Building flows

I have the same question (0)

All responses (17)

Answers (1)

vecerpa 786 on at

Like (4)

Report

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

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.

Was this reply helpful? Yes No
Verified answer

v-yuazh-msft on at

Like (14)

Report
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:

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

Was this reply helpful? Yes No
NHNG 3 on at

Like (0)

Report

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

Was this reply helpful? Yes No
bridgetwilson 8 on at

Like (0)

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

Was this reply helpful? Yes No
CashMaker 4 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ashler 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

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?

Was this reply helpful? Yes No
GrandMaZzZBOY 8 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
GrandMaZzZBOY 8 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
javkhaa0523 19 on at

Like (0)

Report

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

Cool, thanks

Was this reply helpful? Yes No