Add data from a CSV arriving via email as new rows into a table in an Excel file in SharePoint

(1) Share

Report

Posted on by DanJECC

Hi all. I need some assistance with a flow I am trying to build. We have a CSV report that is generated every 2 minutes with a summary of contracts processed that day up to the point that report was generated. As such each report contains all of the data of the previous report, plus the new data from the last 2 minutes. This is to ensure no data is missed if the reports only contained the last 2 minutes' worth and one was delayed. The reports are generated and sent to a Microsoft 365 shared mailbox.

What I want to do is compile the data into an Excel table, with the Excel file stored in SharePoint. From there the data can be queried by one or more other PA flows that I may create in the near future.

I am stuck on two main parts, firstly how to handle the incoming data, it is a CSV file attached to an email; is there an easy way to have PA read the content without having to save a copy of the attachment somewhere first? Secondly, how can I compare the new data to the data already in the destination table and discard any duplicates rows?

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Michael E. Gernaey 53,414 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Let me give the high level answers.

Note.. please use SharePoint not excel for storing, unless you have too.

1. Yes you can just use the Content provide from the Get Attachments instead of writing it and opening it

2. You will need to parse your CSV into either an Array or a JSON Object. JSON is better, but Array can sometimes be easier, its 50/50 depending on the data.

Power Automate: How to parse a CSV File to create a JSON array (tachytelic.net)

How to parse a CSV file in Power Automate (new template)? - Manuel T. Gomes (manueltgomes.com)

PS. this assumes your data is Text only, if its not you will first need to add a Compose to convert it to Text. I cannot tell you what yours is, as I can't see it. Essentially do a test. Send a copy of the data to yourself or that mailbox. Have a sample flow to do only get email and then Get Attachments and loop through the Attachments. In the run you can see if its straight text.

3. Once you have your Data you will use an Apply to Each to loop through the rows IN the in-memory content you created in #2

Inside your Apply to each, you will need to use the List Rows in a Table for Excel, which will have a filter on it, based on the data in the loop.

You are trying to query the existing data to see if the row you got matches.

Then you will have to compare the Data column by column in a Compose or Condition (condition is limited to how many fields it can do at a time)

4. If they do not match, then use Create a Row for Excel

5. If they do match, do nothing

Since 4/5 are sort of a Yes/No situation, if you have like I think 12 columns or less, then use the Condition, so you can easily have the Yes/No paths of the condition to upt your update or create code

Was this reply helpful? Yes No
DanJECC 42 on at

Like (0)

Report

Hi FLMike

Thank you, this is a helpful starting point to get me going, I have been using PA bit-by-bit as my role demands over the last 6 months and am still learning.

You mention using SharePoint and not Excel for storing the data, do you mean a SharePoint list? Can it store a table of data similar to a spreadsheet, with 12-15 columns and hundreds of rows? At the moment my plan was to store the data in a single "master" Excel file stored in a SharePoint doc library. One of the reasons for this is so that if we want to wipe the list at the end of the day, we can first dump that day's data into a backup Excel file (with datestamp in filename) elsewhere in the SharePoint, and then clear the main list (all through PA automations).

For the comparison of reports, there may be a simple way to do it, just not sure of the actual methodology. Since each report has all the data from the day up to that point, any data that exists in previous reports is on the same rows. ie. if the report from 2 mins ago has 283 rows and the latest report has 290 rows, the first 283 of 290 rows in the latest report will be identical to the report from 2 mins ago.

So in that case, can the comparison just be based on the number of rows, find the difference, and then grab the last xx number of rows (where xx is that difference) and stick them into the master list?

Was this reply helpful? Yes No