Create and update list items from csv file

Question

I have below scripts to import a CSV file into SharePoint list. I found the scripts from this link https://www.linkedin.com/pulse/auto-sync-external-data-native-sharepoint-list-lukasz-latynski i made minor changes to help compare list items with the CSV file but everything else is the same.

The scripts run fine when adding new item. i can run the scripts to add for example 17000 items and it is complete within 3hours. However, it is taking a long time when updating an existing item. last week, i was trying to import a CSV file with about 8000 items which already exist in SharePoint and it took about 24hours to update 8000 items.

Is there a way to make the scripts update the list items faster?

Here is the current scripts i'm using:

#Setup the correct modules for SharePoint Manipulation
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
   Add-PsSnapin Microsoft.SharePoint.PowerShell
}

function Update-SPList()
{
$host.Runspace.ThreadOptions = "ReuseThread"

#Import to destination list
#This section of the PowerShell will loop through the csv file we created, compare the inventory list and add new item if it didn't exist.
$csvVariable= Import-CSV -path "C:\ServerScripts\excell.csv"

# Destination site collection
$WebURL = "https://sharepointsite"
# Destination list name
$listName = "SharePoint List"

#Get the SPWeb object and save it to a variable
$webDestination = Get-SPWeb -identity $WebURL

#Get the SPList object to retrieve the list
$list = $webDestination.Lists[$listName]

#Get all items in this list and save them to a variable
$items = $list.items
#loop through csv file
foreach($row in $csvVariable)
{
#set variable for adding new items
$added = 0
#set variables for specifying a query needed for updating items
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$camlQuery = '<Where><Eq><FieldRef Name="Employee ID" /><Value Type="Text">'+ $row."Employee ID" +'</Value></Eq></Where>'
$spQuery.Query = $camlQuery
$listItems = $list.GetItems($spQuery)
$updateitem = $list.Items | Where { $_["Employee ID"] -eq $row."Employee ID" }
#loop through SharePoint list
foreach($item in $items)
{
#check if item exists
if($item["Employee ID"] -eq $row."Employee ID")
{
#Write-Host "Item already on the list" -ForegroundColor Green
$added++
}
}
#add new item if item does not exist
if($added -eq 0)
{
###Write-Host "Adding a new item" -ForegroundColor Green
write-output $row."Employee ID" | Add-Content "C:\ServerScripts\Logs\Addeditems.txt"

$newItem = $list.items.Add()
$newItem["Employee ID"] = $row."Employee ID".ToString()
$newItem["Employee Name"] = $row."Employee name".ToString()
$newItem["Employee Phone"] = $row."Employee Phone".ToString()
$newItem["Team"] = $row."Team".ToString()
$newItem["Employee Location"] = $row."Employee Location".ToString()
$newItem["Employee Address"] = $row."Employee Address".ToString()
$newItem.Update()
}
#check if "Employee ID" in CSV is equals "Employee ID" column in SharePoint list- item needs to be updated
elseif ($updateitem["Employee ID"] -eq $row."Employee ID")
{
###Write-Host "Updating" $updateitem["Employee ID"] -ForegroundColor Yellow
write-output $updateitem["Employee ID"] | Add-Content "C:\ServerScripts\\Logs\updateditems.txt"

#$updateitem["Employee Phone"] = $row."Employee Phone".ToString()
$updateitem["Employee Address"] = $row."Employee Address".ToString()
$updateitem["Employee Team"] = $row."Employee Team".ToString()
$updateitem["Employee Location"] = $row."Employee Location".ToString()

$updateitem.Update()
}
#otherwise no update needed
elseif ($updateitem["Employee ID"] -ne $row."Employee ID")
{
Write-Host "No need to update" -ForegroundColor DarkYellow
}
}
#dispose of SPWeb variable
if ($webDestination)
{
$webDestination.Dispose()
}
}
Update-SPList

Answer 1

It's the normal behavior based on your current server specifications. You can imagine updating 8000 items in one shot! Also, looping to check the corresponding item and to check if it exists, if it does exist update it, if not add it as a new item! It's a big headache!!

In case you need to decrease PowerShell execution time, you have to do the following:

• Try to use a CSOM PowerShell with similar functionality at Updating Sharepoint List items and compare its performance with your current script, it should be faster.
• Also, you can remove Log step write-output $row."Employee ID" | Add-Content "C:\ServerScripts\Logs\Addeditems.txt" despite it's importance in tracking your work, meanwhile removing this line will accelerate the script, otherwise you can't remove any part of the above script.
• Scale up your current server specifications (Memory, CPU)
• Divide your CSV file to sub files,
• Create a Windows Task Schedule to run your script on each subfile out of working hours at a different interval.