Update SharePoint List Items from a CSV with PowerShell
Export your SharePoint list, make some modifications to the cells in the Excel table, then push it back with a script, and all rows return success. But there are some changes that do not get applied as expected, and out of those there are two cases that are silently ignored.
Tested on a PnP.PowerShell 2.12.0 with an actual list..
A CSV has no types
That is the whole problem in one sentence. Export a list and read it back:
$row = Import-Csv .\projects.csv | Select-Object -First 1
foreach ($p in $row.PSObject.Properties) {
"{0,-10} {1,-18} value: {2}" -f $p.Name, $p.Value.GetType().Name, $p.Value
}Id String value: 1
Title String value: Project 01
Status String value: Not Started
DueDate String value: 7/28/2026 5:13:57 PM
Budget String value: 1000
Notes String value: Seeded row 1 for PowerShell examples.Everything is a String. The list on the other side has Choice, DateTime, Currency and Note columns. Handing one to the other is where it falls apart.
The version everybody writes first
$rows = Import-Csv .\changes.csv
# Build the lookup ONCE. Searching the list per row is the usual reason
# these scripts take hours instead of seconds.
$lookup = @{}
Get-PnPListItem -List 'Projects' -PageSize 500 | ForEach-Object {
$lookup[[string]$_['Title']] = $_.Id
}
foreach ($row in $rows) {
$id = $lookup[$row.Title]
if (-not $id) { Write-Host "no match for '$($row.Title)'"; continue }
Set-PnPListItem -List 'Projects' -Identity $id -Values @{
ProjectStatus = $row.Status
DueDate = $row.DueDate
Budget = $row.Budget
}
}I fed it seven rows, four good and three broken on purpose:
updated Project 01
updated Project 02
updated Project 03
updated Project 04
FAILED Project 05: The string was not recognized as a valid DateTime.
no match for 'Project 99'
updated Project 06Looks like it mostly worked. It did not.
Failure one: an invalid Choice value is written anyway
Row four set the status to On Hold. That is not one of the four choices on that column. Look at what happened:
Project 04 status is now: 'On Hold'
Valid choices are:
Not Started
In Progress
Blocked
CompleteSharePoint doesn’t verify the value when writing to the Choice column. It happily accepted a value that was never available, and recorded it without saying anything. And it returned success.
This value is not available via any drop-down list in the user interface. It will simply disappear from your filtered views and from your Power Automate actions. Do this to 2,000 rows and you will realize it after a few months.
It’s up to you to discover
$field = Get-PnPField -List 'Projects' -Identity 'ProjectStatus'
if ($value -notin @($field.Choices)) {
# reject it - SharePoint will not
}Failure two: an empty cell deletes data
Row six had an empty Budget. Not zero, empty. Afterwards:
Title Status DueDate Budget
----- ------ ------- ------
Project 06 Complete 7/1/2026 12:00:00 AMThe Budget is gone. It was 6000 before.
The absence of anything in an individual cell means that it is marked “clear this field,” rather than being left blank. This makes sense, although the exact opposite would have been true had you intended what most people intend to accomplish when they send a partially populated spreadsheet to someone else. Any empty cell in your CSV file represents a deletion..
Both behaviours are reasonable, so decide which one you want rather than finding out.
Failure three: one bad cell loses the whole row
Row five had not a date in the DueDate column, and it threw. That part is fine, loud is good.
The problem is that neither Status nor Budget was entered on that row. The data entry process requires just one piece of bad information to render everything else useless. In Project 05, the old status was retained, along with its old date and budget.
Validate first and keep the good data
[5] Project 05: ProjectStatus=Complete, Budget=5500DueDate is skipped and reported, the other two go through.
The whole script
Reads the list’s real schema, validates every value against its actual field type, batches the writes, and supports -WhatIf:
[CmdletBinding(SupportsShouldProcess)]
param(
[Parameter(Mandatory)] [string] $SiteUrl,
[Parameter(Mandatory)] [string] $ClientId,
[Parameter(Mandatory)] [string] $List,
[Parameter(Mandatory)] [string] $CsvPath,
[string] $KeyColumn = 'Title',
[hashtable] $ColumnMap = @{},
[switch] $ClearEmptyValues
)
$ErrorActionPreference = 'Stop'
if (-not (Test-Path $CsvPath)) { throw "CSV not found: $CsvPath" }
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientId -Interactive
$rows = @(Import-Csv -Path $CsvPath)
if ($rows.Count -eq 0) { throw "CSV is empty." }
Write-Host "Rows in CSV : $($rows.Count)" -ForegroundColor Cyan
# Read the real field types rather than assuming the CSV headers mean anything.
$fields = @{}
foreach ($f in Get-PnPField -List $List) { $fields[$f.InternalName] = $f }
$csvColumns = $rows[0].PSObject.Properties.Name | Where-Object { $_ -ne $KeyColumn }
$targets = @{}
foreach ($col in $csvColumns) {
$internal = if ($ColumnMap.ContainsKey($col)) { $ColumnMap[$col] } else { $col }
if (-not $fields.ContainsKey($internal)) {
Write-Warning "CSV column '$col' maps to '$internal', which is not a field on '$List'. Ignored."
continue
}
$targets[$col] = $fields[$internal]
}
if ($targets.Count -eq 0) { throw "No CSV columns matched fields on '$List'." }
Write-Host "Mapped columns:" -ForegroundColor Cyan
foreach ($col in $targets.Keys | Sort-Object) {
" {0,-14} -> {1,-18} ({2})" -f $col, $targets[$col].InternalName, $targets[$col].TypeAsString
}
# One pass to index the list. Searching per row is what makes these slow.
Write-Host "`nBuilding lookup..." -ForegroundColor Cyan
$lookup = @{}
$duplicates = [System.Collections.Generic.List[string]]::new()
foreach ($item in Get-PnPListItem -List $List -PageSize 500) {
$key = [string]$item[$KeyColumn]
if ([string]::IsNullOrWhiteSpace($key)) { continue }
if ($lookup.ContainsKey($key)) { $duplicates.Add($key); continue }
$lookup[$key] = $item.Id
}
Write-Host " $($lookup.Count) item(s) indexed on '$KeyColumn'"
if ($duplicates.Count) {
Write-Warning "$($duplicates.Count) duplicate key(s) - only the first of each was indexed."
}
function Convert-Value {
param($Raw, $Field)
$type = $Field.TypeAsString
if ([string]::IsNullOrWhiteSpace($Raw)) {
if ($ClearEmptyValues) { return @{ Ok = $true; Value = $null } }
return @{ Ok = $false; Reason = 'empty (skipped)' }
}
switch -Regex ($type) {
'^Choice$|^MultiChoice$' {
# The check SharePoint does not do for you.
$valid = @($Field.Choices)
if ($Raw -notin $valid) {
return @{ Ok = $false; Reason = "'$Raw' is not a valid choice (allowed: $($valid -join ', '))" }
}
return @{ Ok = $true; Value = $Raw }
}
'^DateTime$' {
$parsed = [datetime]::MinValue
if (-not [datetime]::TryParse($Raw, [ref]$parsed)) {
return @{ Ok = $false; Reason = "'$Raw' is not a date" }
}
return @{ Ok = $true; Value = $parsed }
}
'^Number$|^Currency$' {
$parsed = 0.0
if (-not [double]::TryParse($Raw, [ref]$parsed)) {
return @{ Ok = $false; Reason = "'$Raw' is not a number" }
}
return @{ Ok = $true; Value = $parsed }
}
'^Boolean$' {
if ($Raw -in @('1','true','True','yes','Yes')) { return @{ Ok = $true; Value = $true } }
if ($Raw -in @('0','false','False','no','No')) { return @{ Ok = $true; Value = $false } }
return @{ Ok = $false; Reason = "'$Raw' is not a yes/no value" }
}
default { return @{ Ok = $true; Value = $Raw } }
}
}
$plan = [System.Collections.Generic.List[object]]::new()
$problems = [System.Collections.Generic.List[object]]::new()
foreach ($row in $rows) {
$key = [string]$row.$KeyColumn
if (-not $lookup.ContainsKey($key)) {
$problems.Add([pscustomobject]@{ Key = $key; Column = '(row)'; Reason = 'no matching item' })
continue
}
$values = @{}
foreach ($col in $targets.Keys) {
$result = Convert-Value $row.$col $targets[$col]
if ($result.Ok) {
$values[$targets[$col].InternalName] = $result.Value
} elseif ($result.Reason -ne 'empty (skipped)') {
$problems.Add([pscustomobject]@{ Key = $key; Column = $col; Reason = $result.Reason })
}
}
if ($values.Count) {
$plan.Add([pscustomobject]@{ Key = $key; Id = $lookup[$key]; Values = $values })
}
}
Write-Host "`nTo update : $($plan.Count) item(s)" -ForegroundColor Green
Write-Host "Problems : $($problems.Count)"
if ($problems.Count) { $problems | Format-Table Key, Column, Reason -AutoSize }
if ($plan.Count -eq 0) { Write-Host "Nothing to do."; Disconnect-PnPOnline; return }
if ($WhatIfPreference) {
Write-Host "`n-WhatIf: nothing was written. Planned changes:" -ForegroundColor Cyan
foreach ($p in $plan | Select-Object -First 10) {
" [$($p.Id)] $($p.Key): " + (($p.Values.GetEnumerator() | ForEach-Object { "$($_.Key)=$($_.Value)" }) -join ', ')
}
if ($plan.Count -gt 10) { " ... and $($plan.Count - 10) more" }
Disconnect-PnPOnline
return
}
if ($PSCmdlet.ShouldProcess("$($plan.Count) items in '$List'", 'Update')) {
$sw = [System.Diagnostics.Stopwatch]::StartNew()
$batch = New-PnPBatch
foreach ($p in $plan) {
Set-PnPListItem -List $List -Identity $p.Id -Values $p.Values -Batch $batch | Out-Null
}
Invoke-PnPBatch -Batch $batch
$sw.Stop()
Write-Host "`nUpdated $($plan.Count) item(s) in $([math]::Round($sw.Elapsed.TotalSeconds,1))s" -ForegroundColor Green
}
Disconnect-PnPOnlineRun it dry first
.\Update-ListFromCsv.ps1 `
-SiteUrl "https://contoso.sharepoint.com/sites/Demo" `
-ClientId "11111111-2222-3333-4444-555555555555" `
-List "Projects" -CsvPath .\changes.csv `
-ColumnMap @{ Status = 'ProjectStatus' } `
-WhatIfMapped columns:
Budget -> Budget (Currency)
DueDate -> DueDate (DateTime)
Status -> ProjectStatus (Choice)
Building lookup...
25 item(s) indexed on 'Title'
To update : 6 item(s)
Problems : 3
Key Column Reason
--- ------ ------
Project 04 Status 'On Hold' is not a valid choice (allowed: Not Started, In Progress, Blocked, Complete)
Project 05 DueDate 'not a date' is not a date
Project 99 (row) no matching item
-WhatIf: nothing was written. Planned changes:
[4] Project 04: Budget=4400, DueDate=09/01/2026 00:00:00
[5] Project 05: ProjectStatus=Complete, Budget=5500Observe rows 4 and 5. The faulty cell gets dropped and the result reported while the other cells get processed. The naive approach discarded everything in row 5. Now run the test version
Updated 6 item(s) in 4.5sIt’s the -WhatIf run that counts. Thirty seconds spent reading the list of problems is better than getting to find out after six months that the choice column has a lot of invalid values.
