Monday, April 29, 2013

Import From CSV File into SharePoint List using Powershell

Periodically, We needed the data from a CSV file, which is generated by an external Third-party application, to be imported to SharePoint 2010 List. To fulfill this requirement,  I wrote a quick PowerShell script which will read the CSV file and import the data into SharePoint list using PowerShell.

Import CSV to SharePoint List using PowerShell

PowerShell Script to import from CSV file to SharePoint 2010 list
Lets import csv file to SharePoint list using PowerShell
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Read the CSV file - Map the Columns to Named Header (CSV File doesn't has Column Header)
$CSVData = Import-CSV -path "C:\Data.csv" -Header("Title", "Description", "Priority", "AssignedTo", "DueDate", "Status")

#Get the Web
$web = Get-SPWeb -identity ""

#Get the Target List
$list = $web.Lists["Log"]

#Iterate through each Row in the CSV
foreach ($row in $CSVData) 
   $item = $list.Items.Add();
   $item["Title"] = $row.Title
   $item["Description"] = $row.Description
   $item["Priority"] = $row.Priority

   #Set the People Picker Field value
   $item["Assigned To"] = Get-SPUser -Identity $row.AssignedTo -web ""
   #Set the Date Field value
   $item["Due Date"] = Get-Date $row.DueDate

   $item["Status"] = $row.Status

We scheduled this script through Windows Task Scheduler: How to schedule a powershell script using windows task scheduler

In an another case, before importing a list item from CSV file, I had to check whether the item which is being added, is already exists in the List. If It doesn't exists, let the script add new item from CSV file.
$ListItems = $reconciliationList.Items | Where-Object { $_.Item("Title") -eq $row.Title}

  if ($ListItems -eq $null)
      #ADD Item to the List

Update Lookup values in PowerShell:
Unlike other fields, SharePoint look-up fields can't be set directly. We have to get the lookup parent id, to update the lookup field values.
 #For Lookup values: Lookup Parent List

 #Get the Lookup Item from Parent List
 $LookupItem = $departmentList.Items | Where-Object { $_.Item("Title") -eq $row.Department}

 if($LookupItem -ne $null)
     $deptLookup = New-Object Microsoft.Sharepoint.SPFieldLookupValue($LookupItem.ID,$row.Department)

 #Set the Lookup field value
 $item["Department"] = $deptLookup

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Check out these SharePoint products:


  1. Replies
    1. For MOSS, use:
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null;

      $site=new-object Microsoft.SharePoint.SPSite($SiteULR);

  2. To get this to work for lookup values, i had to replace the "if" condition with the following:-
    if($LookupItem -ne $null)
    $LookupListItem = $LookupItem[0]
    $deptLookup = New-Object Microsoft.Sharepoint.SPFieldLookupValue($LookupListItem.ID,$row.department)

  3. In this "$ListItems = $reconciliationList.Items | Where-Object { $_.Item("Title") -eq $row.Title}

    if ($ListItems -eq $null)
    #ADD Item to the List
    What is $reconciliationList.Items and when i execute the script its not working..
    Please help

    1. $reconciliationList is a SPList object!


Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...