Compare Sharepoint List A contents to Sharepoint List B and write the difference to List B

Question

I am newbie in Powershell scripting, I'd like to ask if you can help me do a script.

The objective is to compare Sharepoint List A that has, say 100 records with attachments versus another Sharepoint List B that has 50 records.

I need to update List B with the difference including attachments from List A records.

$SiteURL = "projects.crescent.com" 
Connect-PnPOnline -url $SiteURL –Credentials (Get-Credential) 

$ListOneName = "T1" 
$ListTwoName = "T2" 

#Get site and List objects 
$ColumnToCompare = "Change ID #" 

#$Web = Get-SPWeb $SiteURL 
$ListOne = $web.lists[$ListOneName] 
$ListTwo = $web.lists[$ListTwoName] 

#Fetch specific Column Values from each list to compare 
$ListOneValues = @() 
$ListTwoValues = @() 

$ListOne.Items | 
foreach { $ListOneValues+= $_[$ColumnToCompare] } 

$ListTwo.Items | 
foreach { $ListTwoValues+= $_[$ColumnToCompare] } 

Compare-Object $ListOneValues $ListTwoValues # -PassThru

SO has rules, we are to follow: [Provide MRE](https://stackoverflow.com/help/minimal-reproducible-example) --- [How to ask](https://stackoverflow.com/help/how-to-ask) --- [Don't ask](https://stackoverflow.com/help/dont-ask) --- [Proper Topic](https://stackoverflow.com/help/on-topic) --- [Why not upload images of code/errors?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question). See the help files for the SharePoint cmdlets, and Get-ChildItem, and Compare-Object. Do a search for `Powershell compare SharePoint Lists`. — postanote, Jan 25 '21 at 03:13
Being new is fine, but this means you need to go get some training. Youtube is your friend. Search for 'Beginning PowerShell' or intermediate, or advanced, or PowerShell SharePoint list. Never run code you do not understand, and never run destructive code (add/new, rename/move, update/delete, etc.), without checking yourself first. See the help files for WhatIf and Confirm. If you do not, you could/can cause major damage to your environment/files. — postanote, Jan 25 '21 at 03:19
thank you for the reply. I am not totally new since I can do old school programming and now can do powerapps thing but find it limited on this requirement. I found some script from internet but still unable to run them to get to what i wanted to achieve. hope you can help. — Jaime Tolentino, Jan 25 '21 at 03:32
$SiteURL = "http://projects.crescent.com" Connect-PnPOnline -url $SiteURL –Credentials (Get-Credential) $ListOneName ="T1" $ListTwoName ="T2" $ColumnToCompare="Change ID #" #Get site and List objects #$Web = Get-SPWeb $SiteURL $ListOne = $web.lists[$ListOneName] $ListTwo = $web.lists[$ListTwoName] #Fetch specifc Column Values from each lists to compare $ListOneValues = @() $ListTwoValues = @() $ListOne.Items| foreach { $ListOneValues+= $_[$ColumnToCompare] } $ListTwo.Items | foreach { $ListTwoValues+= $_[$ColumnToCompare] } Compare-Object $ListOneValues $ListTwoValues # -PassThru — Jaime Tolentino, Jan 25 '21 at 03:38
OK, but SharePoint, notwithstanding, the approach is the same, relative to comparing lists on your file system. So, the best case is to practice just using directories on your workstation, then once you get that to work, then use that approach for SharePoint. See the examples for the `Compare-Object` cmdlet. I just edited your original post for you from your comment unchanged but formatted it for readability. — postanote, Jan 25 '21 at 03:44
Have a look at: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026). Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet, it would probably be something like: `$ListOneValues | Join $ListTwoValues -On $ColumnToCompare`. *Side note*: [try to avoid using the increase assignment operator (+=) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponentially exceptive. — iRon, Jan 25 '21 at 08:51

postanote · Answer 1 · 2021-01-25T06:13:44.043

So, I don't have SP anywhere to do this. However, as stated, doing this using the file system, could roughly be something like this.

# Get your lists
($InSourcePath      = Get-ChildItem -Path 'D:\Temp\Source')
($InDestinationPath = Get-ChildItem -Path 'D:\Temp\Destination')
# Results
<#
    Directory: D:\Temp\Source


Mode          LastWriteTime Length Name                                                                                          
----          ------------- ------ ----                                                                                          
-a----  29-Dec-19     21:50     69 5 Free Software You'll Wish You Knew Earlier! 2019 - YouTube.url                              
...                                                  




    Directory: D:\Temp\Destination


Mode          LastWriteTime Length Name                                                            
----          ------------- ------ ----                                                            
d-----  14-Mar-20     17:03        here                                                            
...
#>

# File count 
$InSourcePath.Count
$InDestinationPath.Count
# Results
<#
16
4
#>

# Get the root source and destination paths
($SourcePath      = (($InSourcePath      = Get-ChildItem -Path 'D:\Temp\Source' -File)[0]).DirectoryName)
($DestinationPath = (($InDestinationPath = Get-ChildItem -Path 'D:\Temp\Destination' -File)[0]).DirectoryName)
# Results
<#
D:\Temp\Source
D:\Temp\Destination
#>


# Intial compare
Compare-Object -ReferenceObject $InDestinationPath.Name -DifferenceObject $InSourcePath.Name -IncludeEqual
# Results '==', in both source/destination :::'=>', only in Source ::: '<=', only in destination
<#
InputObject                                                                                    SideIndicator
-----------                                                                                    -------------
5 Free Software You'll Wish You Knew Earlier! 2019 - YouTube.url                               ==           
abc.txt                                                                                        =>           
...           
here                                                                                           <=           
...          
#>


Compare-Object -ReferenceObject $InDestinationPath.Name -DifferenceObject $InSourcePath.Name -IncludeEqual | 
ForEach-Object {
    If ($PSItem.SideIndicator -eq '=>')
    {
        Move-Item -Path "$SourcePath\$($PSitem.InputObject)" -Destination $DestinationPath -WhatIf
    }
} 
# Results
<#
What if: Performing the operation "Move File" on target "Item: D:\Temp\Source\abc.txt Destination: D:\Temp\Destination\abc.txt".
...
#>

Compare Sharepoint List A contents to Sharepoint List B and write the difference to List B

1 Answers1