How to import bulk rows of data into SharePoint 2019 List

Question

Hi

I am trying to import a excel rows data ( it has around 3500 rows ) into a SharePoint Custom list

and i am getting issues please support how to import all rows data into list ?

what i tried

1. i tried to copy all rows and paste in edit mode of list and after some time page is unresponsive and no rows copied
2. from excel i tried to export table to SharePoint list but excel shows message "Cannot connect to the server at this time" from "Export Table to SharePoint List" dialog box
3. When i run PowerShell code in Server for Import data from Excel i got below error message and there is no MS-Excel installed in SharePoint Server

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed

due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

At D:\ImportExcel.ps1:10 char:10

• $excel = New-Object -ComObject Excel.Application
• CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException
  • FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

Answer 1

For the method 1, due to there are around 3500 rows in the excel file, it is not recommended to copy and paste items form excel file to SharePoint server.

For the method 2, Excel creates the list for you. In the Address box, type the address like this URL http://webapplication/sites/testsite. Also make sure that you log in Excel with account has enough permission.

For the method 3, please try following PowerShell.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Read the CSV file
$CSVData = Import-CSV -path "C:\Data.csv"
 
#If the CSV Doesn't has column Headers, you can create Named Headers
$CSVData = Import-CSV -path "C:\Data.csv" -Header("Title", "Description", "Priority", "AssignedTo", "DueDate", "Status")
 
#Get the Web
$web = Get-SPWeb -identity "siteurl"
 
#Get the Target List
$list = $web.Lists["sitename"]
 
#Iterate through each Row in the CSV
foreach ($row in $CSVData)
 {
   $item = $list.Items.Add();
    
   $item["Title"] = $row.Title
   $item["Description"] = $row.Description
   $item["Priority"] = $row.Priority
 
   #Set the People Picker Field value
   $item["Assigned To"] = Get-SPUser -Identity $row.AssignedTo -web "siteurl"
     
   #Set the Date Field value
   $item["Due Date"] = Get-Date $row.DueDate
 
   $item["Status"] = $row.Status
   $item.Update()
 }

Here is another way for you to import excel spreadsheet to SharePoint list.

Go to Site contents -> Import Spreadsheet -> Choose excel file to import data.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.