kwizcom banner advertisement

SharePoint Online: Import CSV File into SharePoint List using PowerShell-CSOM

Scenario: My requirement is to import data from CSV file to SharePoint online list from a local drive, every day!
import CSV data into SharePoint Online List - PowerShell Script

PowerShell Script to import SharePoint List Items from CSV File:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/POC/"
$ListName="Contacts"
$ImportFile ="c:\Scripts\EmpData.csv"
$UserName="Salaudeen@crescent.com"
$Password ="Password goes here"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
 
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get the Data from CSV and Add to SharePoint List
$data = Import-Csv $ImportFile
Foreach ($row in $data) {
    
    #add item to List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $Item = $List.AddItem($ListItemInfo)
    $Item["FirstName"] = $row.FirstName
    $Item["LastName"] = $row.LastName
    $Item["Department"] = $row.Department
    $Item["Mobile"] = $row.Mobile
    $Item.Update()
    $Context.ExecuteQuery() 
   
}
Write-host "CSV data Imported to SharePoint List Successfully!"
SharePoint Online: Import CSV File into SharePoint List using PowerShell-CSOM SharePoint Online: Import CSV File into SharePoint List using PowerShell-CSOM Reviewed by Salaudeen Rajack on September 09, 2015 Rating: 5

11 comments:

  1. Great Script - I am just learning and I was able to make it work! Thank you!

    ReplyDelete
  2. I am getting this error Exception calling
    "ExecuteQuery" with "0" argument(s): "Column 'SamAccount' does not
    exist. It may have been deleted by another user.

    ReplyDelete
    Replies
    1. Change the $UserName and $Password Variable values to yours!

      Delete
    2. Got it to work! Had an incorrect Field - Thank you Great Script :)

      Delete
  3. Hello, great. I use SharePoint CSOM 16 not 15. I changed the path, but I become:
    The Setup command may only be used inside a Describe block.
    In C:\Program Files\WindowsPowerShell\Modules\Pester\3.4.0\Functions\Describe.ps1:125 Zeichen:9

    thank you

    ReplyDelete
  4. Hi Sir,
    I'm getting an error below
    Exception calling "ExecuteQuery" with "0" argument(s): "Column 'ID' does not exist. It may have been deleted by another user. /test/Lists/xxxxxxxx

    ReplyDelete
    Replies
    1. That's because, "ID" column (or any other read-only columns) can't be updated!

      Delete
  5. How do i import user field values?

    ReplyDelete
    Replies
    1. Use: $Item["UserName"] = $Context.Web.EnsureUser($row.UserName)

      Delete
  6. Can we verify if the CSV field has valid entries before import?

    ReplyDelete
    Replies
    1. Sure, If(![string]::IsNullOrEmpty($row.FieldName))
      {
      $Item["FieldName"] = $row.FieldName
      }

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.