Scheduling PowerShell to import SPListItem version history into CSV file

Problem

I need to populate SQL table to store permanent and temporary address changes from SPList item version history. I need to store the first version and subsequent versions only if the address is updated. All will be stored as CSV file with latest address (version) first.

Since the following script will be run as Windows Scheduled Task, it will also rename $csvFileName with original date/time stamp before creating a new one.

Solution

# Import list item version history of RP content type to CSV file / SQL table
# Created by Ben - 7 March 2019

clear

start-transcript

if ((gsnp '*sharepoint*' -ErrorAction SilentlyContinue) -eq $null){asnp *sharepoint*}
$currentPs1Filename = $MyInvocation.MyCommand.Name
$currentPs1FilePath = $MyInvocation.MyCommand.Definition
$currentPs1Folder = $currentPs1FilePath.SubString(0,$currentPs1FilePath.indexOf($currentPs1Filename))

$siteUrl = 'http://your-target-site-url'
$csvFileName = 'RPAddressHistory.csv'
$blankCell = ''

$startTime = (Get-Date)
$counter = 0
$primaryKey = 0
$site = Get-SPSite -Identity $siteUrl
$csvPath = $currentPs1Folder+$csvFileName
$AddressHistory = @()

$csvFileDateTimeStamp = [datetime](Get-ItemProperty -Path $csvPath -Name LastWriteTime).lastwritetime
if (Test-Path $csvPath){
 Rename-Item -Path $csvPath -NewName ($csvFileName.SubString(0,$csvFileName.indexOf('.')+1)+$csvFileDateTimeStamp.ToString("yyyyMMdd-HHmmss")+$csvFileName.SubString($csvFileName.indexOf('.'),4))
}

foreach($web in $site.AllWebs){
    $counter += 1
    if($web.url -like '*cases/????/*'){        
        write-host Progress: ($counter/$site.AllWebs.Count).ToString('0%') - Processing $web.url
        foreach($list in $web.Lists) {
            if($list.Title -eq 'Person Details'){
                foreach($listItem in $list.Items){
                    if($listItem.ContentType.Name -eq 'RP'){
                        #write-host Total RP Version Updates: $listItem.Versions.Count - $listItem['Title']
                        $AddressHistoryToBeReversedInOrder = @()
                        for($i=$listItem.Versions.Count;$i -ge 0;$i--){
                            $version = $listItem.Versions[$i]
                            if($version.Level -eq 'Published'){
                                $PermanentAddressResidence = $version['PermanentAddressResidence']
                                $PermanentAddressNumber = $version['PermanentAddressNumber']
                                $PermanentAddressStreet = $version['PermanentAddressStreet']
                                $PermanentAddressSuburb = $version['PermanentAddressSuburb']
                                $PermanentAddressPostcode = $version['PermanentAddressPostcode']
                                $PermanentAddressFromDate = $version['PermanentAddressFromDate']
                                $TemporaryAddressResidence = $version['TemporaryAddressResidence']
                                $TemporaryAddressNumber = $version['TemporaryAddressNumber']
                                $TemporaryAddressStreet = $version['TemporaryAddressStreet']
                                $TemporaryAddressSuburb = $version['TemporaryAddressSuburb']
                                $TemporaryAddressPostcode = $version['TemporaryAddressPostcode']
                                $TemporaryAddressFromDate = $version['TemporaryAddressFromDate']
                                
                                $currentPermanentAddress = $PermanentAddressResidence + $PermanentAddressNumber + $PermanentAddressStreet + $PermanentAddressSuburb + $PermanentAddressPostcode + $PermanentAddressFromDate
                                $currentTemporaryAddress = $TemporaryAddressResidence + $TemporaryAddressNumber + $TemporaryAddressStreet + $TemporaryAddressSuburb + $TemporaryAddressPostcode + $TemporaryAddressFromDate

                                if($currentPermanentAddress -eq $previousPermanentAddress){
                                    $PermanentAddressResidence = $blankCell
                                    $PermanentAddressNumber = $blankCell
                                    $PermanentAddressStreet = $blankCell
                                    $PermanentAddressSuburb = $blankCell
                                    $PermanentAddressPostcode = $blankCell
                                    $PermanentAddressFromDate = $blankCell
                                }

                                if($currentTemporaryAddress -eq $previousTemporaryAddress){
                                    $TemporaryAddressResidence = $blankCell
                                    $TemporaryAddressNumber = $blankCell
                                    $TemporaryAddressStreet = $blankCell
                                    $TemporaryAddressSuburb = $blankCell
                                    $TemporaryAddressPostcode = $blankCell
                                    $TemporaryAddressFromDate = $blankCell
                                }
                                
                                if(($currentPermanentAddress -ne $previousPermanentAddress) -or ($currentTemporaryAddress -ne $previousTemporaryAddress)){                                    
                                    $AddressHistoryToBeReversedInOrder += New-Object PSObject -Property @{
                                        ParentWebGuid = $web.id
                                        RecordingDate = $version.Created
                                        PermanentAddressResidence = $PermanentAddressResidence
                                        PermanentAddressNumber = $PermanentAddressNumber
                                        PermanentAddressStreet = $PermanentAddressStreet
                                        PermanentAddressSuburb = $PermanentAddressSuburb
                                        PermanentAddressPostcode = $PermanentAddressPostcode
                                        PermanentAddressFromDate = $PermanentAddressFromDate
                                        TemporaryAddressResidence = $TemporaryAddressResidence
                                        TemporaryAddressNumber = $TemporaryAddressNumber
                                        TemporaryAddressStreet = $TemporaryAddressStreet
                                        TemporaryAddressSuburb = $TemporaryAddressSuburb
                                        TemporaryAddressPostcode = $TemporaryAddressPostcode
                                        TemporaryAddressFromDate = $TemporaryAddressFromDate
                                    }
                                }
                                $previousPermanentAddress = $currentPermanentAddress
                                $previousTemporaryAddress = $currentTemporaryAddress
                            }
                        }
                        for($i=$AddressHistoryToBeReversedInOrder.Count-1;$i -ge 0;$i--){
                            $primaryKey += 1
                            $AddressHistory += New-Object PSObject -Property @{
                                        Id = $primaryKey
                                        ParentWebGuid = $AddressHistoryToBeReversedInOrder[$i].ParentWebGuid
                                        RecordingDate = $AddressHistoryToBeReversedInOrder[$i].RecordingDate
                                        PermanentAddressResidence = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressResidence
                                        PermanentAddressNumber = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressNumber
                                        PermanentAddressStreet = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressStreet
                                        PermanentAddressSuburb = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressSuburb
                                        PermanentAddressPostcode = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressPostcode
                                        PermanentAddressFromDate = $AddressHistoryToBeReversedInOrder[$i].PermanentAddressFromDate
                                        TemporaryAddressResidence = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressResidence
                                        TemporaryAddressNumber = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressNumber
                                        TemporaryAddressStreet = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressStreet
                                        TemporaryAddressSuburb = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressSuburb
                                        TemporaryAddressPostcode = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressPostcode
                                        TemporaryAddressFromDate = $AddressHistoryToBeReversedInOrder[$i].TemporaryAddressFromDate
                                    }
                        }
                    }
                }
            }
        }
    }
    $web.Dispose()
}
$site.Dispose()

