Managing Large Lists

[Deleted]

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!

[#2]

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

[#3]

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

[#4]

@Matt Coats 

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. 

[#5]

If it's coming in from Excel, I would just consume the data right form the file. Then replace the file with the new one with the same name etc. This way your App just reads the data as is, no importing required.

This assumes however that the excel file is in some kind of relational / csv type formatting and is always in the same format .

[#6]

@Chris Webb - I tried to connect my PowerApp to the Excel file instead of the SharePoint list, but apparently there is a file size limitation. I received the error below. 

File size above allowed value. File size:6056073, AllowedFileSize (in Bytes): 2000000

[#7]

Ahhh, bummer. I personally would probably try to come up with a way to import using flow to an azure sql database to use. This way you also won't run into many delegation issues with PowerApps and can make use of more functions etc. You can get a basic one for 5 bucks a month to handle these tasks, and anyone can connect to it in your org since you can embed the sql connection to it. Or you could manually import using the sql tools to import the excel file. Just some thoughts. I wouldn't use SharePoint for it thou.

[#8]

@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