Post
Ask Question

Update List Items Using CSV File

At some point in time, you may want to update a SharePoint List using the data from a .CSV file which sits in a web application of any one of the SharePoint environments (SharePoint 2013/2016/SPO). Of course, we do have a lot of options to perform this task, like the following -

1. You can write a console application using SharePoint Server Object Model in case of SharePoint 2013/2016 environments;
2. You can write a console application using Client-Side Object Model (JSOM/.NET Managed CSOM) in case of SP 2013/2016/SPO environments;
3. You can write a PowerShell script in case you have admin rights to the On-Premise farm or a PowerShell script for an SPO environment. 

In this article, we are going to discuss the process of updating a SharePoint List using PnP PowerShell Module. To get to know more about the PnP PowerShell, click here. 

Let’s get started with the steps in brief.

Prepare the .csv file data in such a way that it should be updated in the target list, i.e., the columns should all be properly ordered.

Create a View in the target list which matches with the .csv file.

Once the View and the .csv file are ready, run the updated PowerShell script created from the below sample script.

  1. #Connect to the Site Collection   
  2. Connect-PnPOnline –Url http://SiteCollectionURL –Credentials (Get-Credential)   
  3.    
  4. #Import the column data from .csv file and store in a variable  
  5. Import-Csv D:\Data.csv |     
  6.     ForEach-Object {   
  7.         $Column1  = $_. Column1   
  8.         $Column2  = $_. Column2   
  9.         $Column3  = $_. Column3   
  10.         $Column4  = $_. Column4   
  11.         $Column5  = $_. Column5   
  12.     
  13.  Write-Host $ Column1  $ Column2  $ Column3  $ Column4  $ Column5   
  14.     
  15.       $itemVal = @{   
  16.       'Column1' = $Column1;   
  17.       'Column2' = $Column2;  
  18.       'Column3' = $Column3;  
  19.       'Column4' = $Column4;  
  20.       'Column5' = $Column5;   
  21.       }   
  22.       Set-PnPListItem -List 'CustomListName' -Identity $ID -Values $itemVal      
  23.   }    

As a result, the list gets updated as expected.

Thanks.

Sharing is caring!!