How to Create a Power Shell Script to Add Data from a .csv File to a SharePoint 2013 List

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.

May 28, 2014

Questions?

Email [email protected] with any questions you have pertaining to this course.

Follow Dynamics 101



About D101 Academy

Dynamics101.com is a Rand Group Knowledge Center intended to provide our clients, and the Microsoft community, with on-demand training.

Should you require personalized support for your business, Rand Group offers a variety of Business Advantage support plans to fit your business' needs.

View Rand Group's Microsoft support options.

top
New CPE Accredited Courses Now Available for Dynamics AX, GP, and NAVEARN CREDITS TODAY