Email Body to CSV

[J4ck]

Hi all,

I recieve an email that contains CSV text in the body of the email, I want to extract anything in the body of the email after "@@" and then convert this into a CSV table and then email this back to the same people that were CC'd into the email in the first place. I have tried many different things but always hit a road block! I'm quite new to powerautomate so apologies if this is a simple question.

Here is what my flow currently looks like, but i'm no where near the end as I keep getting stuck 😅😪

Many thanks,

[Sundeep_Malik]

Hey @J4ck 

Can you provide how your email looks like? I might be able to give you some hints.

Also make sure the format is similar always because the email flows are hard coded and any change would result in failing of the flows.

Suggesting you some videos for email flows and also you can try using chat gpt for it.

https://www.youtube.com/watch?v=62BB3rpe37g

Using Chat GPT:

https://www.youtube.com/watch?v=l3YqVpRQniw

I hope this helps 🙂

[J4ck]

Hi @Sundeep_Malik 

Many thanks for such a fast reply!

Please see the email format below, the only thing that would change would be the data within the CSV the rest of the format will stay the same including the headings.

Warning: This email is not from a known sender.

Please find attached\n ATTACHMENT=test.csv&SIZE=423789@@Heading 1.,Heading 2,Heading 3,Heading 4,Heading 5,Heading 6,Heading 7
60,Example,13,Example,Example,6.01,Example
64,Example,15,Example,Example,5.46,Example
21,Example,1,Example,Example,Example,Example
20,Example,4,Example Example,Example,Example

I'll check out your videos, but do let me know if you have any tips as I've been struggling with this!

Thanks

[Sundeep_Malik]

@J4ck 

I always struggle with email flows as well. Hehe.

Tagging a person who might be able to help:

@ManishSolanki 

He is good with the email flows.

I hope he will be able to help. 🙂

[J4ck]

I've added some pictures of my flow incase that helps, I'm just getting stuck on extracting the data in the correct format at the moment.

[ManishSolanki]

Hi @J4ck 

I would request you to pls share the output of "Convert HTML to text" action (with sample data) so that I try some logic to extract data. Does the csv in email body has fixed columns? Do the rows are also fixed in number or may vary?

Thanks

[J4ck]

Hi @ManishSolanki

Many thanks for looking into this, hopefully the above comment answers your questions, but to rephrase thats the output of the HTML to text, the CSV file has fixed rows (these will never change) however the rows will change.

Warning: This email is not from a known sender.

Please find attached\n ATTACHMENT=test.csv&SIZE=423789@@Heading 1.,Heading 2,Heading 3,Heading 4,Heading 5,Heading 6,Heading 7
60,Example,13,Example,Example,6.01,Example
64,Example,15,Example,Example,5.46,Example
21,Example,1,Example,Example,Example,Example
20,Example,4,Example Example,Example,Example

[AlexEncodian]

@J4ck 

Can you provide a picture of the desired output of the CSV file? Using the example you provided above.

[Jason79 · Accepted solution]

Hi @J4ck 

After the HTML to text I have done the following to remove all the text upto and inc. 2nd @ plus the headers:

I then did splitbycomma and JSON steps in this video: How to parse a CSV file with Power Automate - YouTube

After JSON step I added Parse JSON action, click Generate from sample and then paste in 

{
"Heading 1.": "",
"Heading 2": "",
"Heading 3": "",
"Heading 4": "",
"Heading 5": "",
"Heading 6": "",
"Heading 7": ""
}

I then have an excel file with a table using these headers. I then added a add row into a table action, selected that file and added the dynamic content from Parse JSON

My result below

You could then add an action to send that file via email.

Hope this is of some help.

[Jason79 · Accepted solution]

@J4ck 

Had another go but sending out a .csv file. Reiterate I'm a novice so there may be more smarter /efficient methods but I ended up receiving an email with a .csv attachment.

Re. my original post above. After and outside the apply to each add in below 

Then add these steps

Then add action to send email

