Forum Discussion

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

SvenSieverding

Bronze Contributor

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

Prasanna_30

Copper Contributor

Jun 09, 2023

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.