Migrating data between SharePoint environments—especially from on-premises to SharePoint Online—can often require repetitive, error-prone manual steps.
PowerShell automation provides a reliable and efficient way to move, transform, or clean up list data.
This article explores a PowerShell script designed to migrate list items from an on-premises SharePoint site to a SharePoint Online list, while intelligently checking for duplicates before copying.
Background
Many organizations transitioning to Microsoft 365 still maintain legacy SharePoint Server (on-premises) environments.
As part of their modernization journey, they need to migrate lists, libraries, and site content to the cloud.
Simple migration scripts often copy all items from one list to another, leading to duplicates or redundant entries.
This script improves on that process by introducing a duplicate detection mechanism that compares specific fields—First Name, Last Name, Email, and Phone Number—before moving an item.
Objective
The goal of this script is to:
Read items from a SharePoint on-premises list.
Check whether the same item already exists in a SharePoint Online list.
If the item exists → delete it from the source only (no copy).
If the item does not exist → copy it to the destination, then delete it from the source.
Technologies Used
PowerShell – for scripting and automation.
SharePoint Server Object Model – to access on-premises list items using Get-SPWeb.
PnP PowerShell – to connect to SharePoint Online using Connect-PnPOnline and perform operations via the Client-Side Object Model (CSOM).
Script Overview (with Example)
Below is a complete PowerShell example with dummy values for URLs, list names, and credentials.
# ============================================
# SHAREPOINT MIGRATION SCRIPT (WITH DUPLICATE CHECK)
# ============================================
# --- SOURCE SITE (ON-PREMISES) ---
$sourceWebURL = "https://sp2019.contoso.local/sites/Finance"
$sourceListName = "ExpenseClaims"
$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]
$spSourceItems = $spSourceList.GetItems()
# --- DESTINATION SITE (SHAREPOINT ONLINE) ---
$SiteURL = "https://contoso.sharepoint.com/sites/FinancePortal"
$ListName = "ExpenseClaims_Archive"
# Connect to SharePoint Online using App Registration
$AppId = "00000000-aaaa-bbbb-cccc-111111111111"
$AppSecret = "xyz123fakeappsecret987654321"
Connect-PnPOnline -Url $SiteURL -AppId $AppId -AppSecret $AppSecret
Get-PnPContext
# --- PROCESS EACH SOURCE ITEM ---
foreach ($spSourceItem in $spSourceItems) {
$firstName = $spSourceItem['First_x0020_Name']
$lastName = $spSourceItem['Last_x0020_Name']
$email = $spSourceItem['Email']
$phone = $spSourceItem['Phone_x0020_Number']
# --- CHECK IF ITEM ALREADY EXISTS IN DESTINATION ---
$existingItem = Get-PnPListItem -List $ListName -PageSize 2000 -Query @"
<View>
<Query>
<Where>
<And>
<And>
<Eq>
<FieldRef Name='First_x0020_Name' />
<Value Type='Text'>$firstName</Value>
</Eq>
<Eq>
<FieldRef Name='Last_x0020_Name' />
<Value Type='Text'>$lastName</Value>
</Eq>
</And>
<And>
<Eq>
<FieldRef Name='Email' />
<Value Type='Text'>$email</Value>
</Eq>
<Eq>
<FieldRef Name='Phone_x0020_Number' />
<Value Type='Text'>$phone</Value>
</Eq>
</And>
</And>
</Where>
</Query>
</View>
"@
if ($existingItem.Count -gt 0) {
Write-Host "Item already exists for $firstName $lastName ($email) — deleting from source only." -ForegroundColor Yellow
$spSourceItem.Delete()
continue
}
# --- COPY TO DESTINATION ---
Add-PnPListItem -List $ListName -Values @{
"Title" = $firstName
"LastName" = $lastName
"Email" = $email
"Phone" = $phone
"Comments" = $spSourceItem['Comments']
"MeetingDate" = $spSourceItem['Meeting_x0020_Date']
"Region" = $spSourceItem['Region']
"Processed" = $spSourceItem['Status']
}
Write-Host "Copied new item for $firstName $lastName ($email)." -ForegroundColor Green
# --- DELETE SOURCE ITEM AFTER COPY ---
$spSourceItem.Delete()
Write-Host "Deleted source item for $firstName $lastName ($email)." -ForegroundColor Cyan
}
Write-Host "Migration completed successfully!" -ForegroundColor White -BackgroundColor DarkGreen
How the Script Works
Connects to the source SharePoint site
Uses the on-premises Server Object Model (Get-SPWeb) to retrieve all items from the list.
Connects to SharePoint Online
Uses PnP PowerShell with App ID and App Secret credentials for secure connection.
Loops through each source item
Reads item details such as First Name, Last Name, Email, and Phone.
Performs a duplicate check
Executes a CAML query on the destination list to see if an identical record already exists.
Conditional behavior
If a match is found → delete from source (no copy).
If no match → copy to destination, then delete from source.
Logs actions
Uses Write-Host for color-coded console output showing progress and results.
Why This Script Is Useful
Prevents duplicate data in SharePoint Online during migration.
Automatically removes old data from the on-premises list.
Can be safely rerun without creating redundant entries.
Ensures data integrity and simplifies cloud migration workflows.
Important Note
This script permanently deletes items from the source list after migration.
Always test in a development or staging environment first.
You can comment out the $spSourceItem.Delete() lines to perform a dry run (no deletions).
Possible Enhancements
Handle attachments and lookup fields.
Export detailed migration logs to CSV.
Filter by date to migrate only recent items.
Add a “Test Mode” switch to simulate without making changes.
Conclusion
This PowerShell automation provides an efficient and safe way to migrate SharePoint list data.
By adding a smart duplicate check and automated cleanup, it ensures that your SharePoint Online environment remains accurate and organized during the migration process.