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