1

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

1 Answer 1

3

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.
3
  • Thank you for the good suggestion. I'm already using a task scheduler to start the scripts on the weekend (Saturday) but it is still on the next Monday which is why I was hopping to find a better way to update the list items. I increase the RAM to 40GB on the DB and 30GB on App and 16GB on each WFE but that did not help.
    – Gene
    Jun 22, 2017 at 17:37
  • do you try this script sharepoint.stackexchange.com/questions/25466/… Also please don't forget to upvote and mark as an answer in case it helps you thanks! Jun 26, 2017 at 0:08
  • I did try this scripts but it did not do what i wanted to accomplish. the scripts I'm using works fine i just need to figure out a way to make the process faster. the first suggestion suggest that i try to use a CSOM PowerShell with similar functionality but I'm not sure how to convert all the functions yet.
    – Gene
    Jun 26, 2017 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.