Friday, January 21, 2011

Use SharePoint Web Services to export all User Profile data to CSV

Here is a an easy way to get user profile data out of SharePoint. The solution uses web services so you don’t have to worry about having access to the server. It will work with SharePoint 2007 and SharePoint 2010.

Steps:
1. Create a Visual Studio Console Application.
2.
Make a web service reference to the following web services:

http://portalname/_vti_bin/usergroup.asmx (Name it UserGroupWs)
http://portalname/_vti_bin/userprofileservice.asmx (Name it UserProfileServiceWs)

3. Copy the following code and run it! It will save a file to your desktop called SharePointUserData.csv.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using ConsoleApplication1.UserGroupWs;
using ConsoleApplication1.UserProfileServiceWs;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
UserInfo ui = new UserInfo();
List<UserInfo> userInfo = ui.GetUserInfo();
StringBuilder sb = new StringBuilder();
sb.Append("AboutMe,");
sb.Append("AccountName,");
sb.Append("ADGuid,");
sb.Append("Assistant,");
sb.Append("CellPhone,");
sb.Append("Department,");
sb.Append("Fax,");
sb.Append("FirstName,");
sb.Append("HomePhone,");
sb.Append("LastName,");
sb.Append("Manager,");
sb.Append("Office,");
sb.Append("PersonalSpace,");
sb.Append("PictureURL,");
sb.Append("PreferredName,");
sb.Append("PublicSiteRedirect,");
sb.Append("QuickLinks,");
sb.Append("SID,");
sb.Append("SPS-AboutUs,");
sb.Append("SPS-Birthday,");
sb.Append("SPS-ClaimID,");
sb.Append("SPS-ClaimProviderID,");
sb.Append("SPS-ClaimProviderType,");
sb.Append("SPS-DataSource,");
sb.Append("SPS-DisplayOrder,");
sb.Append("SPS-DistinguishedName,");
sb.Append("SPS-DontSuggestList,");
sb.Append("SPS-Dotted-line,");
sb.Append("SPS-EmailOptin,");
sb.Append("SPS-FormerNames,");
sb.Append("SPS-HireDate,");
sb.Append("SPS-Interests,");
sb.Append("SPS-JobTitle,");
sb.Append("SPS-LastColleagueAdded,");
sb.Append("SPS-LastKeywordAdded,");
sb.Append("SPS-Location,");
sb.Append("SPS-LogoURL,");
sb.Append("SPS-MasterAccountName,");
sb.Append("SPS-MemberOf,");
sb.Append("SPS-MySiteUpgrade,");
sb.Append("SPS-ObjectExists,");
sb.Append("SPS-OWAUrl,");
sb.Append("SPS-Parent,");
sb.Append("SPS-ParentType,");
sb.Append("SPS-PastProjects,");
sb.Append("SPS-Peers,");
sb.Append("SPS-PhoneticDisplayName,");
sb.Append("SPS-PhoneticFirstName,");
sb.Append("SPS-PhoneticLastName,");
sb.Append("SPS-ProxyAddresses,");
sb.Append("SPS-ResourceAccountName,");
sb.Append("SPS-ResourceSID,");
sb.Append("SPS-Responsibility,");
sb.Append("SPS-SavedAccountName,");
sb.Append("SPS-SavedSID,");
sb.Append("SPS-School,");
sb.Append("SPS-Section-BasicInfo,");
sb.Append("SPS-Section-ContactInfo,");
sb.Append("SPS-Section-CustomProperties,");
sb.Append("SPS-Section-Delegation,");
sb.Append("SPS-Section-Details,");
sb.Append("SPS-Section-OrganizationMembers,");
sb.Append("SPS-Section-Preferences,");
sb.Append("SPS-SipAddress,");
sb.Append("SPS-Skills,");
sb.Append("SPS-SourceObjectDN,");
sb.Append("SPS-Statustes,");
sb.Append("SPS-Team-Site,");
sb.Append("SPS-TimeZone,");
sb.Append("Title,");
sb.Append("UserName,");
sb.Append("UserProfile_GUID,");
sb.Append("WebSite,");
sb.Append("WorkEmail,");
sb.Append("WorkPhone");
sb.Append("\r\n");
foreach (UserInfo u in userInfo)
{
sb.Append(u.AboutMe + ",");
sb.Append(u.AccountName + ",");
sb.Append(u.ADGuid + ",");
sb.Append(u.Assistant + ",");
sb.Append(u.CellPhone + ",");
sb.Append(u.Department + ",");
sb.Append(u.Fax + ",");
sb.Append(u.FirstName + ",");
sb.Append(u.HomePhone + ",");
sb.Append(u.LastName + ",");
sb.Append(u.Manager + ",");
sb.Append(u.Office + ",");
sb.Append(u.PersonalSpace + ",");
sb.Append(u.PictureURL + ",");
sb.Append(u.PreferredName + ",");
sb.Append(u.PublicSiteRedirect + ",");
sb.Append(u.QuickLinks + ",");
sb.Append(u.SID + ",");
sb.Append(u.SPSAboutUs + ",");
sb.Append(u.SPSBirthday + ",");
sb.Append(u.SPSClaimID + ",");
sb.Append(u.SPSClaimProviderID + ",");
sb.Append(u.SPSClaimProviderType + ",");
sb.Append(u.SPSDataSource + ",");
sb.Append(u.SPSDisplayOrder + ",");
sb.Append(u.SPSDistinguishedName + ",");
sb.Append(u.SPSDontSuggestList + ",");
sb.Append(u.SPSDottedline + ",");
sb.Append(u.SPSEmailOptin + ",");
sb.Append(u.SPSFormerNames + ",");
sb.Append(u.SPSHireDate + ",");
sb.Append(u.SPSInterests + ",");
sb.Append(u.SPSJobTitle + ",");
sb.Append(u.SPSLastColleagueAdded + ",");
sb.Append(u.SPSLastKeywordAdded + ",");
sb.Append(u.SPSLocation + ",");
sb.Append(u.SPSLogoURL + ",");
sb.Append(u.SPSMasterAccountName + ",");
sb.Append(u.SPSMemberOf + ",");
sb.Append(u.SPSMySiteUpgrade + ",");
sb.Append(u.SPSObjectExists + ",");
sb.Append(u.SPSOWAUrl + ",");
sb.Append(u.SPSParent + ",");
sb.Append(u.SPSParentType + ",");
sb.Append(u.SPSPastProjects + ",");
sb.Append(u.SPSPeers + ",");
sb.Append(u.SPSPhoneticDisplayName + ",");
sb.Append(u.SPSPhoneticFirstName + ",");
sb.Append(u.SPSPhoneticLastName + ",");
sb.Append(u.SPSProxyAddresses + ",");
sb.Append(u.SPSResourceAccountName + ",");
sb.Append(u.SPSResourceSID + ",");
sb.Append(u.SPSResponsibility + ",");
sb.Append(u.SPSSavedAccountName + ",");
sb.Append(u.SPSSavedSID + ",");
sb.Append(u.SPSSchool + ",");
sb.Append(u.SPSSectionBasicInfo + ",");
sb.Append(u.SPSSectionContactInfo + ",");
sb.Append(u.SPSSectionCustomProperties + ",");
sb.Append(u.SPSSectionDelegation + ",");
sb.Append(u.SPSSectionDetails + ",");
sb.Append(u.SPSSectionOrganizationMembers + ",");
sb.Append(u.SPSSectionPreferences + ",");
sb.Append(u.SPSSipAddress + ",");
sb.Append(u.SPSSkills + ",");
sb.Append(u.SPSSourceObjectDN + ",");
sb.Append(u.SPSStatustes + ",");
sb.Append(u.SPSTeamSite + ",");
sb.Append(u.SPSTimeZone + ",");
sb.Append(u.Title + ",");
sb.Append(u.UserName + ",");
sb.Append(u.UserProfile_GUID + ",");
sb.Append(u.WebSite + ",");
sb.Append(u.WorkEmail + ",");
sb.Append(u.WorkPhone);
sb.Append("\r\n");
}
TextWriter tw = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "SharePointUserData.csv");
tw.WriteLine(sb.ToString());
tw.Close();
}
}

