cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ELGHAZI009
Level: Powered On

Import data daily froma new Excel to Sharepoint list

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.

4 REPLIES 4
Super User
Super User

Re: Import data daily froma new Excel to Sharepoint list

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!

 

Create.pngPurge.png 

ELGHAZI009
Level: Powered On

Re: Import data daily froma new Excel to Sharepoint list

@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!

Super User
Super User

Re: Import data daily froma new Excel to Sharepoint list

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

Community Support Team
Community Support Team

Re: Import data daily froma new Excel to Sharepoint list

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

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,129)