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
3

It's a normal behavior based on your current server specifications. You can imagine updating 8000 items in one shot! also, looping to checks the corresponding item and to check if exists update it if not add it! 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 important to track 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 working hour at a different interval.
  • 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 '17 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! – Mohamed El-Qassas MVP Jun 26 '17 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 '17 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.