Export User Profiles to csv

Summary

Sometimes you will have to export all or a sub set of the account in the User Profiles in order to do some validation or cleaning. This is where this script sample comes in handy. The sample is an update of an existing script by Salaudeen Rajack , see https://www.sharepointdiary.com/2017/08/sharepoint-online-export-user-profile-properties-to-csv-using-powershell.html#ixzz7vvoXWaLr

Example Screenshot

PnP PowerShell
CLI for Microsoft 365


$adminsiteUrl = "https://contoso-admin.sharepoint.com/"
$conn = Connect-PnPOnline -Url $adminsiteUrl -Interactive -ReturnConnection
Connect-AzAccount
$AllUsers = Get-AzADUser 
$Counter = 0
$AllUsers.Count
$UserProfileData = @()
$emaildomain = "@contoso.com"


ForEach($User in $AllUsers)
{
        #filter out those account you dont need
        if($User.ObjectType -ne "User")
        {
            continue
        }
        # exclude those without an email address that matches this domain
        if($null -eq $User.Mail -or $User.Mail.IndexOf($emaildomain) -eq -1 )
        {
            continue
        }
        
        
        Write-host "`nGetting User Profile Property for: $($User.UserPrincipalName)" -f Yellow
        #Get the User Property value from SharePoint 
        try 
        {
            $UserProfile = Get-PnPUserProfileProperty -Account ($User.UserPrincipalName) -Connection $conn
            $UserProfile.UserProfileProperties["Department"]
            
            # Yet another option to exclude account from the export. Here we exclude account without a value in the Department field
            if($null -eq $UserProfile.UserProfileProperties["Department"] -or $UserProfile.UserProfileProperties["Department"] -eq "")
            {
                continue
            }
            #Get User Profile Data
            $UserData = New-Object PSObject
            ForEach($Key in $UserProfile.UserProfileProperties.Keys)
            { 
                $UserData | Add-Member NoteProperty $Key($UserProfile.UserProfileProperties[$Key])
            }
            $UserProfileData += $UserData
            $Counter++
            Write-Progress -Activity "Getting User Profile Data..." -Status "Getting User Profile $Counter of $($AllUsers.Count)" -PercentComplete (($Counter / $AllUsers.Count)  * 100)
        
        }
        catch 
        {
            Write-Host $_.Exception.Message
            
        }     
        

}
#Export the data to CSV
$CSVPath = "C:\temp\UPAAccounts.csv"
$UserProfileData | Export-Csv $CSVPath -Encoding utf8BOM -Delimiter "|"
   
write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath

Check out the PnP PowerShell to learn more at: https://aka.ms/pnp/powershell

The way you login into PnP PowerShell has changed please read PnP Management Shell EntraID app is deleted : what should I do ?


m365 login --authType deviceCode

# Get all users and their properties, add properties as needed with --properties and separate with comma
m365 aad user list --properties "displayName,mail"

# Get all users and their properties and export to csv
m365 aad user list --output csv --properties "displayName,mail,givenName,jobTitle,mail,mobilePhone,officeLocation,preferredLanguage,surname,userPrincipalName,id" > users.csv

# Get current user with all properties
m365 aad user get --id "@meId"

# get any user with all properties
m365 aad user get --id "UserID"

Check out the CLI for Microsoft 365 to learn more at: https://aka.ms/cli-m365

Important changes coming to the way you login into CLI for Microsoft 365 (effective 9th September 2024) see Changes in PnP Management Shell registration in Microsoft 365

Contributors

Author(s)
Kasper Larsen
Valeras Narbutas

Disclaimer

THESE SAMPLES ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.