Forum Discussion

Prasanna_30's avatar
Prasanna_30
Copper Contributor
Jun 07, 2023
Solved

Transfer 1 million rows to Sharepoint list

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 i...
Microsoft Flow
sharepoint
  • SvenSieverding's avatar
    SvenSieverding
    Jun 07, 2023

    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

takolota1515's avatar
takolota1515
Copper Contributor
Jul 19, 2023

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

 

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

Resources