I utilised this: Instantly using Power Automate create CSV file and send email attachment - Global SharePoint Diary (...

I did receive email with email attachment which I haven't tried before 🙂

[J4ck]

Holy @Jason79 can't thank you enough for all this! Did not expect you to put this much work into it haha, really appreciated it and thanks for breaking it down as well I understand it all now!

It looks like "\n" is being put into the data when converting from HTML so just need to figure out how to remove this and it should all being working perfectly, again thanks so much! and thank you to everyone else who helped!!!

"

[J4ck]

So after having done some research it looks like HTML to text automatically inputs these \n at random. Just in the example data provided it wasn't long enough to trigger this, if you were to use the below as the data source it will input these new lines and thus breaking the CSV formatting.

Warning: This email is not from a known sender.

Please find attached\n ATTACHMENT=test.csv&SIZE=423789@@Heading 1.,Heading 2,Heading 3,Heading 4,Heading 5,Heading 6,Heading 7
60,Example,13,Example, Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example,6.01,Example
64,Example,15,Example,Example,5.46,Example
21,Example,1,Example,Example,Example,Example
20,Example,4,Example Example,Example,Example

I have tried to replace \n with a space however I then get an error saying the CSVdata is null, so not sure if I'm doing something wrong and replacing everything?

[Jason79]

Hi @J4ck 

Thanks for the reply. 

My suggested flows takes the /n that splits the headers over 2 lines into account.

Replace all text up to and inc. 2nd @ removes all text to start of Heading 1. (not sure if there's supposed to be a '.')

Then CSV data action skip(outputs('Split_new_line'),2) skips the headers that are split over 2 lines because of the /n.

Output for csv data

[

  "60,Example,13,Example,Example,6.01,Example",

  "64,Example,15,Example,Example,5.46,Example",

  "21,Example,1,Example,Example,Example,Example",

  "20,Example,4,Example,Example,Example,Example"

]

If you need the headers on a single line you could use below in a new compose and then 

concat(first(outputs('Split_new_line')),outputs('Split_new_line')[1])

Raw out: "Heading 1.,Heading2,Heading 3,Heading 4,Heading 5,Heading 6,Heading 7"

Hope this helps

[J4ck]

Hi @Jason79 

Thanks for all your help so far, the headings are perfect. It's when you add a certain amount of data it seems to add new lines, so for example if you were to input the below as the source data into the flow.

Warning: This email is not from a known sender.

Please find attached\n ATTACHMENT=test.csv&SIZE=423789@@Heading 1.,Heading 2,Heading 3,Heading 4,Heading 5,Heading 6,Heading 7
60,Example,13,Example, Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example Example,6.01,Example
64,Example,15,Example,Example,5.46,Example
21,Example,1,Example,Example,Example,Example
20,Example,4,Example Example,Example,Example


This outputs as 
"Warning: This email is not from a known sender. Please find attached\\n\nATTACHMENT=test.csv&SIZE=423789@@Heading 1.,Heading 2,Heading 3,Heading\n4,Heading 5,Heading 6,Heading 7 60,Example,13,Example, Example Example Example\nExample Example Example Example Example Example Example Example Example Example\nExample Example Example Example Example,6.01,Example\n64,Example,15,Example,Example,5.46,Example\n21,Example,1,Example,Example,Example,Example 20,Example,4,Example\nExample,Example,Example"

Which has serval new line calls within it, meaning the CSV is misaligned. I presume the best way to do this would be to replace the newline with just a space but I'm struggling to do so?

[Jason79]

Can you provide an example how you expect the data you pasted above to be displayed in a csv file?

Not every item is separated by a ',' so should each row have 7 data points and then start a new row? A cell will contain multiple items without the ','.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7

replace(outputs(enter_output),decodeUriComponent('%0A'),' ') should replace \n

replace(outputs(enter_output),' ',',') should replace space with a comma

[J4ck]

Hi @Jason79 

Here is the expected format from that data.

It looks like when \n are being inputted this creates a new cell and breaks up my data when in fact I want it to be in the same cell, and thus everything after that is 1 column out because its been pushed out by the \n.

I have tried to implement the replace function as you mentioned but upon doing so I get the below error

"The 'from' property value in the 'table' action inputs is of type 'Null'. The value must be of type 'Array'."

Hope that all makes sense, but if not feel free to let me know.

Again thanks a ton for all your time and help 🙂

[Jason79]

Is this of any use?

Solved: html to text function adding random break lines is... - Power Platform Community (microsoft....

Haven't tested as a bit beyond my understanding.