January 7, 2013

Export SharePoint Group to Excel using PowerShell

$siteUrl="SiteURLHere"
$groupName="GroupNametoExport"
$Output = @("GroupName|Name|Login|Email|Department|Title")
$web = Get-SPWeb $siteUrl
$site = $web.Site
$rootWeb = $site.RootWeb
$UserList = $rootWeb.Lists["User Information List"]
$web.SiteGroups[$groupName].Users|%{$user = $UserList.GetItemById($_.ID)
if($user -ne $null)
{
$JobTitle = $user["JobTitle"]
$Department = $user["Department"]
}
$Output += ($groupName+"|"+$_.Name+"|"+$_.UserLogin+"|"+$_.Email+"|"+$ Department +"|"+$JobTitle)
}
$rootWeb.Dispose()
$web.Dispose()
$site.Dispose()
$Output > "D:\MembersExport.csv"

3 comments:

  1. Got the following error..

    You must provide a value expression on the right-hand side of the '+' operator.
    At line:7 char:70
    + $Output += ($groupName+"|"+$_.Name+"|"+$_.UserLogin+"|"+$_.Email+"|"+ <<<< $
    Department +"|"+$JobTitle)
    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordEx
    ception
    + FullyQualifiedErrorId : ExpectedValueExpression

    ReplyDelete
  2. I figured out the issue.. There was space after $ and before Deparment.
    Here is the updated one.

    $siteUrl="http://itacentral/myorg/gm/regions/us/wg/gmevents"
    $groupName="GM Events Members"
    $Output = @("GroupName|Name|Login|Email|Department|Title")
    $web = Get-SPWeb $siteUrl
    $site = $web.Site
    $rootWeb = $site.RootWeb
    $UserList = $rootWeb.Lists["User Information List"]
    $web.SiteGroups[$groupName].Users|%{$user = $UserList.GetItemById($_.ID)
    if($user -ne $null)
    {
    $JobTitle = $user["JobTitle"]
    $Department = $user["Department"]
    }
    $Output += ($groupName+"|"+$_.Name+"|"+$_.UserLogin+"|"+$_.Email+"|"+$Department +"|"+$JobTitle)
    }
    $rootWeb.Dispose()
    $web.Dispose()
    $site.Dispose()
    $Output > "D:\WaseemBackup\MembersExport.csv"

    ReplyDelete
  3. This was helpful, although for SharePoint 2013 the | character is part of the UserLogin for a claims id. If you use * instead as the delimiter, Excel won't break apart the claims ids during the import.

    ReplyDelete

Dear Readers, I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.