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
  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
  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
  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
  27. $filePath = "F:\Intranet Employee Report.csv"
  29. #Import CSV
  30. $CSVRecords = Import-Csv $FilePath
  31. Write-host -f Yellow "$($CSVRecords.count) Rows Found!"
  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. }
  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!!!