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 the SharePoint list using PowerShell.

How Import CSV into SharePoint List using Powershell?

PowerShell Script to import from CSV file to SharePoint list

Let’s import CSV file to SharePoint list using PowerShell

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Read the CSV file
$CSVData = Import-CSV -path "C:\Data.csv"

#If the CSV Doesn't has column Headers, you can create Named Headers
$CSVData = Import-CSV -path "C:\Data.csv" -Header("Title", "Description", "Priority", "AssignedTo", "DueDate", "Status")

#Get the Web
$web = Get-SPWeb -identity "http://sharepoint.crescent.com/sites/Marketing/"

#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 "http://sharepoint.crescent.com/sites/Marketing/"
    
   #Set the Date Field value
   $item["Due Date"] = Get-Date $row.DueDate

   $item["Status"] = $row.Status
   $item.Update()
 }

Import CSV to SharePoint List using PowerShell
We scheduled this script through Windows Task Scheduler: How to schedule a PowerShell script using windows task scheduler?

In another case, before importing a list item from the CSV file, I had to check whether the item which is being added, already exists in the List. If It doesn’t exist, let the script add a 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 lookup 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
 $DepartmentList=$web.Lists["Department"]

 #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
   
 $item.Update()

Here is my another post on importing data from CSV file to SharePoint Online list using PowerShell: Import CSV to SharePoint Online List using PowerShell

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

18 thoughts on “Import From CSV File into SharePoint List using PowerShell

  • October 8, 2021 at 6:46 AM

    Great script Rajack,
    I faced an issue when exporting lists containing columns with unicode values. It’s showing question marks. Any suggestions?

    Reply
    • October 8, 2021 at 1:37 PM

      Simple! Just use “Encoding” parameter for Export-CSV cmdlet. E.g.
      | Export-Csv -Encoding UTF8 -NoTypeInformation -Path out.csv

      Reply
  • August 18, 2021 at 12:38 AM

    This there away where I can read the CSV file and update the list without do the mapping like you’ve done mapping $item[“Status”] = $rowStatus?

    Reply
  • August 17, 2021 at 10:10 PM

    I have a lot of list to import form CSV file how can I map the Items to update in the list without mapping each list item to CSV row.
    How can I just update the list from the CSV file with out doing $item[“Title”] = $row.Title. I on Sharepoint 2019 on premise. Please help. Thanks
    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 “http://sharepoint.crescent.com/sites/Marketing/”

    #Set the Date Field value
    $item[“Due Date”] = Get-Date $row.DueDate

    $item[“Status”] = $row.Status
    $item.Update()
    }

    #Read more: https://www.sharepointdiary.com/2013/04/import-from-csv-file-to-sharepoint-list-using-powershell.html#ixzz73p6rjdeR

    Reply
  • November 18, 2020 at 9:07 AM

    Hi,
    I’ve got a problem with importing people picker value, I get the following error:
    Get-SPUser : Cannot validate argument on parameter ‘Identity’. The argument is null. Provide a valid value for the argument, and then try running the command again.
    At C:skrypt.ps1:24 char:43
    + $item[“AssignedTo”] = Get-SPUser -Identity $row.AssignedTo -Web $web ;

    Thanks for Your help.
    M.

    Reply
  • December 19, 2019 at 7:02 PM

    I need to import the yes/no field.

    Any help?

    Reply
  • August 5, 2019 at 11:31 AM

    When I added line with list validation, my script is importing empty fields. Could you help with this?

    Reply
  • July 27, 2017 at 12:18 PM

    When the CSV file has special characters like ä,ö they are not entered correctly.

    Reply
    • July 27, 2017 at 12:21 PM

      Convert the CSV file into Unicode and then process:
      Get-Content “C:ReportsData.csv” | Out-File “C:ReportsConverted-Data.csv” -Encoding Unicode

      Reply
  • July 27, 2017 at 12:23 PM

    How do I refer column names with space? E.g. instead of “Name” its “Name of the Person” in my CSV.

    Reply
  • June 12, 2017 at 4:28 PM

    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

    Reply
  • November 3, 2014 at 9:43 AM

    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)
    }

    Reply
    • February 28, 2017 at 12:15 PM

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

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

      Reply

Leave a Reply