SharePoint Online: Import CSV File into SharePoint List using PowerShell
Scenario: My requirement is to import data from a CSV file to the SharePoint Online list from a local drive every day!
Do you have a CSV file that you need to import into a SharePoint Online list? Well, PowerShell makes it easy! In this post, I’ll show you how to import a CSV file into a SharePoint Online list using PowerShell. You’ll be able to import any type of data into your list, including text, numbers, and dates. Let’s get started!
PowerShell Script to import SharePoint List Items from CSV File:
While it’s possible to copy-paste the data from a CSV file to the SharePoint Online list using the “Quick Edit” view, the PowerShell approach is necessary for automation. Here is the SharePoint Online PowerShell to import CSV to the 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 -Interactive
#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, which you can download:
And the imported data in the 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 -Interactive
#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)
}
ElseIf($FieldType -eq "DateTime")
{
$ItemValue.Add($FieldName,[DateTime]$Row.$FieldName)
}
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 correct format, and the CSV file has list field internal names as its columns! You can download the CSV file from:
How about importing an Excel file (instead of CSV) into SharePoint Online List? Sure, Here you go: Import Excel to SharePoint Online list using PowerShell
I am getting this error Exception calling
“ExecuteQuery” with “0” argument(s): “Column ‘Name’ does not
exist. It may have been deleted by another user.
The files are uploading in same script but column is not updated, I have full access to site
My problem is trying to get a list of people from Excel to a SP list, and have the name of the person resolve. I noticed you have people columns in your bulk example, but none of the names are actually resolving. Will that script still work for this purpose?
Use the last script in the article. Make sure your user names are in [email protected] format, as in the attached CSV template.
Hi there,
Thank you for this script (PowerShell Script to import SharePoint List Items from CSV File):
I have 2 issues though-
1. This is the error I’m receiving.
Exception calling “ExecuteQuery” with “0” argument(s): “String was not recognized as a valid DateTime.”
At C:\Scripts\SCRIPT_upload_to_list.ps1:48 char:2
+ $Context.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ServerException
2. Even when this error keeps appearing for each line in the CSV, the list items are uploaded to the list, however only 918 are added out of 4206.
What can cause this? Does the above error relate to this?
Thanks.
If you have a DateTime field, Try this format: $Item[$FieldName] = [DateTime]”10/21/1984″
Hello! I’m running into the same issue as the previous user with my dates – they’re either 1 day off or it will error if there’s no content in them. I see your instruction about the datetime addition, but not sure where to put it in the script. Could you hlep?
I second this. Not sure where to put this DateTime field into the script. Any additional advice would be greatly appreciated.
Added an ElseIf condition to the last script in this article!
Thank you so much. It’s so close to working. I get the error “Error: Cannot convert value “30/06/2021” to type “System.DateTime”. Error: “String was not recognized as a valid DateTime.” For reference I have Date only turned on and display format as friendly for the sharepoint list column. I initially thought it was because the display format was showing 21 October that it wasnt working. I have since changed that to standard so it displays in the list Exactly as dd/mm/yyyy but I still get the error. I’ve tried messing around with DateTime::ParseExact to force it to use a specific date but cant get it to work either. Any help is appreciated as always.
Thanks!
Thanks again!!!
Elseif statement didnt work initially due to me not using the appropriate DateTime in my csv file. I had to alter the date time to include hours, minutes and seconds like so 10/21/2022 20:00:14 . Once this was amended it accepted the date time and created the item in the list.
Also in case this helps others I think it is worth connecting to the sharepoint list via the command Connect-PnPOnline -URL $SiteURL -Interactive and running Get-PnPList -Identity $ListName . This will show you the internal names of the list columns you want to use as your csv column titles. I had incorrectly just being use the titles that appear in the sharepoint list not realising the script references internal names that can be sometimes spelt differently especially if they contain spaces or were spelt incorrectly when first setting up the sharepoint list.
Also if you have a yes or no column you will need to put true or false as the data otherwise it wont come across. Mine initially wouldnt transfer over and it took me a while to realise because it is a boolean fieldtype so doesnt know what to do with yes or no.
Thanks again for your help Salaudeen. This script worked perfectly and will be used again in future. Much appreciated.
What if I had an item that doesn’t exist in the list, how can I add it, without importing the items that already exist?
This script only imports the items from the given CSV – doesn’t check if the item already exists or not. If you want to update existing list items from CSV, refer: Update SharePoint Online List from CSV using PowerShell
How can I combine the two scripts you have so that I have a column which states the type of operation. For example first column named “operation” says “add” then add this entry, “modify” then update entry and “delete” delete this entry if found? Can you please help with that requirement?
Instead of Import, You can update list items from a CSV file. Refer:PowerShell Script to update List Items in SharePoint Online
Hi Rajack, I am facing below error.
Exception 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
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!
Here you go: PowerShell to Update Lookup Field Value in SharePoint Online
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:UsersxxxxxxxxUpload.ps1:32 char:5
+ $Context.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : XmlException
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.”
Can you try adding this line in your HOST file?
127.0.0.1 msoid. # To fix PnP connect problem
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!
Thanks Yr.. You just saved my precious time..Blessings from India..
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?
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
My CSV has a column “Name of the Customer”, How should I code it?
Can we verify if the CSV field has valid entries before import?
Sure, If(![string]::IsNullOrEmpty($row.FieldName))
{
$Item[“FieldName”] = $row.FieldName
}
How do i import user field values?
Use: $Item[“UserName”] = $Context.Web.EnsureUser($row.UserName)
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
That’s because, “ID” column (or any other read-only columns) can’t be updated!
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 FilesWindowsPowerShellModulesPester3.4.0FunctionsDescribe.ps1:125 Zeichen:9
thank you
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.
Change the $UserName and $Password Variable values to yours!
Got it to work! Had an incorrect Field – Thank you Great Script 🙂
Great Script – I am just learning and I was able to make it work! Thank you!