Instantly Export SharePoint user information list to CSV(Excel) file using PowerShell in 2 steps

In this “Export SharePoint user information list” tutorial, we will learn how we can export the SharePoint user information list to CSV or excel file using the PowerShell script in SharePoint Online/2013/2016/2019.

Key Highlights: Export SharePoint user information list

• How to export the user information list in SharePoint 2013/2016/2019 using the PowerShell script.
• How to export the user information list SharePoint online using the PowerShell script.
• How to get a user e-mail address from list Item by ID in SharePoint using PowerShell.
• How to get SPUser by user ID in PowerShell.

SharePoint user information list PowerShell – PowerShell script to export the user information list in SharePoint 2013/2016/2019/Online

Using the below PowerShell script we can export the users from SharePoint ‘User Information’ list to CSV or Excel file using PowerShell script.

#The below script is used to extract the users from the user information list using the PowerShell programmatically.
cls

$PSshell = Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorVariable err -ErrorAction SilentlyContinue
if($PSshell -eq $null)
{
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$fileName = "User Information Report"
#'yyyyMMddhhmm yyyyMMdd
$enddate = (Get-Date).tostring("yyyyMMddhhmmss")
#$filename = $enddate + '_VMReport.doc'
$logFileName = $fileName +"_"+ $enddate+"_Log.txt"
$invocation = (Get-Variable MyInvocation).Value
$directoryPath = Split-Path $invocation.MyCommand.Path

$directoryPathForLog=$directoryPath+"\"+"LogFiles"
if(!(Test-Path -path $directoryPathForLog))
{
New-Item -ItemType directory -Path $directoryPathForLog
#Write-Host "Please Provide Proper Log Path" -ForegroundColor Red
}
#$logPath = $directoryPath + "\" + $logFileName

$logPath = $directoryPathForLog + "\" + $logFileName

$isLogFileCreated = $False

function Write-Log([string]$logMsg)
{
if(!$isLogFileCreated){
Write-Host "Creating Log File..."
if(!(Test-Path -path $directoryPath))
{
Write-Host "Please Provide Proper Log Path" -ForegroundColor Red
}
else
{
$script:isLogFileCreated = $True
Write-Host "Log File ($logFileName) Created..."
[string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
Add-Content -Path $logPath -Value $logMessage
}
}
else
{
[string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
Add-Content -Path $logPath -Value $logMessage
}
}

#The below function is used to extract the users from the user information list which takes SiteURL as paramter.

function ExtractSPuserInformationListToCSV()
{

param
(
[Parameter(Mandatory=$true)] [string] $SiteURL

)

$web = Get-SPWeb $SiteURL
$list = $web.Lists["User Information List"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery

$CamlQueryString = '<Query><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query>'

$spQuery.Query = $CamlQueryString

$userInformationListItemsColls = $list.GetItems($spQuery)

foreach($oneUserInInformationList in $userInformationListItemsColls)
{

Write-Host $oneUserInInformationList.Title
if (![string]::IsNullOrEmpty($oneUserInInformationList["ows_Name"]))
{

$userData = Get-SPUser -Web $web.URL -Identity $oneUserInInformationList["ows_Name"]

$UserInformationlistItemData = @{
"Display Name" = $userData.DisplayName
"User Login" = $userData.UserLogin
"Email" = $userData.Email
"Name" = $userData.Name

}

}
New-Object PSObject -Property $UserInformationlistItemData

}

$web.Dispose();
return $UserInformationlistItemData
}

####################Testing - calling the function########################################################

try
{

#ExtractSPuserInformationListToCSV "Your site URL" | Out-GridView #Your site collection URL
ExtractSPuserInformationListToCSV "Your site URL" | Export-Csv -NoTypeInformation -Path "Your CSV file path"; #Your site collection URL

$message="The user information list inventory generation has been completed successfully."
Write-Host $message -BackgroundColor Green

}
catch
{
$ErrorMessage = $_.Exception.Message +"in the user information list inventory generation script!: "
Write-Host $ErrorMessage -BackgroundColor Red
Write-Log $ErrorMessage
}
####################Testing - calling the function ends here####################################

In the above $CamlQueryString is as below:

$CamlQueryString = '<Query><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query>'

Can we add a user to the user information list in SharePoint (add user to user information list in SharePoint online)?

No, we cannot add a user to the user information list directly in SharePoint. Whenever we grant access to a user on the SharePoint site/list/library or any other resource, the user automatically gets added to this user information hidden list which means the list is used to store the details of the users we grant any permissions – this is by the design of SharePoint.

How to access the user information list SharePoint URL?

The user information list in SharePoint is a hidden list – we can access the user information list using the below URL

yoursiteURL/_catalogs/users/simple.aspx

Example:

https://globalsharepoint2020.sharepoint.com/_catalogs/users/simple.aspx

Summary: Export SharePoint user information list

Hence in this PowerShell script, we have learned the below with respect to exporting the user formation list data to a CSV file:

• How to export the user information list in SharePoint 2013/2016/2019 using the PowerShell script.
• How to export the user information list SharePoint online using the PowerShell script.
• How to get a user e-mail address from a list Item by ID using PowerShell in SharePoint.
• How to get SPUser by user ID in PowerShell.

See Also: SharePoint PowerShell tutorial

You may also like the following SharePoint PowerShell tutorials:

• [Verified] Get all webparts from pages in a site using PowerShell in SharePoint Online
• Get document library inventory report in SharePoint using PowerShell script
• How to start SharePoint list workflow using PowerShell
• How to hide quick launch menu in SharePoint online using PnP PowerShell
• Edit user Permission is greyed Out SharePoint Online
• Get workflow inventory from SharePoint online using PowerShell CSOM
• Create a modern team site using PnP PowerShell in SharePoint
• In 2 steps convert a classic SharePoint page to modern using PnP
• SharePoint Online: Delete All Files from document library for the given date – PowerShell CSOM
• Create SharePoint online list using PnP provisioning template
• SharePoint Automation: PowerShell script to get remote server information
• Office 365: Retrieve hub sites and associated sites using PnP Powershell
• SharePoint Online Automation – O365 – Upload files to document library using PowerShell CSOM
• SharePoint Online Automation – O365 – Create multiple items in a list using PowerShell CSOM
• SharePoint Online Automation – O365 – Update document library metadata using PowerShell CSOM
• [Solved] Fix the “The term ‘Get-SPWeb’ is not recognized as the name of a cmdlet, function” PowerShell error
• Introduction to SharePoint information architecture

About Post Author

SP Doctor

Your SharePoint and Power Platform Doctor and Guide, sharing expertise that unlocks the true potential of the Microsoft 365 ecosystem.

See author's posts