In this post, I am going to show you how to update SharePoint list items from a CSV, and the three ways it goes wrong – two of which happen silently. Full script at the end.
Run against a real list on PnP.PowerShell 2.12.0.
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 does not validate Choice values on write. It took a value that was never an option, stored it, and said nothing. It reported success.
The column now holds something no dropdown in the interface can produce. Your filtered views miss it, your Power Automate conditions do not match it, and nothing anywhere flags it. Do this to two thousand rows and you will find out months later.
You have to check it yourself:
$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.
An empty cell is treated as “clear this field”, not “leave this alone”. Which is defensible, but it is the opposite of what most people mean when they send a spreadsheet with a few columns filled in. If your CSV only carries the columns you meant to change, every blank cell in it is 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.
What is not fine is that Status and Budget on that row were not written either. The update is one call per item, so a single unusable value throws all of it away. Project 05 kept its old status, its old date and its old budget.
Validate before sending and the good values survive:
[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=5500Note rows 4 and 5. The bad cell is dropped and reported, the good ones still go through. The naive version threw all of row 5 away.
Then run it for real:
Updated 6 item(s) in 4.5sThe -WhatIf run is the one that matters. Thirty seconds of reading a problem list beats finding out in six months that a Choice column is full of values that were never options 🙂