Forum Discussion
Managing Large Lists
Here is my current situation -
Weekly, I receive an updated copy of our company roster. It's an employee directory, essentially, containing nealry 40,000 rows of data. What would be the most efficient way to update this week weekly with the newest data? For past projects, I just linked my SP site to Access and created some macros that would delete the old and add the new. However, deleting (approx.) 40k records and then adding them again takes quite a bit of time.
Just wondering if anyone else is in a similar situation or has done this before. i.e. weekly refresh of large lists in SharePoint from excel files. Any help is greatly appreciated!
7 Replies
- Does it works to remove all the records in the list and re-add them? You could easily automate this with a simple PowerShell script
Deleted Forgive me as I dodge around your original question and ask about your end-goal instead. Assuming you are using ADFS, is the data you use for this roster also reside in ADFS? The reason I ask--my organization recently is exploring dealing with employee directory maintenance by leaning on Microsoft Teams' Who bot instead of a list. Who isn't ideal for browsing employees, but it is very good at returning AD information about specific people and even provides some extra information you can't get from a directory, such as what people that person most communicates with.
As for your original question--the best way I can think of to manage a list that large other than delete/add would be to set up a delta job. In theory, you could do this with a tool like SSRS via Kingswaysoft or maybe even Flow. To pseudo-code the process, you basically have this:
- Match unique identifiers (i.e., employee number) from new list (the most recent employee directory data) to master list (your SharePoint list)
- If new list has a new unique identifier not found in the master list, add the new record to your master list
- If master list has a unique identifier that isn't found in the new list, delete that record from the master list
- If unique identifiers match, but any of the other fields in don't match between the master list and the new list, overwrite master list record with new list record information
Delta jobs should, in theory, run faster than a drop-and-load process.
My end goal is to create a front end via PowerApps for the Directors that allows them to quickly filter the list by various levels (which are fields in the list) within the org. I simply want to design a quick, view only form to display details of everyone that fits the criteria. I'm not using any type of active directory. The data in the list is coming via Excel file. This file is received daily via email, but I only need to update/refresh my SP list weekly. Since the excel file has nearly 40k rows of data, the delete/append queries that I've typically been doing for similar projects take way too long. So I'm just wondering if there is an easier way to accomplish this.
Deletedan alternative approach would be to add the extra fields to Azure AD and then connect your power app to that. This would eliminate the file in the middle problem and provide a much richer experience for the app users. The Org Browser app template that comes with PowerApps provides an example that could get you started
- Match unique identifiers (i.e., employee number) from new list (the most recent employee directory data) to master list (your SharePoint list)
