
Power shell is taking a more and more important position in SharePoint development and administration. After STSADM was deprecated, power shell become a more powerful tool with many added advantages. Keeping this in mind, today I’ll discuss a simple yet very useful business function: adding data to a SharePoint list using power shell.
There are many ways to add data to a SharePoint list. For some, understanding a scenario of how to add it using a power shell script might be useful.
Reasons you would want to add data to a SharePoint list using a power shell script include:
- When an administrator wants a way to change data before populating a list
- When an administrator needs to run the same data add operation on the same list in multiple sub sites
- When an administrator needs a script to get this operation done and cannot use a feature activation due to some business restrictions
- When an administrator needs to add data based on a .csv file that may be provided from some other source
For our scenario let us write the power shell script. Please note the points below:
- We will add data to a list named Test Types
- For simplicity, let us assume the Test Types list has only one column
- We will have some values in a .csv file
- The script should import all these values to the Test Type list column
- We will include the best practice of adding a log file entry
Defining the script to add data
Param( [Parameter(Mandatory=$True)] [string]$url, [Parameter(Mandatory=$True)] [string]$inputFile, [Parameter(Mandatory=$True)] [string]$logFile, [switch]$deleteExistingItems ) Start-Transcript -path $logFile -Append $List = "Test Types" [void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") $site = new-object Microsoft.SharePoint.SPSite($url) $web = $site.rootweb $OpenList = $web.Lists[$List] if (($deleteExistingItems -eq $true)) {"Deleting items in list $OpenList" | out-default; while($OpenList.Items.Count -gt 0){$OpenList.Items.Delete(0)}} #loop through a csv file that contains list item data to add Import-Csv $inputFile | ForEach { $newitem = $OpenList.items.Add() $newitem["Title"]= $_.TestTypes; $newitem.Update() # output results to screen "New Item $_ is Added to list $OpenList" | out-default } $web.Dispose() $site.Dispose() Stop-Transcript
Explanation of the above script:
- We have defined params to take the input parameters to the script
- All the parameters are self-explanatory, e.g., $url represents the url of the site where the Test Types list is present
- According to best practices we should have an environment variable file to store these values
- You can, of course, hard code the values in this script
- The script retrieves the SPWeb object, then gets the list based on the name mentioned, then deletes existing items – if that parameter is set to true in the script call, then it iterates through the .csv file to find the values, creates a new list of items in the Test Type list, updates the list, and disposes SPSite and SPWeb objects. The steps are self-explanatory.
Now let us call the script passing the parameters
Assumptions:
- The above power shell script is stored as ‘AddSPItemTestTypes.ps1’.
- The .csv file is named as ‘TestType.csv’ and it has the values for a single column
The call for the script can be written as:
$List = "<url to the site>" $ filename = "<log file>" 'AddSPItemTestTypes.ps1' -url $url -inputFile 'TestType.csv' -logFile $filename -deleteExistingItems
Note: deleteExistingItems is included to make sure existing items are deleted before adding new item values.