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

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:

Note: deleteExistingItems is included to make sure existing items are deleted before adding new item values.

May 28, 2014