Answered

Parse text from email attachment and add row into spreadsheet

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I am getting regular (every 30 mins) automated emails with a .txt file attached consisting of data that I want to be automatically entered into a spreadsheet. The .txt file has the same format every time and looks like this:

NS;5247
Su;6163

(2 rows affected)

and the body of the email looks like this:

Here is the report for 12/08/2021 10:55:33

What I want to do is enter this data into a new row on a spreadsheet every time I get a new email (with the same sender/subject every time), so that the data from every email is inputted into the spreadsheet in this format below.

Type	Count	DateTime
NS	5247	12/08/2021 10:55:33
Su	6163	12/08/2021 10:55:33

Additionally, I have a lot emails already sent to me that have this data but are not entered into the spreadsheet, would it be possible to create a flow that enters the figures from the previous emails into a spreadsheet? However, this is not as important as the previous issue as I can do this manually.

Thanks in advance

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

fchopo 8,003 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi,

In this excellent post it's explained how to extract data from txt file: Solved: Txt file to Excel table - Power Platform Community (microsoft.com)

In any case, you could do something like this:

1) When you receive an email with a specific subject:

2) Initialize a variable with a line break

3) For each attachment in the email

3.1) Get the attachment and get all the lines:

split(body('get_attachment_(v2)'),variables('lines'))

3.2) For all the lines

3.2.1) Get fields Type and Count using :

split(item(),';')[0]

or [1] according to the position in the text file.

3.2.2.) Create an Excel row.

Hope it helps!

Ferran

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

Like (0)

Report
Copy link

Link copied!

Hi,

This seems to be the correct approach, but I am getting an error when testing this flow:

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Copy link

Link copied!

Please, review the parameters in the Get Attachment (v2) action.

Regards,

Ferran

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

Like (1)

Report
Copy link

Link copied!

Hey Ferran,

I have figured out the issue. I needed to put the attachment content into a string variable so that it would input the plaintext into the spreadsheet instead of the attachment's contentbytes. Here is my approach:

But your answer was really helpful, so I'll mark it as the solution.

Was this reply helpful? Yes No
dauc21 2 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous,

Can you help me on how to put the content of the attachment into a string variable? Because I try this way, but I can only extract the content in bytes. I am unable to extract the contents of the string from the attached file.

Was this reply helpful? Yes No