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.