Import data daily froma new Excel to Sharepoint list

[ELGHAZI009]

Hi everyone,

I have been struggling to this for weeks, and I have looked everywhere yet can't find a similar case to mine.
Here is my problem:
I have an Excel spreadsheet with data formatted as a table that comes every day to our Email, with the same structure just the values are different, and I need to move this data to SharePoint and keep updating daily when this file comes.
Now I have set up a Flow that when new email comes it move it to a folder in SharePoint, then I need to get rows from it to update a SharePoint list that has the same columns as the Excel spreadsheet, but who I can tell the flow a table name and file name while I don't have the file there yet !
I'm sure there is a solution to that and hope someone will help with that.
Thank you.

[Brad_Groux]

I have a similiar workload that I do daily, outside of getting the file from an email every day - I grab it from an FTP. You likely need three different Flows to perform this task, which would be: 

1. First Flow: Create a flow to monitor your email to grab the Excel file daily. You could do this by monitoring the FROM address, or the attachment type, Subject name, etc. Then have the Flow upload that Excel file to a document library. 
2. Second Flow: Purge the SharePoint List. I've found, that merging changes from an Excel file to a SharePoint Online List is nearly impossible - because it simply isn't reliable. So we created a SharePoint "Purge List" Flow that deletes all rows from the List daily (right before we upload the new list items).
3. Third Flow: The third flow is to upload the Excel table to the now empty SharePoint List on a recurring schedule. You can set the Trigger to be when a new file is added to the Document Library, or you could schedule it at a certain time daily. 

For us, we perform all of this on a recurring schedule. The "Download" Flow runs nightly at 10:00PM (a few hours after our client publishes the file every night). Then, the "Purge" Flow happens at midnight, with the "Upload" Flow happening 15 mintues later. The Purge flow runs in about 30 seconds, and and the Upload flow runs in about 60 seconds.

This combination works great for us, and has for months. You can certainly build all of this functionality into a single Flow, but I like having the ability of knowing right away if/when each step of the process fails. For one reason or another, we do have failures every so often but this allows us to remedy those failures right away. 

I've added screenshots of our Purge and Upload Flows, which hopefully may help. It sounds like your email that comes in daily may have spreadsheets that are named differently, and possibly have different table names, but the process should still pretty much be the same, you just need to add the logic to figure out the names, and/or add a step to the dowload Flow that renames the file/table names before uploading the Excel file to the document library. 

You may be able to get the table and spreadsheet to merge nightly, but again, we've found that purging the list daily is the best way to insure that we truly have reliable data daily. The merge functionality isn't reliable enough for us. The Excel connector is very troublesome, and in a perfect world we wouldn't have to use it - and I'm hoping that we eventually move this process to a REST API operation. But for now, this works for us.

Hope this helps!

 

[ELGHAZI009]

@Brad_Groux  ,

First of all, Thank you for taking the time and looking through this.
My problem is with the Excel part in the flow, as in your workflow in the filed of 'List rows present in a table', what did fill in the filed of 'File' and 'Table'?
Logically there is no file yet in the folder!

[Brad_Groux]

My file and table name never change, so it is easy for me to just set those statically. As I said in my first post, if yours is always named differently, you can just add the logic to the "download" flow to rename the file and/or table before it uploads the file to the document library, so it is always the same name.

See this great post by @v-micsh-msft on how to Modify Dynamic Content Before Use in Flow. 

[v-yamao-msft]

Hi @ELGHAZI009 ,

Thanks for feedback.

I am afraid that your scenario that using the Excel file as a dynamic value for the Excel Online actions could not be achieved currently.

There is a similar request on Allow for dynamic file value for Excel online (Business), please vote the idea at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Allow-for-dynamic-quot-file-quot-value-for-excel-quot...

PGs will take hot ideas into consideration first in the future releases.

Thanks for your understanding.

Best regards,

Mabel