Use Case: Export User Profiles to a SharePoint list – phonebook style

SharePoint User Profiles Service Application has a lot of nice and very useful features but it misses one small but important thing. Customers often want to display all their contacts (from User Profile Store) in a single list, phonebook style.

Here is a simple solution how to achieve this with a PowerShell script. The script queries the User Profile store and then copies user profile information to a simple SharePoint list (based on Contacts list template). The final result might look like this:

Please note:

• If your company has more than 1000 employees this solution might not scale properly.
• The script below was designed for English based SharePoint site templates, additional modifications might be required for other languages.
• The script must be executed under user that has both read privileges to User Profile Service Application and contribute privileges for your site.

Configuration steps:

• On your intranet/team site create a list based on the Contacts list template
• Create an additional column in this list: UserName – Single line of text

Here is the script:

cls
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
	Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
function Enumerate-SPUserProfiles($webUrl, $listName)
{
	$x= [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
	$x= [System.Reflection.Assembly]::LoadWithPartialName("microsoft.sharepoint.portal")
	$x= [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
	$x= [System.Reflection.Assembly]::LoadWithPartialName("System.Web")
	$sites = Get-SPSite
	$context = [Microsoft.Office.Server.ServerContext]::GetContext($sites[0])
	$profileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

	$web = Get-SPWeb $webUrl
	$list = $web.Lists[$listName]

	# gets all the user profiles, those that have domain\username as DisplayName are excluded
	$profileManager.GetEnumerator() | ?{$_.DisplayName -notlike '*\*' -and $_.ProfileType -eq "User" } | foreach-object { Update-PhoneBook $_ $web $list $webUrl } |
}

function Update-PhoneBook($user, $web, $list, $webUrl)
{
	if($user.Item("FirstName") -ne "" -and $user.Item("FirstName") -ne $null)
	{
		$spQuery = new-object Microsoft.SharePoint.SPQuery
		$userName = $user.Item("UserName")
		$camlQuery = "<Where><Eq><FieldRef Name='UserName' /><Value Type='Text'>$userName</Value></Eq></Where>";
		$spQuery.Query = $camlQuery
		$spQuery.RowLimit = 1
	 	$spListItemCollection = $list.GetItems($spQuery)
		$domain = (gwmi WIN32_ComputerSystem).Domain

		if($spListItemCollection.Count -ge 1)
		{
			Write-Host "Updating $username..."
			Update-Contact $spListItemCollection[0] $user $domain $webUrl
		}
		else
		{
			Write-Host "Adding $username..."
			$contact = $list.items.add();
			Update-Contact $contact $user $domain $webUrl
		}
	}
}

function Update-Contact($contact, $user, $domain, $webUrl)
{
	$lastName = $user.Item("LastName");
	$contact["Title"] = "$lastName";

	$firstName = $user.Item("FirstName");
	$contact["FirstName"] = "$firstName";

	$workEmail = $user.Item("WorkEmail");
	$contact["Email"] = "$workEmail";

	$workPhone = $user.Item("WorkPhone");
	$contact["WorkPhone"] = "$workPhone";

	$CellPhone = $user.Item("CellPhone");
	$contact["CellPhone"] = "$cellPhone";

	$office = $user.Item("Office");
	$contact["Office"] = "$office";

	$department = $user.Item("Department");
	$contact["Department"] = "$department";

	$userName = $user.Item("UserName");
	$contact["UserName"] = "$userName";

	$contact.update()
}

You can run it by typing:

Enumerate-SPUserProfiles "http://your_site_url" "List_Name"