Thursday 8 November 2012

Export list item metadata to a CSV file using PowerShell

I recently wrote a bit of a script for someone on a forum that exports certain listitem metadata to a CSV file. I thought I'd share it here:

The basic idea is the script needs to check all the lists of a certain type in a web and it's sub webs, and check whether a certain metadata field had been filled in (or not), and then export the results to a CSV file.

The script has a method that's called recursively, adding the results to a custom psobject. It then uses Export-Csv commandlet to export the results to a CSV file... easy!

The results look like this:



Here's the code (which could probably do with being "elegantised"):

function Audit-Webs($SiteUrl,$CheckSubWebs) 
{ 
$items = New-Object psobject 
$items | Add-Member -MemberType NoteProperty -Name "Title" -value "" ;
$items | Add-Member -MemberType NoteProperty -Name "Field" -value "" ;
$items | Add-Member -MemberType NoteProperty -Name "FieldValue" -value "" ;
$items | Add-Member -MemberType NoteProperty -Name "ListTitle" -value ""; 
$items | Add-Member -MemberType NoteProperty -Name "Web" -value "" ;
$a = $null $a = @(); 
$w = get-spweb $SiteUrl ;
$lc = $w.Lists; 
foreach($l in $lc){ 
if($l.Hidden -eq $true){continue;} 
if($l.BaseTemplate -eq 100 -and $l.Title -eq "peopleHighlight"){ 
$listitems = $l.Items;
foreach($i in $listitems){ 
$b = $items | Select-Object *; 
$b.ListTitle=$l.Title; 
$b.Web=$w.Title; 
$b.Field = "Order0"; 
if([String]::IsNullOrEmpty($i["Title"])){$b.Title = "";}
else{$b.Title = $i["Title"];} 
if([String]::IsNullOrEmpty($i["Order0"])){$b.FieldValue = $false;} 
else{ $b.FieldValue = $true;}
$a+=$b
}
}
} 
if($CheckSubWebs) { 
if($w.Webs.Count -gt 0) {
foreach($sw in $w.Webs) {
$a += Audit-Webs -SiteUrl $sw.Url -CheckSubWebs $true 
} 
} 
} 
$w.Dispose(); 
Write-Output $a 
} 

$a = $null;
$a = @();
$a = Audit-Webs -SiteUrl "http://mywebsite" -CheckSubWebs $true $a 
| Where-Object {$_} | Export-Csv -Delimiter "," -Path C:\export1.csv -notype  

2 comments:

  1. Matt, is there a way to only loop through the top level web and first level sub webs? In my organization we're not too worried about the documents held in the lower sub sites.

    ReplyDelete
    Replies
    1. Yes. Add a if statement to the foreach loop that checks to see if the sub webs parent is the root web.

      E.g.

      $w = get-spweb http://sneakpreview
      foreach($sw in $w.webs)
      {
      if ($sw.Parent.IsRootWeb -eq $true)
      {
      ...
      }
      }

      Delete