In Power Automate, how to get data from a .csv email attachment and format it for display?

[Facilities_AV]

1st time posting a question...

I have a simple task in Flow - I need to

1) get data from a .csv email attachment,

2) format it in an html table, and then

3) send the table in another email.

I've seen several paid connectors and some other "advanced" methods to "parse" the .csv data to create a JSON array - is the the best solution?

I need to accomplish the task using built-in/standard connectors - the main connection is "Outlook O365."

Trigger is "when an email is received."

.

Output is "send an email"

Any help is much appreciated!

[DamoBird365 · Accepted solution]

Hi @Facilities_AV 

How did you get on with your solution?  I have covered parsing CSV's here 

https://www.damobird365.com/parse-a-csv-to-json-array-quickly/

and

https://www.damobird365.com/magic-efficient-csv-to-json/

If you need a hand, give me a shout.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Facilities_AV]

Hi Damien - Thanks very much for your great blog and YT channel - super useful stuff!

Your solution get's me close, but no success just yet. My problem is I don't have any control over the formatting of the file I get, and the sender is using a date format with a comma inside the field, which is splitting the fields in an incorrect way.

So when I "get file contents" it it comes out like:

...and then after I do the "return line" and the "split" compose and then "ParseMyCSV..." (reducing to 10 columns) I get a return that splits the rows incorrectly:

So the error throws off the column relationships, breaking the dates and pushing subsequent fields into incorrect columns. Since the offending commas are inside double-quotes I don't understand why they are read as field separators? I'm sure there is an encoding element I'm missing that a more experienced operator would guess right away.

FWIW if I just open the .csv from OneDrive, Excel parses the dates correctly as some flavor of UTC:

Anyway, once I get this data encoding sorted out I feel certain that "ParseMyCSVbyDamoBird365" will be a great solution... Let me know if anything occurs to you!

Thanks

Eric D - ACT Facilities AV

[DamoBird365 · Accepted solution]

Hi @Facilities_AV 

I have a sample demo on how to handle commas with quote encapsulated csv's.  See here.

The aim of the game is to replace the commas with PIPES and then split the CSV.  You also might need to handle empty fields.

Take a look and let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Facilities_AV]

Hi Damien

Well that totally did the trick! Would have been nice to have the solution in a blog post like the original example (because you made it so easy!) but I was able to follow. I confess I had to really come to grips with the syntax of those serial replace/compose actions in order to pull it off - and I feel like I learned some new skill!

I had to go back and modify "parsemycsvbydamobird365" to work with the new "pipe delimited" version of the data, but I've now got a cleanly parsed JSON to work with:

I am marking BOTH the original response and the troubleshoot/fix for my messy CSV as "accepted solutions."

Thanks so much for your help!

Eric D - ACT Facilities AV

[DamoBird365]

Thanks @Facilities_AV, it's always a pleasure to hear that my content has been of use.  Looks like you have developed something pretty cool too!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Harold_Anderson]

That link gives an error: ERR_SSL_VERSION_OR_CIPHER_MISMATCH