SharePoint Online: Import CSV File into SharePoint List using PowerShell
Scenario: My requirement is to import data from CSV file to SharePoint online list from a local drive, every day!
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 to list.
PnP PowerShell to Import CSV file data into SharePoint Online List
Let's import CSV to SharePoint Online list with PnP PowerShell:
This PowerShell adds new items from the given CSV file to the SharePoint Online list. If you want to update the SharePoint Online list from CSV file, use: PowerShell to Update SharePoint Online List from CSV
Bulk Add List Items to SharePoint Online from a CSV file using PnP PowerShell
The above scripts work fine for simple field types such as single lines of text, choice, etc., What if you have complex field types such as Lookup, People Picker, etc? Here is my "Projects" list with almost all available field types in SharePoint Online:
List structure:
Here is the updated PowerShell script to bulk import list items from a 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 to list.
#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="[email protected]" $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 the SharePoint Online list using PowerShell.
PnP PowerShell to Import CSV file data into SharePoint Online List
Let's import CSV to SharePoint Online list with PnP PowerShell:
#Parameters $SiteUrl = "https://crescent.sharepoint.com/sites/marketing" $ListName = "Contacts" $CSVPath = "C:\temp\Contacts.csv" #Get he CSV file contents $CSVData = Import-CsV -Path $CSVPath #Connect to site Connect-PnPOnline $SiteUrl -UseWebLogin #Iterate through each Row in the CSV and import data to SharePoint Online List ForEach ($Row in $CSVData) { Write-Host "Adding Contact $($Row.FirstName)" #Add List Items - Map with Internal Names of the Fields! Add-PnPListItem -List $ListName -Values @{"FirstName" = $($Row.FirstName); "LastName" = $($Row.LastName); "Department"=$($Row.Department); "Mobile" = $($Row.Mobile); }; }Here is my CSV file: CSV Template to Import to SharePoint Online List, and the imported data in list:
This PowerShell adds new items from the given CSV file to the SharePoint Online list. If you want to update the SharePoint Online list from CSV file, use: PowerShell to Update SharePoint Online List from CSV
Bulk Add List Items to SharePoint Online from a CSV file using PnP PowerShell
The above scripts work fine for simple field types such as single lines of text, choice, etc., What if you have complex field types such as Lookup, People Picker, etc? Here is my "Projects" list with almost all available field types in SharePoint Online:
List structure:
Here is the updated PowerShell script to bulk import list items from a CSV.
#Config Variables $SiteURL = "https://crescent.sharepoint.com/sites/projects" $ListName = "Projects" $CSVFilePath = "C:\Temp\ProjectData.csv" #Function to get Lookup ID from Lookup Value Function Get-LookupID($ListName, $LookupFieldName, $LookupValue) { #Get Parent Lookup List and Field from Child Lookup Field's Schema XML $LookupField = Get-PnPField -List $ListName -Identity $LookupFieldName [Xml]$Schema = $LookupField.SchemaXml $ParentListID = $Schema.Field.Attributes["List"].'#text' $ParentField = $Schema.field.Attributes["ShowField"].'#text' $ParentLookupItem = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1 If($ParentLookupItem -ne $Null) { Return $ParentLookupItem["ID"] } Else { Return $Null } } Try { #Connect to the Site Connect-PnPOnline -URL $SiteURL -UseWebLogin #Get the data from CSV file $CSVData = Import-CSV $CSVFilePath #Get the List to Add Items $List = Get-PnPList -Identity $ListName #Get fields to Update from the List - Skip Read only, hidden fields, content type and attachments $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne "ContentType") -and ($_.InternalName -ne "Attachments") } #Loop through each Row in the CSV file and update the matching list item ID ForEach($Row in $CSVData) { #Frame the List Item to update $ItemValue = @{} $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name #Map each field from CSV to target list Foreach($CSVField in $CSVFields) { $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField} If($MappedField -ne $Null) { $FieldName = $MappedField.InternalName #Check if the Field value is not Null If($Row.$CSVField -ne $Null) { #Handle Special Fields $FieldType = $MappedField.TypeAsString If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field { $PeoplePickerValues = $Row.$FieldName.Split(",") $ItemValue.add($FieldName,$PeoplePickerValues) } ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field { $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ } $ItemValue.Add($FieldName,$LookupIDs) } Else { #Get Source Field Value and add to Hashtable $ItemValue.Add($FieldName,$Row.$FieldName) } } } } Write-host "Adding List item with values:" $ItemValue | Format-Table #Add New List Item Add-PnPListItem -List $ListName -Values $ItemValue | Out-Null } } Catch { write-host "Error: $($_.Exception.Message)" -foregroundcolor Red }Make sure the data in the CSV is in the right format and CSV file has list field internal names as its columns! You can download the CSV file from Bulk Import CSV to SharePoint Online List
Great Script - I am just learning and I was able to make it work! Thank you!
ReplyDeleteI am getting this error Exception calling
ReplyDelete"ExecuteQuery" with "0" argument(s): "Column 'SamAccount' does not
exist. It may have been deleted by another user.
Change the $UserName and $Password Variable values to yours!
DeleteGot it to work! Had an incorrect Field - Thank you Great Script :)
DeleteHello, great. I use SharePoint CSOM 16 not 15. I changed the path, but I become:
ReplyDeleteThe 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
Hi Sir,
ReplyDeleteI'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
That's because, "ID" column (or any other read-only columns) can't be updated!
DeleteHow do i import user field values?
ReplyDeleteUse: $Item["UserName"] = $Context.Web.EnsureUser($row.UserName)
DeleteCan we verify if the CSV field has valid entries before import?
ReplyDeleteSure, If(![string]::IsNullOrEmpty($row.FieldName))
Delete{
$Item["FieldName"] = $row.FieldName
}
My CSV has a column "Name of the Customer", How should I code it?
ReplyDeleteHello,
ReplyDeleteThanks 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?
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
DeleteThanks Yr.. You just saved my precious time..Blessings from India..
ReplyDeletehi, 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:
ReplyDeletefirstname 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!
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.”
ReplyDeleteCan you try adding this line in your HOST file?
Delete127.0.0.1 msoid. # To fix PnP connect problem
Exception calling "ExecuteQuery" with "0" argument(s): "For security reasons DTD is prohibited in this XML document. To enable
ReplyDeleteDTD 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
I would like to import data from CSV to the SharePoint Online list. However, my list does have a LookUp field. I was not able to import data since I can't just import data to sharepoint list with lookup field. Can you give a working sample on how you can insert data to the list with LookUp column? Thanks!
ReplyDeleteHere you go: PowerShell to Update Lookup Field Value in SharePoint Online
DeleteHi Rajack, I am facing below error.
ReplyDeleteException calling "ExecuteQuery" with "0" argument(s): "Invalid data has been used to update
the list item. The field you are trying to update may be read only."
At line:43 char:5
+ $Context.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ServerException