Export sharepoint user profile properties to excel using C#

This is a short guide on how to export the properties from a Sharepoint 2010 user profile database into a spreadsheet using a simple C# script.

Our end result is to get this export into a format something like:

1. Open Visual Studio
2. Create new project - Visual C# > Consolse Application

1. Name it UserProfileDataExtract
2. Add the missing references below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
namespace UserProfileDataExtract
{
    class Program
    {
        static void Main(string[] args)
        {

            Console.WriteLine("Accessing mysite.......");
 

            //This is the mysite location
            SPSite site = new SPSite("http://mysite/people");

            Console.WriteLine("Site Accessed.......");

            StringBuilder sb = new StringBuilder(); 
 

            //Creating headers in the first row
            sb.Append("Username,");
            sb.Append("Firstname,");
            sb.Append("Lastname,");
            sb.Append("Phone,");
            sb.Append("Mobile,");


            sb.Append("Email,");
            sb.Append("Manager,");
            sb.Append("JobTitle,");
            sb.Append("\r\n");
  

            Console.WriteLine("Using Site.......");
            using (site)
            {
                Console.WriteLine("Get Context and profile manager.......");
                SPServiceContext context = SPServiceContext.GetContext(site);
                UserProfileManager profileManager = new UserProfileManager(context);

                //Loop through all the user profiles in the sharepoint database
                Console.WriteLine("Starting Loop.......");
                foreach (UserProfile profile in profileManager)
                {
                   //Retrieve the profileimage values for current user
                    string strName = Convert.ToString(profile[PropertyConstants.UserName].Value);
                    string FName = Convert.ToString(profile[PropertyConstants.FirstName].Value);
                    string LName = Convert.ToString(profile[PropertyConstants.LastName].Value);
                    string Phone = Convert.ToString(profile[PropertyConstants.WorkPhone].Value);
                    string Mobile = Convert.ToString(profile[PropertyConstants.CellPhone].Value);
                    string Email = Convert.ToString(profile[PropertyConstants.WorkEmail].Value);
                    string Manager = Convert.ToString(profile[PropertyConstants.Manager].Value);
                    string JobTitle = Convert.ToString(profile[PropertyConstants.JobTitle].Value);

                    Console.WriteLine("Processing: " + strName);
sb.Append(strName + ",");
                    sb.Append(FName + ",");
                    sb.Append(LName + ",");
                    sb.Append(Phone + ",");
                    sb.Append(Mobile + ",");
                    sb.Append(Email + ",");
                    sb.Append(Manager + ",");
                    sb.Append(JobTitle.Replace(",", "-") + ",");

                    sb.Append("\r\n");

               }
          Console.WriteLine("Finished profile retrieval");
          Console.WriteLine("Writing to csv file");

          //Write the long string to a csv file and save it to the desktop


          TextWriter tw = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "SharePointUserData.csv");
          tw.WriteLine(sb.ToString());
          tw.Close(); 

          } 

      }//End Main
    }//End class
 }