$AddressHistory | Select-Object -Property Id, ParentWebGuid, RecordingDate, PermanentAddressResidence, PermanentAddressNumber, PermanentAddressStreet, PermanentAddressSuburb, PermanentAddressPostcode, PermanentAddressFromDate, TemporaryAddressResidence, TemporaryAddressNumber, TemporaryAddressStreet, TemporaryAddressSuburb, TemporaryAddressPostcode, TemporaryAddressFromDate  | Export-Csv -NoTypeInformation -Path $csvPath
#$AddressHistory | Format-Table -Property Id, ParentWebGuid, RecordingDate, PermanentAddressResidence, PermanentAddressNumber, PermanentAddressStreet, PermanentAddressSuburb, PermanentAddressPostcode, PermanentAddressFromDate, TemporaryAddressResidence, TemporaryAddressNumber, TemporaryAddressStreet, TemporaryAddressSuburb, TemporaryAddressPostcode, TemporaryAddressFromDate

write-host Total number of web processed: $counter
write-host Duration: ((Get-Date) - $startTime).TotalMinutes minutes

stop-transcript

Save the above script as .ps1 file and create a Windows Scheduler task as follows:

Explanation

if ((gsnp "*sharepoint*" -ErrorAction SilentlyContinue) -eq $null){asnp *sharepoint*}

to load SharePoint snap-in if not loaded

$AddressHistoryToBeReversedInOrder is for storing each SPListItem version interogated from first version to last.

$AddressHistory is for storing final array of object to be stored as CSV file with latest version written first to create descending order by default.

$csvFileDateTimeStamp = [datetime](Get-ItemProperty -Path $csvPath -Name LastWriteTime).lastwritetime
if (Test-Path $csvPath){
 Rename-Item -Path $csvPath -NewName ($csvFileName.SubString(0,$csvFileName.indexOf('.')+1)+$csvFileDateTimeStamp.ToString("yyyyMMdd-HHmmss")+$csvFileName.SubString($csvFileName.indexOf('.'),4))
}

to rename existing CSV file with its original date/time stamp.

The CSV output will be written on the same directory where the script file is stored.