Saturday, January 11, 2014

Use PowerShell to Edit, Delete and Add Items from a CSV file

Business Problem:
Users do the following:
  • Copy items from an excel spreadsheet to a SharePoint list (List 1)
  • Run workflows to see if the item appears in a secondary list (List 2).  
  • If the item in List 1 exists in List 2, update List 2 with the information in List 1.
  • If the item doesn't exist in List 2, but does in List 1, add it to List 2.
  • If the item exists in List 2, but doesn't in List 1, delete the item in List 1.
They were really good at solving their work problems using the tools at hand: Nintex and Excel, and copying and pasting into data view.  The only problem was that all of these operations took a toll on the SharePoint servers, caused extreme slowness for every site and still took users some time to do the copy and paste, as they were doing about 1000 rows every time.

Goal: Automate this process using PowerShell.  They could copy and paste without issues using SharePoint 2007, but after the migration to SharePoint 2013 we found that SharePoint could no longer copy and paste over about 100 rows (30 columns) of data.  It froze.  Plus, all the workflow was really a problem for slowness, so automating this and moving it to 3 or 4 in the morning was a real win.

Here is the script.  As usual, use it at your own risk.  I guarantee this works for me, but if it doesn't for you or causes your system to blow up and excrete melted marshmallows (think Ghostbusters!), I am deeply sorry, but can't be held responsible.

Comments are in green and variables you need to change to use it are in blue.

#specify file and check to see if it exists.  If it does, continue operations
$csvVariable= Import-CSV "\\networkpath\MyFile.csv"
if ($csvVariable) {"File exists"} else {exit}

# Check if the Sharepoint Snapin is loaded already, and load if not
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{Add-PSSnapin Microsoft.SharePoint.PowerShell}
$WebURL = "http://mySharePoint/sites/mysharepointsite" #site name
$listName = "MyListName" # list name
$web = Get-SPWeb -identity $WebURL #Get the SPWeb object and save it to a variable
$list = $web.Lists[$listName] #Get the SPList object to retrieve the list
$items = $list.items #Get all items in this list and save them to a variable

#loop through SharePoint list and delete items that don't appear in csv file
    foreach($item in $items)
      $exists = 0
      #loop through csv and compare item to each row
      foreach ($row in $csvVariable)
        if ($item.Title -eq $row.PID)
      #if the item hasn't been found, delete from SharePoint list
      if ($exists -eq 0)
#loop through csv file
foreach($row in $csvVariable)
    $updated = 0
    #loop through SharePoint list
    foreach($item in $items)
        if($item["Title"] -eq $row.CSV column name)
                #Change the value of Student name SP list to Student name csv
                $item["Title"] = $row."CSV column name"
                #Update the item
        #add new item if an update wasn't made
        if($updated -eq 0)
                $newItem = $list.items.Add()       
                $newItem["SP column 1"] = $row."csvcolumn 1"
                $newItem["SP column 2"] = $row."csv column 2
                #had to use internal column name here because of some special characters
                $newItem["Column_x0020_name_x0020_Date"] = $row."Csv Date"      

No comments:

Post a Comment