Update Sharepoint from excel over 5K items

[vffdd]

Just wondering about the best way to do this from an efficiency and run point of view.  Bit confused on the licencing restrictions around this.  I have a flow created but it seems to take forever (I stopped it at an hour and it still wasn't finished)

I need a flow that will update a sharepoint list based on changes and additions to an excel file.  The excel file has over 3K items..and if I use an "apply to each" loop then that seems nuts? but I don't know any other way to go around it.  

I need the flow to update the sharepoint list (add new rows and amend existing ones) based on changes made in the excel file (existing info changed and new items added)

Is it even possible to do this for that number of items in a flow? or should I be thinking of some other way?

TIA

[WillPage]

Consider using Azure Data Factory if this is something you're going to be doing frequently and you can see your dataset scaling up over time. For just a one-off job, yes, your Apply to each loop is the way to go. It'll be very slow and you might have failed iterations of the loop due to API throttling limits so build in some solid error handling and use the retry policy to your advantage.

Personally, I've never looked into it, but I understand there's a way to bulk create or update list items using the Send an HTTP request to SharePoint action, but I don't know how flexible it is.

[takolota]

@vffdd & @WillPage 

It’s all definitely possible, in a couple ways actually.

1st

For creating the new records in SharePoint, you can check if the SharePoint list contains each Excel row.

So do a Get items on the SharePoint list. Then identify a primary key column or a column that is filled in & unique for every row in both the SP list & Excel file. If the primary key for a row could by chance show up in another column of the list, then you will want to use a Select action on the SP values & switch the view to the plain box & insert the dynamic content for your primary key row. Example:

That should generate an array of just the primary key values in the SP list. From there you can use a Filter action on the Excel values & check for any Excel row where the array of SP primary key values does not contain the primary key value of the Excel row. Example: 
From there you can use the Parse JSON action on the Filter array action to get the filtered array of just the Excel rows that do not exist in SharePoint yet. Then you can use a Create item action to just create those rows in SP.

2nd
You probably can't do something similar to what I just described for the rows you need to update if you need to track updates across multiple columns. It would just be too much work & too complex to create or maintain that way.
However, I did create a batch update SharePoint flow for just this type of scenario. You should be able to use the template in this thread to batch update every row & it will only consume a handful of action API calls per 800-1000 rows. (Batch Update SharePoint List With External Data - Power Platform Community (microsoft.com))

[vffdd]

Thanks guys.   @takolota  I will definitely try your method just one query  where you say  "From there you can use the Parse JSON action on the Filter array action to get the filtered array of just the Excel rows that do not exist in SharePoint yet. Then you can use a Create item action to just create those rows in SP."  yeah I'm not the best at that LOL do you have an example? 😁 The create bit is fine it's the Parse JSON action...

[takolota · Accepted solution]

@vffdd 

Sure, so once you have everything set up to the Filter array, add the Parse JSON action and put the Filter array Body dynamic content into the Parse JSON action.

Then put two brackets [ ] in the schema part as a placeholder. Do a test run of the flow. That run should fail due to the wrong schema. But in the flow run, you can grab the array outputs of the Filter array action, go back to edit mode, select Generate from sample on the Parse JSON, and paste the Filter array output into the pop-up box to generate the schema for your Filter array data.

[takolota]

@WillPage & @vffdd 

I almost forgot, if you can get your Excel table to track the last time each row was modified, then you can also filter down to just the rows that were updated since the last refresh for the update portion of this. Then you may not even need to set-up the batch update method.

[vffdd]

Thanks I Like that option !