Post
Ask Question

Export CSV To SharePoint List Data Using PnP PowerShell

Introduction

 
Many times we have requirements like having to add a CSV file to the Sharepoint list so if there are many records then manually this work becomes difficult. So in this blog, we will see how we can achieve this using Powershell. 
 
So here I have used PnP PowerShell to achieve this solution. 
 

Scenario

 
I have a CSV file and in this CSV file, I have 6 columns like FirstName, LastName, JobTitle, Location, BirthDate, and HireDate. So here we will do all the things like creating a list, fields, and list items by PowerShell. so by running a script we can do all the things.
 
So let's see the step by step solution. 
 

Implementation

  • Open Windows Powershell ISE 
  • Create a new file 
  • Write a script as below,
    • First, we will connect the site URL with the user's credentials.
          To connect the SharePoint site with PNP refer to this article. 
    •  Then we will create a list and fields. so field types will be as a below,
           FirstName,LastName,JobTitle,Location - Single line of text
 
           BirthDate, HireDate - Date and time
 
           So for this, we will use the Add-PnPField method.
    • We will import the CSV using the Import-Csv method.
Then we will add the records to the list using the Add-PnPListItem method. So here first we will get records from CSV and it will return an array so we will iterate it and then save all items in the list. 
  1. $Login = #userid    
  2. $password = #password  
  3. $secureStringPwd = $password | ConvertTo-SecureString -AsPlainText -Force     
  4. $Creds = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList $Login, $secureStringPwd   
  5. $siteUrl = #siteUrl  
  6.  
  7. #connect to site  
  8. Write-Host "Connection to the site..." -ForegroundColor Yellow  
  9. Connect-PnpOnline -Url $SiteUrl -Credentials $Creds       
  10. Write-Host "Connection successfully..." -ForegroundColor Yellow  
  11.  
  12. #create a list  
  13. Write-Host "Creating list..." -ForegroundColor Yellow  
  14. New-PnPList -Title "Employees" -Url "lists/Employees"   
  15. Write-Host "List created..." -ForegroundColor Yellow  
  16.  
  17. #create fields  
  18. Write-Host "Creating fields..." -ForegroundColor Yellow  
  19. Add-PnPField -List "Employees" -DisplayName "First Name" -InternalName "FirstName" -Type Text -AddToDefaultView  
  20. Add-PnPField -List "Employees" -DisplayName "Last Name" -InternalName "LastName" -Type Text -AddToDefaultView  
  21. Add-PnPField -List "Employees" -DisplayName "Location" -InternalName "Location" -Type Text -AddToDefaultView  
  22. Add-PnPField -List "Employees" -DisplayName "Job Title" -InternalName "JobTitle" -Type Text -AddToDefaultView   
  23. Add-PnPField -List "Employees" -DisplayName "Hire Date" -InternalName "HireDate" -Type DateTime -AddToDefaultView  
  24. Add-PnPField -List "Employees" -DisplayName "Birth Date" -InternalName "BirthDate" -Type DateTime -AddToDefaultView  
  25. Write-Host "Fields created..." -ForegroundColor Yellow  
  26.   
  27. $filePath = "F:\Intranet Employee Report.csv"  
  28.  
  29. #Import CSV  
  30. $CSVRecords = Import-Csv $FilePath  
  31. Write-host -f Yellow "$($CSVRecords.count) Rows Found!"  
  32.  
  33. #create list items  
  34. Write-Host "Creating list items..." -ForegroundColor Yellow  
  35. foreach ($Record in $CSVRecords) {  
  36.     $items = Add-PnPListItem -List "Employees" -Values @{  
  37.         "Title"     = $Record.'FirstName' + " " + $Record.'LastName';  
  38.         "FirstName" = $Record.'FirstName';  
  39.         "LastName"  = $Record.'LastName';  
  40.         "Location"  = $Record.'Location';  
  41.         "JobTitle"  = $Record.'JobTitle';        
  42.         "BirthDate" = $Record.'BirthDate';  
  43.         "HireDate"  = $Record.'HireDate';  
  44.     }  
  45. }  
  46.   
  47. Write-Host "list items created..." -ForegroundColor Yellow  
Now run the script with the F5 command.
 

Output

 
When the script runs it will ask for the list template. So after setting the lists template first it will create a list then fields and then list items as below. 
 
Output - Export CSV to SharePoint 
 

Summary

 
In this article, we have seen how to export CSV to SharePoint list data using PNP PowerShell.
 
Hope you like this. If it is helpful to you then share it with others. Give your valuable feedback and suggestions in the comments section below.
 
Sharing is caring!!!


Similar Articles