kwizcom banner advertisement

Export SharePoint List Item Version History to Excel using PowerShell

I wrote a PowerShell script and C# object model codes to generate SharePoint version history report in SharePoint document version history report, related to that, got a new requirement to export version history to excel.
export sharepoint list version history to excel using powershell

This version history report helps to compare what data is changed, who changed the list data, etc over time.  just replace the values of WebURL, ListName variables with your Site/List values and run the script to export version history SharePoint 2013.

PowerShell script to Export SharePoint List version history to excel:
# ******* Variables Section ******************
#Define these variables 
$WebURL="http://sharepoint.crescent.com/sites/Sales/"
$ListName ="Invoice"
$ReportFile = "D:\Invoice_VersionHistory.csv"
# *********************************************

#delete file if exists
If (Test-Path $ReportFile)
 {
 Remove-Item $ReportFile
 }

#Get the Web and List
$Web = Get-SPWeb $WebURL
$List = $web.Lists.TryGetList($ListName) 

 #Check if list exists
 if($List -ne $null)
 {
  #Get all list items
  $ItemsColl = $List.Items
  
  #Write Report Header
  Add-Content -Path $ReportFile -Value "Item ID, Version Lable, Created by, Created at, Title" 
 
  #Loop through each item
  foreach ($item in $ItemsColl) 
  {
   #Iterate each version
      foreach($version in $item.Versions)
       {
    #Get the version content
    $VersionData = "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title'])"
    #Write to report
    Add-Content -Path $ReportFile -Value $VersionData
   }
  }
 }
Write-Host "Version history has been exported successfully!"

Please note, in line numbers 25 and 36, I've added only "Title" field from the list. You may want to add additional fields as per your requirement. On running the script, all list item versions will be exported to Excel (CSV format).

To export SharePoint document version history, Refer: Extract and Download all document versions
Export SharePoint List Item Version History to Excel using PowerShell Export SharePoint List Item Version History to Excel using PowerShell Reviewed by Salaudeen Rajack on 4:53 PM Rating: 5

5 comments:

  1. Fining this resource was GREAT!!! Thanks for the share. Although, I tried to get this to work on SharePoint Online and it will not. I think there may not be a command equivalent to Get-SPWeb. Is there another way to get this accomplished? Maybe via RESTful API
    http://msdn.microsoft.com/en-us/library/office/jj860569(v=office.15).aspx
    What do you think? alanshieldsjr at live dot com

    ReplyDelete
  2. how about SP 2010?

    ReplyDelete
    Replies
    1. This script works on SharePoint 2010 Also!

      Delete
  3. This is a great script and it has helped me quite a bit. But does anyone know how I can leverage this script to export all the custom tags/columns that were modified with each version? Let's say we have a list with 10 custom tags and for each version, we want to export the tags that were changed.

    ReplyDelete
  4. I wanted to find version history for auditing purpose, thanks to the above powershell script, it got me half way. Thought I might share as it might be useful to someone else.

    In the below I wanted to know when was the field of "consult status" changed. I omit customer details due to privacy reason. From the version history we can see when consult status has changed.

    version
    4.0 17/09/2013 11:38 presence information

    CONSULT STATUS COMPLETED
    3.0 17/09/2013 11:34

    CONSULT REASON FOR REVIEW
    2.0 17/09/2013 11:28


    CONSULT STATUS REVIEWED
    1.0 17/09/2013 10:35
    ---


    I modified the above script for my own environment, sharepoint 2010.

    # ******* Variables Section ******************
    #Define these variables
    $WebURL= "http://sharepoint/ ";
    $ListName ="CONSULT"
    $ReportFile = "c:\temp\VersionHistory.csv"
    # *********************************************

    #delete file if exists
    If (Test-Path $ReportFile)
    {
    Remove-Item $ReportFile
    }

    #Get the Web and List
    $Web = Get-SPWeb $WebURL;
    $List = $web.Lists.TryGetList($ListName);

    #Check if list exists
    if($List -ne $null)
    {
    #Get all list items
    $ItemsColl = $List.Items;
    $versionColl = $ItemColl.Version;

    #Write Report Header
    Add-Content -Path $ReportFile -Value "status change, version history, version date, customer, customernumber, admission date, req unit, consult unit"

    #Loop through each item
    foreach ($item in $ItemsColl)
    {
    # I need to find out what these column are, it is for debugging purpose
    #write-host $item[0];
    #write-host $item[1];
    #write-host $item[19];
    #write-host $item[20];
    #$ItemData = "$($item.id), $($item[0]), $($item[1]), $($item[19]), $($item[20]), $($item."Consult Requesting Unit"))"

    #Iterate each version
    foreach($version in $item.Versions)
    {
    #Get the version content
    #$VersionData = "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $(Get-Date ($version.Created) -Format "yyyy-MM-dd-HH:mm:ss"), $($version.ListItem.Title),$($version.Field.Modified)"
    #Write to report
    #Add-Content -Path $ReportFile -Value $VersionData
    #write-host $VersionData;
    foreach ($field in $version.Fields)
    {
    if ($field.Title -eq "CONSULT STATUS") # I only wanted consult status
    {
    if ( $(Get-Date ($version.Created) -Format "yyyy") -eq '2017' ) # as the data is too large, filter for 2017
    {
    $fieldstatus = $field.GetFieldValueAsText($version[$field.Title]);
    $customertile1 = $($version.ListItem.Title) -replace ',', ' '; # replace commas as it is bad for csv
    $VersionData = "$fieldstatus , $($version.VersionLabel), $(Get-Date ($version.Created) -Format "yyyy-MM-dd-HH:mm:ss"), $customertile1, $($item[1]), $(Get-Date ($item[7]) -Format "yyyy-MM-dd-HH:mm:ss"), $($item[15]), $($item[19])";
    Add-Content -Path $ReportFile -Value $VersionData;
    #write-host $VersionData;
    }
    }
    }
    }
    # Add-Content -Path $ReportFile -Value $ItemData
    }
    }
    Write-Host "Version history has been exported successfully!"


    result of extract to csv will look something like below. It gave me my audit trail for when consult status changed.
    status change version history version date customer customerid
    COMPLETED 7 2017-01-03-03:39:58 fred 1105484
    COMPLETED 6 2017-01-03-03:39:58 fred 1105484
    COMPLETED 5 2017-01-03-03:39:57 fred 1105484
    REVIEWED 4 2017-01-03-00:55:59 fred 1105484
    REVIEWED 3 2017-01-03-00:55:59 fred 1105484
    NEW 2 2017-01-01-00:56:47 fred 1105484
    NEW 1 2017-01-01-00:56:42 fred 1105484

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.