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!
sharepoint online powershell import csv
Create a List in SharePoint Online, matching columns from the CSV file, before running the script!

PowerShell Script to import SharePoint List Items from CSV File:
Here is the SharePoint Online PowerShell to import CSV
#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!"
This imports csv to SharePoint Online list using PowerShell.
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

18 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
  7. My CSV has a column "Name of the Customer", How should I code it?

    ReplyDelete
  8. Hello,
    Thanks for the script, Perfect.
    But I have a Question.
    My problem is that always the whole content of the CSV file is transferred and not only values that are new or not available

    Is there a way to check this and import only new or missing values?

    ReplyDelete
    Replies
    1. Yes, Basically your requirement is: Instead of Importing the whole CSV file, you'll have to Update from CSV file. Use: Update SharePoint List Items from CSV File using PowerShell

      Delete
  9. Thanks Yr.. You just saved my precious time..Blessings from India..

    ReplyDelete
  10. hi, what you should is adding all new records to existing list. but how to do update certain filed(column) of existing list entries from CSV? for example, I have the following 2 entries in my .csv, as well as in my SharePoint Online list, like this:

    firstname lastname Office
    John Doe NewYork
    Jane Doe Buffalo

    And I want to use a CSV to update both people's office to Washington, how would I go about doing that? thanks in advance!

    ReplyDelete
  11. Exception calling “ExecuteQuery” with “0” argument(s): “For security reasons DTD is prohibited in this XML document. To enable DTD processing set the Dtd Processing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method.”

    ReplyDelete
  12. Exception calling "ExecuteQuery" with "0" argument(s): "For security reasons DTD is prohibited in this XML document. To enable
    DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method."
    At C:\Users\xxxxx\xxx\Upload.ps1:32 char:5
    + $Context.ExecuteQuery()
    + ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : XmlException

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.