# Prompt user for source and destination paths
$sourceFolder = Read-Host "Enter the path to the source folder"
$destinationFolder = Read-Host "Enter the path to the destination folder"
# Validate that the paths exist
if (-not (Test-Path -Path $sourceFolder -PathType Container)) {
Write-Host "Error: Source folder does not exist or is not accessible." -ForegroundColor Red
exit
}
if (-not (Test-Path -Path $destinationFolder -PathType Container)) {
Write-Host "Error: Destination folder does not exist or is not accessible." -ForegroundColor Red
exit
}
# Prompt user for path mapping options
Write-Host "`nPath Mapping Options:" -ForegroundColor Cyan
Write-Host "1: Use relative paths (recommended for folder structures that match)"
Write-Host "2: Specify custom root paths to replace in the report"
$pathMappingOption = Read-Host "Select path mapping option (1 or 2)"
$sourceRootPath = $sourceFolder
$destinationRootPath = $destinationFolder
if ($pathMappingOption -eq "2") {
Write-Host "`nYou've selected to use custom path mapping." -ForegroundColor Cyan
Write-Host "This will help match files with similar structures but different root paths." -ForegroundColor Cyan
Write-Host "Example: Source path: 'E:\Company_Data\Djarragun Enterprises\ZZ. 2. Finance'" -ForegroundColor Yellow
Write-Host " Destination path: 'G:\OneDrive\Bama Services\Finance Documents - Documents\2. Finance'" -ForegroundColor Yellow
Write-Host "`nThis allows matching 'ZZ. 2. Finance\2.0 Archive\file.txt' with '2. Finance\2.0 Archive\file.txt'" -ForegroundColor Yellow
$sourceRootPath = Read-Host "Enter the source root path to be replaced (e.g., E:\Company_Data\Djarragun Enterprises\ZZ. 2. Finance)"
$destinationRootPath = Read-Host "Enter the destination root path to be replaced (e.g., G:\OneDrive\Bama Services\Finance Documents - Documents\2. Finance)"
}
# Prompt user for thread count
$maxThreads = Read-Host "Enter the number of parallel threads to use (recommended: number of CPU cores, default: 2)"
if ([string]::IsNullOrWhiteSpace($maxThreads) -or -not [int]::TryParse($maxThreads, [ref]$null)) {
$maxThreads = 2
} else {
$maxThreads = [int]$maxThreads
}
Write-Host "Using $maxThreads parallel threads for file processing." -ForegroundColor Cyan
# Prompt user for report output location
$outputBaseLocation = Read-Host "Enter the folder path where you want to save the reports (press Enter to use the current directory)"
# If user didn't specify a location, use the current directory
if ([string]::IsNullOrWhiteSpace($outputBaseLocation)) {
$outputBaseLocation = Get-Location
}
# Validate that the output path exists
elseif (-not (Test-Path -Path $outputBaseLocation -PathType Container)) {
Write-Host "Warning: Output folder does not exist. Attempting to create it..." -ForegroundColor Yellow
try {
New-Item -Path $outputBaseLocation -ItemType Directory -Force | Out-Null
Write-Host "Output folder created successfully." -ForegroundColor Green
}
catch {
Write-Host "Error: Could not create output folder. Using current directory instead." -ForegroundColor Red
$outputBaseLocation = Get-Location
}
}
# Overall progress tracking
$overallStartTime = Get-Date
$totalOverallSteps = 5 # 1. Get files, 2. Process source, 3. Process dest, 4. Compare files, 5. Generate report
$currentOverallStep = 0
# Create a synchronized hashtable for thread-safe access to shared variables
$sync = [System.Collections.Hashtable]::Synchronized(@{
ProcessedFiles = 0
TotalFiles = 0
CurrentFile = ""
Errors = 0
ElapsedTime = [TimeSpan]::Zero
FilesPerSecond = 0
EstimatedRemaining = [TimeSpan]::Zero
})
function Update-OverallProgress {
param (
[string]$Activity,
[int]$StepNumber
)
$percent = [Math]::Round(($StepNumber / $totalOverallSteps) * 100, 0)
$elapsedTime = (Get-Date) - $overallStartTime
$formattedElapsedTime = Format-ElapsedTime -TimeSpan $elapsedTime
Write-Progress -Id 0 -Activity "File Migration Validation" -Status "$Activity ($percent% Complete)" `
-PercentComplete $percent `
-CurrentOperation "Overall Progress: Step $StepNumber of $totalOverallSteps | Elapsed: $formattedElapsedTime"
}
# Helper function to format time span nicely
function Format-ElapsedTime {
param (
[TimeSpan]$TimeSpan
)
if ($TimeSpan.TotalHours -ge 1) {
return "{0:d2}h:{1:d2}m:{2:d2}s" -f $TimeSpan.Hours, $TimeSpan.Minutes, $TimeSpan.Seconds
}
elseif ($TimeSpan.TotalMinutes -ge 1) {
return "{0:d2}m:{1:d2}s" -f $TimeSpan.Minutes, $TimeSpan.Seconds
}
else {
return "{0:d2}s" -f $TimeSpan.Seconds
}
}
# Function to get the relative path
function Get-RelativePath {
param (
[string]$FullPath,
[string]$BasePath
)
if ($FullPath.StartsWith($BasePath, [StringComparison]::OrdinalIgnoreCase)) {
$relativePath = $FullPath.Substring($BasePath.Length)
if ($relativePath.StartsWith('\')) {
$relativePath = $relativePath.Substring(1)
}
return $relativePath
}
return $FullPath
}
# Function to update progress for multi-threaded operations
function Update-MultiThreadProgress {
param (
[string]$Activity,
[int]$Id = 1
)
$percentComplete = 0
if ($sync.TotalFiles -gt 0) {
$percentComplete = [Math]::Min(100, [Math]::Round(($sync.ProcessedFiles / $sync.TotalFiles) * 100, 0))
}
$currentOperation = "Processed: $($sync.ProcessedFiles) of $($sync.TotalFiles) | " +
"Elapsed: $(Format-ElapsedTime -TimeSpan $sync.ElapsedTime) | " +
"Remaining: $(Format-ElapsedTime -TimeSpan $sync.EstimatedRemaining) | " +
"Speed: $([Math]::Round($sync.FilesPerSecond, 1)) files/sec"
Write-Progress -Id $Id -Activity $Activity -Status "Processing $percentComplete%" `
-PercentComplete $percentComplete -CurrentOperation $currentOperation
}
# Initialize runspace pool for multi-threading
function Initialize-RunspacePool {
param(
[int]$MaxThreads
)
# Create a session state for the runspace pool
$sessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
# Create the runspace pool
$runspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $sessionState, $Host)
$runspacePool.Open()
return $runspacePool
}
# Function to process files in parallel
function Process-FilesInParallel {
param(
[array]$Files,
[string]$BasePath,
[string]$Activity,
[System.Management.Automation.Runspaces.RunspacePool]$RunspacePool
)
# Reset counters in the synchronized hashtable
$sync.ProcessedFiles = 0
$sync.TotalFiles = $Files.Count
$sync.Errors = 0
$sync.CurrentFile = ""
# Create a collection to store the runspaces
$runspaces = New-Object System.Collections.ArrayList
# Create a collection to store the file information
$fileCollection = [System.Collections.Concurrent.ConcurrentBag[PSObject]]::new()
# Progress tracking variables
$startTime = Get-Date
$lastUpdateTime = $startTime
$progressUpdateInterval = 1 # Update progress every 1 second
# Create a scriptblock to process each file
$scriptBlock = {
param(
[string]$FilePath,
[string]$BasePath,
[System.Collections.Concurrent.ConcurrentBag[PSObject]]$Collection
)
try {
# Calculate hash
$hash = (Get-FileHash -Path $FilePath -Algorithm SHA256).Hash
# Get file info
$file = Get-Item -Path $FilePath
# Create relative path
$relativePath = if ($FilePath.StartsWith($BasePath, [StringComparison]::OrdinalIgnoreCase)) {
$rel = $FilePath.Substring($BasePath.Length)
if ($rel.StartsWith('\')) {
$rel.Substring(1)
} else {
$rel
}
} else {
$FilePath
}
# Create file object
$fileObject = [PSCustomObject]@{
Name = if ($file.Name) { $file.Name } else { [System.IO.Path]::GetFileName($FilePath) }
BaseName = if ($file.BaseName) { $file.BaseName } else { [System.IO.Path]::GetFileNameWithoutExtension($FilePath) }
FullPath = $file.FullName
RelativePath = $relativePath
LastWriteTime = $file.LastWriteTime
Extension = if ($file.Extension) { $file.Extension } else { [System.IO.Path]::GetExtension($FilePath) }
SizeBytes = $file.Length
SizeKB = [math]::Round($file.Length / 1KB, 2)
SizeMB = [math]::Round($file.Length / 1MB, 2)
SHA256Hash = $hash
}
# Add to collection
$Collection.Add($fileObject)
return $true
}
catch {
# Return the error information
return $_.Exception.Message
}
}
# Start processing files in parallel
foreach ($file in $Files) {
# Create a PowerShell instance and add the script
$powershell = [powershell]::Create().AddScript($scriptBlock).AddArgument($file.FullName).AddArgument($BasePath).AddArgument($fileCollection)
# Set the runspace pool
$powershell.RunspacePool = $RunspacePool
# Begin asynchronous invocation
$handle = $powershell.BeginInvoke()
# Add to the runspaces collection
[void]$runspaces.Add([PSCustomObject]@{
PowerShell = $powershell
Handle = $handle
File = $file.FullName
})
}
# Monitor progress and clean up completed runspaces
do {
# Update current counters
$completed = $runspaces | Where-Object { $_.Handle.IsCompleted }
# Process completed runspaces
foreach ($runspace in $completed) {
# Get the result
$result = $runspace.PowerShell.EndInvoke($runspace.Handle)
# Update processed files counter
$sync.ProcessedFiles++
$sync.CurrentFile = [System.IO.Path]::GetFileName($runspace.File)
# Check for errors
if ($result -ne $true) {
$sync.Errors++
Write-Host "Error processing file $($runspace.File): $result" -ForegroundColor Yellow
}
# Dispose the PowerShell instance
$runspace.PowerShell.Dispose()
# Remove from the collection
[void]$runspaces.Remove($runspace)
}
# Update progress every second
$currentTime = Get-Date
if (($currentTime - $lastUpdateTime).TotalSeconds -ge $progressUpdateInterval) {
$sync.ElapsedTime = $currentTime - $startTime
# Calculate files per second
if ($sync.ProcessedFiles -gt 0 -and $sync.ElapsedTime.TotalSeconds -gt 0) {
$sync.FilesPerSecond = $sync.ProcessedFiles / $sync.ElapsedTime.TotalSeconds
}
# Calculate estimated remaining time
if ($sync.FilesPerSecond -gt 0) {
$remainingFiles = $sync.TotalFiles - $sync.ProcessedFiles
$estimatedRemainingSeconds = $remainingFiles / $sync.FilesPerSecond
$sync.EstimatedRemaining = [TimeSpan]::FromSeconds($estimatedRemainingSeconds)
}
# Update progress display
Update-MultiThreadProgress -Activity $Activity
# Update overall progress
Update-OverallProgress -Activity $Activity -StepNumber $currentOverallStep
$lastUpdateTime = $currentTime
}
# Small sleep to prevent CPU hogging
Start-Sleep -Milliseconds 100
} while ($runspaces.Count -gt 0)
# Ensure progress is set to 100% at the end
$sync.ProcessedFiles = $sync.TotalFiles
Update-MultiThreadProgress -Activity $Activity
# Return the file collection
return $fileCollection
}
# Step 1: Get files
$currentOverallStep++
Update-OverallProgress -Activity "Scanning files" -StepNumber $currentOverallStep
# Get all files from source location
$sourceFiles = Get-ChildItem -Path $sourceFolder -File -Force -Recurse
Write-Host "Found $($sourceFiles.Count) files in source location"
# Get all files from destination location
$destinationFiles = Get-ChildItem -Path $destinationFolder -File -Force -Recurse
Write-Host "Found $($destinationFiles.Count) files in destination location"
# Initialize runspace pool
$runspacePool = Initialize-RunspacePool -MaxThreads $maxThreads
# Step 2: Process source files with multi-threading
$currentOverallStep++
Update-OverallProgress -Activity "Processing source files" -StepNumber $currentOverallStep
Write-Host "Processing source files..." -ForegroundColor Cyan
Write-Host "----------------------------------------" -ForegroundColor Gray
$sourceStartTime = Get-Date
$sourceCollection = Process-FilesInParallel -Files $sourceFiles -BasePath $sourceRootPath -Activity "Processing Source Files" -RunspacePool $runspacePool
$sourceEndTime = Get-Date
$sourceTotalTime = $sourceEndTime - $sourceStartTime
Write-Progress -Id 1 -Activity "Processing Source Files" -Completed
Write-Host "Source file processing complete in $(Format-ElapsedTime -TimeSpan $sourceTotalTime)" -ForegroundColor Green
Write-Host "Successfully processed $($sourceCollection.Count) of $($sourceFiles.Count) files" -ForegroundColor Green
Write-Host "----------------------------------------" -ForegroundColor Gray
# Step 3: Process destination files with multi-threading
$currentOverallStep++
Update-OverallProgress -Activity "Processing destination files" -StepNumber $currentOverallStep
Write-Host "Processing destination files..." -ForegroundColor Cyan
Write-Host "----------------------------------------" -ForegroundColor Gray
$destStartTime = Get-Date
$destinationCollection = Process-FilesInParallel -Files $destinationFiles -BasePath $destinationRootPath -Activity "Processing Destination Files" -RunspacePool $runspacePool
$destEndTime = Get-Date
$destTotalTime = $destEndTime - $destStartTime
Write-Progress -Id 1 -Activity "Processing Destination Files" -Completed
Write-Host "Destination file processing complete in $(Format-ElapsedTime -TimeSpan $destTotalTime)" -ForegroundColor Green
Write-Host "Successfully processed $($destinationCollection.Count) of $($destinationFiles.Count) files" -ForegroundColor Green
Write-Host "----------------------------------------" -ForegroundColor Gray
# Close the runspace pool
$runspacePool.Close()
$runspacePool.Dispose()
# Step 4: Comparing files
$currentOverallStep++
Update-OverallProgress -Activity "Comparing files" -StepNumber $currentOverallStep
Write-Host "Comparing files..." -ForegroundColor Cyan
$comparisonStartTime = Get-Date
# Find files that exist in source but not in destination based on RelativePath (missing files)
$missingFiles = $sourceCollection | Where-Object {
$relPath = $_.RelativePath
-not ($destinationCollection | Where-Object { $_.RelativePath -eq $relPath })
}
# Create enhanced report objects with both source and destination information
$mismatchedFilesReport = @()
$matchedFilesReport = @()
# Process each source file to create detailed comparison reports
$totalSourceFiles = $sourceCollection.Count
$i = 0
foreach ($sourceFile in $sourceCollection) {
$i++
# Update comparison progress every 100 files or 5% of total, whichever is smaller
# Ensure updateInterval is at least 1 to avoid division by zero
$updateInterval = [Math]::Max(1, [Math]::Min(100, [Math]::Floor($totalSourceFiles * 0.05)))
if ($i % $updateInterval -eq 0 -or $i -eq $totalSourceFiles) {
$percent = [Math]::Round(($i / $totalSourceFiles) * 100, 1)
Write-Progress -Id 1 -Activity "Comparing Files" -Status "Processed $i of $totalSourceFiles files ($percent%)" -PercentComplete $percent
# Update overall progress
Update-OverallProgress -Activity "Comparing files" -StepNumber $currentOverallStep
}
# Find matching destination file based on RelativePath
$matchingDestFile = $destinationCollection | Where-Object { $_.RelativePath -eq $sourceFile.RelativePath } | Select-Object -First 1
if ($matchingDestFile) {
# Create a combined report object
$reportObject = [PSCustomObject]@{
FileName = $sourceFile.Name
RelativePath = $sourceFile.RelativePath
SourcePath = $sourceFile.FullPath
DestinationPath = $matchingDestFile.FullPath
SourceSizeBytes = $sourceFile.SizeBytes
DestinationSizeBytes = $matchingDestFile.SizeBytes
SourceSizeKB = $sourceFile.SizeKB
DestinationSizeKB = $matchingDestFile.SizeKB
SourceSizeMB = $sourceFile.SizeMB
DestinationSizeMB = $matchingDestFile.SizeMB
SizeMatch = $sourceFile.SizeBytes -eq $matchingDestFile.SizeBytes
SourceHash = $sourceFile.SHA256Hash
DestinationHash = $matchingDestFile.SHA256Hash
HashMatch = $sourceFile.SHA256Hash -eq $matchingDestFile.SHA256Hash
SourceLastWriteTime = $sourceFile.LastWriteTime
DestinationLastWriteTime = $matchingDestFile.LastWriteTime
TimeMatch = [Math]::Abs(($sourceFile.LastWriteTime - $matchingDestFile.LastWriteTime).TotalSeconds) -lt 2
}
# Add to appropriate collection based on hash matching
if ($sourceFile.SHA256Hash -eq $matchingDestFile.SHA256Hash) {
$matchedFilesReport += $reportObject
} else {
$mismatchedFilesReport += $reportObject
}
}
}
Write-Progress -Id 1 -Activity "Comparing Files" -Completed
# Prepare missing files report with source info only
$missingFilesReport = @($missingFiles | Select-Object @{Name='FileName';Expression={$_.Name}},
@{Name='RelativePath';Expression={$_.RelativePath}},
@{Name='SourcePath';Expression={$_.FullPath}},
@{Name='SourceSizeBytes';Expression={$_.SizeBytes}},
@{Name='SourceSizeKB';Expression={$_.SizeKB}},
@{Name='SourceSizeMB';Expression={$_.SizeMB}},
@{Name='SourceHash';Expression={$_.SHA256Hash}},
@{Name='SourceLastWriteTime';Expression={$_.LastWriteTime}})
$comparisonElapsedTime = (Get-Date) - $comparisonStartTime
$formattedComparisonTime = Format-ElapsedTime -TimeSpan $comparisonElapsedTime
Write-Host "File comparison complete in $formattedComparisonTime" -ForegroundColor Green
Write-Host "----------------------------------------" -ForegroundColor Gray
# Step 5: Generating reports
$currentOverallStep++
Update-OverallProgress -Activity "Generating Excel report" -StepNumber $currentOverallStep
Write-Host "Creating Excel report..." -ForegroundColor Cyan
$reportStartTime = Get-Date
# Determine output path for reports
$currentDateTime = Get-Date -Format "yyyyMMdd-HHmmss"
$outputPath = Join-Path -Path $outputBaseLocation -ChildPath "MigrationValidation_$currentDateTime"
New-Item -Path $outputPath -ItemType Directory -Force | Out-Null
# Update progress
Write-Progress -Id 1 -Activity "Creating Excel Report" -Status "Initializing report" -PercentComplete 10
# Create Excel report with formatting
$excelReportPath = Join-Path -Path $outputPath -ChildPath "MigrationValidation_Report.xlsx"
try {
# Check if Excel is installed
$excel = New-Object -ComObject Excel.Application -ErrorAction Stop
$excel.Visible = $false
$excel.DisplayAlerts = $false
# Create a new Excel workbook
$workbook = $excel.Workbooks.Add()
# Rename the first sheet to Summary
$summarySheet = $workbook.Worksheets.Item(1)
$summarySheet.Name = "Summary"
# Add additional sheets
$matchedSheet = $workbook.Worksheets.Add()
$matchedSheet.Name = "Matched Files"
$mismatchedSheet = $workbook.Worksheets.Add()
$mismatchedSheet.Name = "Mismatched Files"
$missingSheet = $workbook.Worksheets.Add()
$missingSheet.Name = "Missing Files"
# Update progress
Write-Progress -Id 1 -Activity "Creating Excel Report" -Status "Populating Excel worksheets" -PercentComplete 30
# ----------------- Summary Sheet -----------------
$summarySheet.Cells.Item(1, 1) = "Migration Validation Summary"
$summarySheet.Cells.Item(1, 1).Font.Bold = $true
$summarySheet.Cells.Item(1, 1).Font.Size = 14
$summarySheet.Cells.Item(3, 1) = "Source Folder:"
$summarySheet.Cells.Item(3, 2) = $sourceFolder
$summarySheet.Cells.Item(3, 1).Font.Bold = $true
$summarySheet.Cells.Item(4, 1) = "Destination Folder:"
$summarySheet.Cells.Item(4, 2) = $destinationFolder
$summarySheet.Cells.Item(4, 1).Font.Bold = $true
$summarySheet.Cells.Item(5, 1) = "Source Root Path:"
$summarySheet.Cells.Item(5, 2) = $sourceRootPath
$summarySheet.Cells.Item(5, 1).Font.Bold = $true
$summarySheet.Cells.Item(6, 1) = "Destination Root Path:"
$summarySheet.Cells.Item(6, 2) = $destinationRootPath
$summarySheet.Cells.Item(6, 1).Font.Bold = $true
$summarySheet.Cells.Item(7, 1) = "Report Generated:"
$summarySheet.Cells.Item(7, 2) = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$summarySheet.Cells.Item(7, 1).Font.Bold = $true
$summarySheet.Cells.Item(8, 1) = "Threads Used:"
$summarySheet.Cells.Item(8, 2) = $maxThreads
$summarySheet.Cells.Item(8, 1).Font.Bold = $true
$summarySheet.Cells.Item(10, 1) = "Total Source Files:"
$summarySheet.Cells.Item(10, 2) = $sourceFiles.Count
$summarySheet.Cells.Item(10, 1).Font.Bold = $true
$summarySheet.Cells.Item(11, 1) = "Total Destination Files:"
$summarySheet.Cells.Item(11, 2) = $destinationFiles.Count
$summarySheet.Cells.Item(11, 1).Font.Bold = $true
$summarySheet.Cells.Item(12, 1) = "Time to Process Source Files:"
$summarySheet.Cells.Item(12, 2) = Format-ElapsedTime -TimeSpan $sourceTotalTime
$summarySheet.Cells.Item(12, 1).Font.Bold = $true
$summarySheet.Cells.Item(13, 1) = "Time to Process Destination Files:"
$summarySheet.Cells.Item(13, 2) = Format-ElapsedTime -TimeSpan $destTotalTime
$summarySheet.Cells.Item(13, 1).Font.Bold = $true
$summarySheet.Cells.Item(15, 1) = "Migration Results"
$summarySheet.Cells.Item(15, 1).Font.Bold = $true
$summarySheet.Cells.Item(16, 1) = "Successfully Matched Files:"
$summarySheet.Cells.Item(16, 2) = $matchedFilesReport.Count
$summarySheet.Cells.Item(16, 1).Font.Bold = $true
$summarySheet.Cells.Item(17, 1) = "Files With Content Mismatch:"
$summarySheet.Cells.Item(17, 2) = $mismatchedFilesReport.Count
$summarySheet.Cells.Item(17, 1).Font.Bold = $true
if ($mismatchedFilesReport.Count -gt 0) {
$summarySheet.Cells.Item(17, 2).Font.ColorIndex = 3 # Red
}
$summarySheet.Cells.Item(18, 1) = "Missing Files:"
$summarySheet.Cells.Item(18, 2) = $missingFilesReport.Count
$summarySheet.Cells.Item(18, 1).Font.Bold = $true
if ($missingFilesReport.Count -gt 0) {
$summarySheet.Cells.Item(18, 2).Font.ColorIndex = 3 # Red
}
# Auto-fit columns
$summarySheet.UsedRange.Columns.AutoFit() | Out-Null
# ----------------- Matched Files Sheet -----------------
# Add headers
$headers = @("File Name", "Relative Path", "Source Path", "Destination Path", "Source Size (KB)", "Destination Size (KB)", "Size Match", "Source Hash", "Destination Hash", "Hash Match", "Source Modified", "Destination Modified", "Time Match")
for ($col = 0; $col -lt $headers.Count; $col++) {
$matchedSheet.Cells.Item(1, $col + 1) = $headers[$col]
$matchedSheet.Cells.Item(1, $col + 1).Font.Bold = $true
}
# Add data
$row = 2
foreach ($file in $matchedFilesReport) {
$matchedSheet.Cells.Item($row, 1) = $file.FileName
$matchedSheet.Cells.Item($row, 2) = $file.RelativePath
$matchedSheet.Cells.Item($row, 3) = $file.SourcePath
$matchedSheet.Cells.Item($row, 4) = $file.DestinationPath
$matchedSheet.Cells.Item($row, 5) = $file.SourceSizeKB
$matchedSheet.Cells.Item($row, 6) = $file.DestinationSizeKB
$matchedSheet.Cells.Item($row, 7) = $file.SizeMatch
$matchedSheet.Cells.Item($row, 8) = $file.SourceHash
$matchedSheet.Cells.Item($row, 9) = $file.DestinationHash
$matchedSheet.Cells.Item($row, 10) = $file.HashMatch
$matchedSheet.Cells.Item($row, 11) = $file.SourceLastWriteTime
$matchedSheet.Cells.Item($row, 12) = $file.DestinationLastWriteTime
$matchedSheet.Cells.Item($row, 13) = $file.TimeMatch
# Color the match cells
if ($file.SizeMatch) {
$matchedSheet.Cells.Item($row, 7).Interior.ColorIndex = 4 # Green
} else {
$matchedSheet.Cells.Item($row, 7).Interior.ColorIndex = 3 # Red
}
if ($file.HashMatch) {
$matchedSheet.Cells.Item($row, 10).Interior.ColorIndex = 4 # Green
} else {
$matchedSheet.Cells.Item($row, 10).Interior.ColorIndex = 3 # Red
}
if ($file.TimeMatch) {
$matchedSheet.Cells.Item($row, 13).Interior.ColorIndex = 4 # Green
} else {
$matchedSheet.Cells.Item($row, 13).Interior.ColorIndex = 3 # Red
}
$row++
}
# Auto-fit columns
$matchedSheet.UsedRange.Columns.AutoFit() | Out-Null
# ----------------- Mismatched Files Sheet -----------------
# Add headers (same as matched files)
for ($col = 0; $col -lt $headers.Count; $col++) {
$mismatchedSheet.Cells.Item(1, $col + 1) = $headers[$col]
$mismatchedSheet.Cells.Item(1, $col + 1).Font.Bold = $true
}
# Add data
$row = 2
foreach ($file in $mismatchedFilesReport) {
$mismatchedSheet.Cells.Item($row, 1) = $file.FileName
$mismatchedSheet.Cells.Item($row, 2) = $file.RelativePath
$mismatchedSheet.Cells.Item($row, 3) = $file.SourcePath
$mismatchedSheet.Cells.Item($row, 4) = $file.DestinationPath
$mismatchedSheet.Cells.Item($row, 5) = $file.SourceSizeKB
$mismatchedSheet.Cells.Item($row, 6) = $file.DestinationSizeKB
$mismatchedSheet.Cells.Item($row, 7) = $file.SizeMatch
$mismatchedSheet.Cells.Item($row, 8) = $file.SourceHash
$mismatchedSheet.Cells.Item($row, 9) = $file.DestinationHash
$mismatchedSheet.Cells.Item($row, 10) = $file.HashMatch
$mismatchedSheet.Cells.Item($row, 11) = $file.SourceLastWriteTime
$mismatchedSheet.Cells.Item($row, 12) = $file.DestinationLastWriteTime
$mismatchedSheet.Cells.Item($row, 13) = $file.TimeMatch
# Color the match cells
if ($file.SizeMatch) {
$mismatchedSheet.Cells.Item($row, 7).Interior.ColorIndex = 4 # Green
} else {
$mismatchedSheet.Cells.Item($row, 7).Interior.ColorIndex = 3 # Red
}
if ($file.HashMatch) {
$mismatchedSheet.Cells.Item($row, 10).Interior.ColorIndex = 4 # Green
} else {
$mismatchedSheet.Cells.Item($row, 10).Interior.ColorIndex = 3 # Red
}
if ($file.TimeMatch) {
$mismatchedSheet.Cells.Item($row, 13).Interior.ColorIndex = 4 # Green
} else {
$mismatchedSheet.Cells.Item($row, 13).Interior.ColorIndex = 3 # Red
}
$row++
}
# Auto-fit columns
$mismatchedSheet.UsedRange.Columns.AutoFit() | Out-Null
# ----------------- Missing Files Sheet -----------------
# Add headers
$missingHeaders = @("File Name", "Relative Path", "Source Path", "Source Size (KB)", "Source Hash", "Source Modified")
for ($col = 0; $col -lt $missingHeaders.Count; $col++) {
$missingSheet.Cells.Item(1, $col + 1) = $missingHeaders[$col]
$missingSheet.Cells.Item(1, $col + 1).Font.Bold = $true
}
# Add data
$row = 2
foreach ($file in $missingFilesReport) {
$missingSheet.Cells.Item($row, 1) = $file.FileName
$missingSheet.Cells.Item($row, 2) = $file.RelativePath
$missingSheet.Cells.Item($row, 3) = $file.SourcePath
$missingSheet.Cells.Item($row, 4) = $file.SourceSizeKB
$missingSheet.Cells.Item($row, 5) = $file.SourceHash
$missingSheet.Cells.Item($row, 6) = $file.SourceLastWriteTime
$row++
}
# Auto-fit columns
$missingSheet.UsedRange.Columns.AutoFit() | Out-Null
# Update progress
Write-Progress -Id 1 -Activity "Creating Excel Report" -Status "Formatting Excel document" -PercentComplete 90
# Format tables as simple formatted ranges instead of ListObjects
$matchedRange = $matchedSheet.UsedRange
$matchedRange.Borders.LineStyle = 1
$matchedRange.Rows.Item(1).Font.Bold = $true
$matchedRange.Rows.Item(1).Interior.ColorIndex = 15
$mismatchedRange = $mismatchedSheet.UsedRange
$mismatchedRange.Borders.LineStyle = 1
$mismatchedRange.Rows.Item(1).Font.Bold = $true
$mismatchedRange.Rows.Item(1).Interior.ColorIndex = 15
$missingRange = $missingSheet.UsedRange
$missingRange.Borders.LineStyle = 1
$missingRange.Rows.Item(1).Font.Bold = $true
$missingRange.Rows.Item(1).Interior.ColorIndex = 15
# Save the workbook
$workbook.SaveAs($excelReportPath)
$workbook.Close($true)
$excel.Quit()
# Release COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($matchedRange) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($mismatchedRange) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($missingRange) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($matchedSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($mismatchedSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($missingSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($summarySheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Write-Progress -Id 1 -Activity "Creating Excel Report" -Completed
Write-Host "Excel report created successfully at: $excelReportPath" -ForegroundColor Green
}
catch {
Write-Host "Could not create Excel report: $($_.Exception.Message)" -ForegroundColor Yellow
Write-Host "Validation was completed successfully, but no report could be created." -ForegroundColor Yellow
}
# Complete the overall progress
Write-Progress -Id 0 -Activity "File Migration Validation" -Status "Complete" -PercentComplete 100
Start-Sleep -Seconds 1
Write-Progress -Id 0 -Activity "File Migration Validation" -Completed
# Calculate overall elapsed time
$overallElapsedTime = (Get-Date) - $overallStartTime
$formattedOverallTime = Format-ElapsedTime -TimeSpan $overallElapsedTime
Write-Host "`nMigration Validation Summary:" -ForegroundColor Green
Write-Host "Total source files: $($sourceFiles.Count)"
Write-Host "Total destination files: $($destinationFiles.Count)"
Write-Host "Missing files: $($missingFilesReport.Count)"
Write-Host "Files with content mismatch: $($mismatchedFilesReport.Count)"
Write-Host "Successfully matched files: $($matchedFilesReport.Count)"
Write-Host "`nSource processing time: $(Format-ElapsedTime -TimeSpan $sourceTotalTime)"
Write-Host "Destination processing time: $(Format-ElapsedTime -TimeSpan $destTotalTime)"
Write-Host "Total processing time: $formattedOverallTime"
Write-Host "`nResults exported to: $outputPath"