Transfer 1 million rows to Sharepoint list

[Prasanna_30]

I want to transfer 1 million rows to Sharepoint list, I tried different ways like Export to Sharepoint from MS access, Power automate flow, Export to Sharepoint from excel. Nothing is working. Data is in excel sheet, can anyone suggest me way to load this much data into Sharepoint list ?

[#2 · Accepted solution]

Hi @Prasanna_30 

firstly, this should work with PnP Powershell and the "Add-PnPListItem" Command (https://pnp.github.io/powershell/cmdlets/Add-PnPListItem.html)
Just call the following command a million times 

Add-PnPListItem -List "Demo List" -Values @{"Title" = "Test Title"; "Category"="Test Category"}

(But wait a few seconds every 100 items or so to make sure that you don't get throttled).

Secondly, DON'T DO THIS.

SharePoint is not a Database. It can handle that much data, but is not build for that. You will have problems later while acessing the data (i.e. just being able to download at max. 5000 elements per call and massive problems filtering your data if you did not set your indexes perfectly).

If you are using SharePoint Online then try to use a Dataverse table instead
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/data-platform-intro

Best Regards,
Sven

[#3]

@Prasanna_30

TThere is an easy way for that. Just use my description here:

Dynamic 10K+ Data Sync to SharePoint: Virtual Tables & Dataflow from Multiple Sources

It's a trick with virtual tables. Easy to build, I would say in 5-10 minutes and then just wait until the import is ready. 

[#4]

Hi!

Don't Truely and honestly don't input 1 million rows in to a SharePoint list as the SharePoint list will have a hard time to handle this.
It's better if you split it up to multiple lists. as Querying, breaking permissions and just finding information in the list such as searching will be a pain.

The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.

https://learn.microsoft.com/en-us/sharepoint/troubleshoot/lists-and-libraries/items-exceeds-list-vie...

[#5]

@SvenSieverding 

Thanks for you valuable feedback, I want to use that data for power apps, I tried exporting data to teams dataverse and connected to teams power apps, but major issue I am facing is unable to filter out data. If I apply any filter then it searches in top 30k records, is there any way to filter out all data. For example if I search any unique Id then it will show up.

[#6]

We can also do this with this Power Automate template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoin...

You could even regularly use it to the update that same SharePoint list after the million records are loaded.