public class UserInfo
{
public int counter { get; set; }
public string AboutMe {get;set;}
public string AccountName {get;set;}
public string ADGuid {get;set;}
public string Assistant {get;set;}
public string CellPhone {get;set;}
public string Department {get;set;}
public string Fax {get;set;}
public string FirstName {get;set;}
public string HomePhone {get;set;}
public string LastName {get;set;}
public string Manager {get;set;}
public string Office {get;set;}
public string PersonalSpace {get;set;}
public string PictureURL {get;set;}
public string PreferredName {get;set;}
public string PublicSiteRedirect {get;set;}
public string QuickLinks {get;set;}
public string SID {get;set;}
public string SPSAboutUs {get;set;}
public string SPSBirthday {get;set;}
public string SPSClaimID {get;set;}
public string SPSClaimProviderID {get;set;}
public string SPSClaimProviderType {get;set;}
public string SPSDataSource {get;set;}
public string SPSDisplayOrder {get;set;}
public string SPSDistinguishedName {get;set;}
public string SPSDontSuggestList {get;set;}
public string SPSDottedline {get;set;}
public string SPSEmailOptin {get;set;}
public string SPSFormerNames {get;set;}
public string SPSHireDate {get;set;}
public string SPSInterests {get;set;}
public string SPSJobTitle {get;set;}
public string SPSLastColleagueAdded {get;set;}
public string SPSLastKeywordAdded {get;set;}
public string SPSLocation {get;set;}
public string SPSLogoURL {get;set;}
public string SPSMasterAccountName {get;set;}
public string SPSMemberOf {get;set;}
public string SPSMySiteUpgrade {get;set;}
public string SPSObjectExists {get;set;}
public string SPSOWAUrl {get;set;}
public string SPSParent {get;set;}
public string SPSParentType {get;set;}
public string SPSPastProjects {get;set;}
public string SPSPeers {get;set;}
public string SPSPhoneticDisplayName {get;set;}
public string SPSPhoneticFirstName {get;set;}
public string SPSPhoneticLastName {get;set;}
public string SPSProxyAddresses {get;set;}
public string SPSResourceAccountName {get;set;}
public string SPSResourceSID {get;set;}
public string SPSResponsibility {get;set;}
public string SPSSavedAccountName {get;set;}
public string SPSSavedSID {get;set;}
public string SPSSchool {get;set;}
public string SPSSectionBasicInfo {get;set;}
public string SPSSectionContactInfo {get;set;}
public string SPSSectionCustomProperties {get;set;}
public string SPSSectionDelegation {get;set;}
public string SPSSectionDetails {get;set;}
public string SPSSectionOrganizationMembers {get;set;}
public string SPSSectionPreferences {get;set;}
public string SPSSipAddress {get;set;}
public string SPSSkills {get;set;}
public string SPSSourceObjectDN {get;set;}
public string SPSStatustes {get;set;}
public string SPSTeamSite {get;set;}
public string SPSTimeZone {get;set;}
public string Title {get;set;}
public string UserName {get;set;}
public string UserProfile_GUID {get;set;}
public string WebSite {get;set;}
public string WorkEmail {get;set;}
public string WorkPhone {get;set;}

public List<UserInfo> GetUserInfo()
{
UserGroup userGroup = new UserGroup();
userGroup.UseDefaultCredentials = true;
XmlNode allUsers = userGroup.GetAllUserCollectionFromWeb();
XNode xNode = XDocument.Parse(allUsers.OuterXml);
return (from root in xNode.Document.Elements()
from users in root.Elements()
from user in users.Elements()
let loginName = (string)user.Attribute("LoginName")
let userInfo = GetUserInfoDetails(loginName)
select new UserInfo()
{
AboutMe = userInfo.AboutMe,
AccountName = userInfo.AccountName,
ADGuid = userInfo.ADGuid,
Assistant = userInfo.Assistant,
CellPhone = userInfo.CellPhone,
Department = userInfo.Department,
Fax = userInfo.Fax,
FirstName = userInfo.FirstName,
HomePhone = userInfo.HomePhone,
LastName = userInfo.LastName,
Manager = userInfo.Manager,
Office = userInfo.Office,
PersonalSpace = userInfo.PersonalSpace,
PictureURL = userInfo.PictureURL,
PreferredName = userInfo.PreferredName,
PublicSiteRedirect = userInfo.PublicSiteRedirect,
QuickLinks = userInfo.QuickLinks,
SID = userInfo.SID,
SPSAboutUs = userInfo.SPSAboutUs,
SPSBirthday = userInfo.SPSBirthday,
SPSClaimID = userInfo.SPSClaimID,
SPSClaimProviderID = userInfo.SPSClaimProviderID,
SPSClaimProviderType = userInfo.SPSClaimProviderType,
SPSDataSource = userInfo.SPSDataSource,
SPSDisplayOrder = userInfo.SPSDisplayOrder,
SPSDistinguishedName = userInfo.SPSDistinguishedName,
SPSDontSuggestList = userInfo.SPSDontSuggestList,
SPSDottedline = userInfo.SPSDottedline,
SPSEmailOptin = userInfo.SPSEmailOptin,
SPSFormerNames = userInfo.SPSFormerNames,
SPSHireDate = userInfo.SPSHireDate,
SPSInterests = userInfo.SPSInterests,
SPSJobTitle = userInfo.SPSJobTitle,
SPSLastColleagueAdded = userInfo.SPSLastColleagueAdded,
SPSLastKeywordAdded = userInfo.SPSLastKeywordAdded,
SPSLocation = userInfo.SPSLocation,
SPSLogoURL = userInfo.SPSLogoURL,
SPSMasterAccountName = userInfo.SPSMasterAccountName,
SPSMemberOf = userInfo.SPSMemberOf,
SPSMySiteUpgrade = userInfo.SPSMySiteUpgrade,
SPSObjectExists = userInfo.SPSObjectExists,
SPSOWAUrl = userInfo.SPSOWAUrl,
SPSParent = userInfo.SPSParent,
SPSParentType = userInfo.SPSParentType,
SPSPastProjects = userInfo.SPSPastProjects,
SPSPeers = userInfo.SPSPeers,
SPSPhoneticDisplayName = userInfo.SPSPhoneticDisplayName,
SPSPhoneticFirstName = userInfo.SPSPhoneticFirstName,
SPSPhoneticLastName = userInfo.SPSPhoneticLastName,
SPSProxyAddresses = userInfo.SPSProxyAddresses,
SPSResourceAccountName = userInfo.SPSResourceAccountName,
SPSResourceSID = userInfo.SPSResourceSID,
SPSResponsibility = userInfo.SPSResponsibility,
SPSSavedAccountName = userInfo.SPSSavedAccountName,
SPSSavedSID = userInfo.SPSSavedSID,
SPSSchool = userInfo.SPSSchool,
SPSSectionBasicInfo = userInfo.SPSSectionBasicInfo,
SPSSectionContactInfo = userInfo.SPSSectionContactInfo,
SPSSectionCustomProperties = userInfo.SPSSectionCustomProperties,
SPSSectionDelegation = userInfo.SPSSectionDelegation,
SPSSectionDetails = userInfo.SPSSectionDetails,
SPSSectionOrganizationMembers = userInfo.SPSSectionOrganizationMembers,
SPSSectionPreferences = userInfo.SPSSectionPreferences,
SPSSipAddress = userInfo.SPSSipAddress,
SPSSkills = userInfo.SPSSkills,
SPSSourceObjectDN = userInfo.SPSSourceObjectDN,
SPSStatustes = userInfo.SPSStatustes,
SPSTeamSite = userInfo.SPSTeamSite,
SPSTimeZone = userInfo.SPSTimeZone,
Title = userInfo.Title,
UserName = userInfo.UserName,
UserProfile_GUID = userInfo.UserProfile_GUID,
WebSite = userInfo.WebSite,
WorkEmail = userInfo.WorkEmail,
WorkPhone = userInfo.WorkPhone

}).ToList();
}

private UserInfo GetUserInfoDetails(string loginName)
{
Console.WriteLine(counter++.ToString() + " - " + loginName);
UserInfo userInfo = new UserInfo();
try
{
UserProfileService userProfileService = new UserProfileService();
userProfileService.UseDefaultCredentials = true;
PropertyData[] data = userProfileService.GetUserProfileByName(loginName);
string pictureUrl = GetPropertyData(data, "PictureURL");
userInfo.AboutMe = GetPropertyData(data, "AboutMe");
userInfo.AccountName = GetPropertyData(data, "AccountName");
userInfo.ADGuid = GetPropertyData(data, "ADGuid");
userInfo.Assistant = GetPropertyData(data, "Assistant");
userInfo.CellPhone = GetPropertyData(data, "CellPhone");
userInfo.Department = GetPropertyData(data, "Department");
userInfo.Fax = GetPropertyData(data, "Fax");
userInfo.FirstName = GetPropertyData(data, "FirstName");
userInfo.HomePhone = GetPropertyData(data, "HomePhone");
userInfo.LastName = GetPropertyData(data, "LastName");
userInfo.Manager = GetPropertyData(data, "Manager");
userInfo.Office = GetPropertyData(data, "Office");
userInfo.PersonalSpace = GetPropertyData(data, "PersonalSpace");
userInfo.PictureURL = GetPropertyData(data, "PictureURL");
userInfo.PreferredName = GetPropertyData(data, "PreferredName");
userInfo.PublicSiteRedirect = GetPropertyData(data, "PublicSiteRedirect");
userInfo.QuickLinks = GetPropertyData(data, "QuickLinks");
userInfo.SID = GetPropertyData(data, "SID");
userInfo.SPSAboutUs = GetPropertyData(data, "SPS-AboutUs");
userInfo.SPSBirthday = GetPropertyData(data, "SPS-Birthday");
userInfo.SPSClaimID = GetPropertyData(data, "SPS-ClaimID");
userInfo.SPSClaimProviderID = GetPropertyData(data, "SPS-ClaimProviderID");
userInfo.SPSClaimProviderType = GetPropertyData(data, "SPS-ClaimProviderType");
userInfo.SPSDataSource = GetPropertyData(data, "SPS-DataSource");
userInfo.SPSDisplayOrder = GetPropertyData(data, "SPS-DisplayOrder");
userInfo.SPSDistinguishedName = GetPropertyData(data, "SPS-DistinguishedName");
userInfo.SPSDontSuggestList = GetPropertyData(data, "SPS-DontSuggestList");
userInfo.SPSDottedline = GetPropertyData(data, "SPS-Dotted-line");
userInfo.SPSEmailOptin = GetPropertyData(data, "SPS-EmailOptin");
userInfo.SPSFormerNames = GetPropertyData(data, "SPS-FormerNames");
userInfo.SPSHireDate = GetPropertyData(data, "SPS-HireDate");
userInfo.SPSInterests = GetPropertyData(data, "SPS-Interests");
userInfo.SPSJobTitle = GetPropertyData(data, "SPS-JobTitle");
userInfo.SPSLastColleagueAdded = GetPropertyData(data, "SPS-LastColleagueAdded");
userInfo.SPSLastKeywordAdded = GetPropertyData(data, "SPS-LastKeywordAdded");
userInfo.SPSLocation = GetPropertyData(data, "SPS-Location");
userInfo.SPSLogoURL = GetPropertyData(data, "SPS-LogoURL");
userInfo.SPSMasterAccountName = GetPropertyData(data, "SPS-MasterAccountName");
userInfo.SPSMemberOf = GetPropertyData(data, "SPS-MemberOf");
userInfo.SPSMySiteUpgrade = GetPropertyData(data, "SPS-MySiteUpgrade");
userInfo.SPSObjectExists = GetPropertyData(data, "SPS-ObjectExists");
userInfo.SPSOWAUrl = GetPropertyData(data, "SPS-OWAUrl");
userInfo.SPSParent = GetPropertyData(data, "SPS-Parent");
userInfo.SPSParentType = GetPropertyData(data, "SPS-ParentType");
userInfo.SPSPastProjects = GetPropertyData(data, "SPS-PastProjects");
userInfo.SPSPeers = GetPropertyData(data, "SPS-Peers");
userInfo.SPSPhoneticDisplayName = GetPropertyData(data, "SPS-PhoneticDisplayName");
userInfo.SPSPhoneticFirstName = GetPropertyData(data, "SPS-PhoneticFirstName");
userInfo.SPSPhoneticLastName = GetPropertyData(data, "SPS-PhoneticLastName");
userInfo.SPSProxyAddresses = GetPropertyData(data, "SPS-ProxyAddresses");
userInfo.SPSResourceAccountName = GetPropertyData(data, "SPS-ResourceAccountName");
userInfo.SPSResourceSID = GetPropertyData(data, "SPS-ResourceSID");
userInfo.SPSResponsibility = GetPropertyData(data, "SPS-Responsibility");
userInfo.SPSSavedAccountName = GetPropertyData(data, "SPS-SavedAccountName");
userInfo.SPSSavedSID = GetPropertyData(data, "SPS-SavedSID");
userInfo.SPSSchool = GetPropertyData(data, "SPS-School");
userInfo.SPSSectionBasicInfo = GetPropertyData(data, "SPS-Section-BasicInfo");
userInfo.SPSSectionContactInfo = GetPropertyData(data, "SPS-Section-ContactInfo");
userInfo.SPSSectionCustomProperties = GetPropertyData(data, "SPS-Section-CustomProperties");
userInfo.SPSSectionDelegation = GetPropertyData(data, "SPS-Section-Delegation");
userInfo.SPSSectionDetails = GetPropertyData(data, "SPS-Section-Details");
userInfo.SPSSectionOrganizationMembers = GetPropertyData(data, "SPS-Section-OrganizationMembers");
userInfo.SPSSectionPreferences = GetPropertyData(data, "SPS-Section-Preferences");
userInfo.SPSSipAddress = GetPropertyData(data, "SPS-SipAddress");
userInfo.SPSSkills = GetPropertyData(data, "SPS-Skills");
userInfo.SPSSourceObjectDN = GetPropertyData(data, "SPS-SourceObjectDN");
userInfo.SPSStatustes = GetPropertyData(data, "SPS-Statustes");
userInfo.SPSTeamSite = GetPropertyData(data, "SPS-Team-Site");
userInfo.SPSTimeZone = GetPropertyData(data, "SPS-TimeZone");
userInfo.Title = GetPropertyData(data, "Title");
userInfo.UserName = GetPropertyData(data, "UserName");
userInfo.UserProfile_GUID = GetPropertyData(data, "UserProfile_GUID");
userInfo.WebSite = GetPropertyData(data, "WebSite");
userInfo.WorkEmail = GetPropertyData(data, "WorkEmail");
userInfo.WorkPhone = GetPropertyData(data, "WorkPhone");

}
catch (System.Web.Services.Protocols.SoapException ex)
{
//It appears that an exception gets generated for users
//that have not logged into SharePoint yet.
//They must logon for a profile to be created.
if (!ex.Message.Contains("A user with the account name"))
{
throw;
}
}
return userInfo;
}

private string GetPropertyData(PropertyData[] data, string columnName)
{
//Delimit properties with a semi-colon or comma.
string returnValue = string.Empty;
try
{
returnValue = string.Join("; ",
(from v in
((PropertyData)data.Where(x => x.Name == columnName).Single()).Values
select v.Value.ToString()).ToArray());
}
catch (Exception ex)
{

}
return returnValue;
}
}
}

2 comments:

  1. Thanks for sharing.

    One more thing, if I'm not mistaken, it only pulls out the user profile on the site-level, not from the entire UP service-level though.

    Please keep up with the good-work!

    All best,
    Eric

    ReplyDelete
  2. Good Article...Thanks for sharing

    ReplyDelete