Jun 07 2023 05:50 AM
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 ?
Jun 07 2023 10:22 AM
SolutionHi @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
Jun 07 2023 11:41 AM - edited Jun 07 2023 11:42 AM
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.
Jun 07 2023 02:29 PM - edited Jun 07 2023 02:30 PM
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.
Jun 09 2023 06:59 AM
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.
Jul 19 2023 02:08 PM
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.
Jun 07 2023 10:22 AM
SolutionHi @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