Export SharePoint user information list to CSV(Excel) file using PowerShell

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

One comment

 51,232 total views,  7 views today

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.

Export SharePoint user information list to CSV(Excel) file using PowerShell
Export SharePoint user information list to CSV(Excel) file using PowerShell
#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:

 

About Post Author

1 comments on “Instantly Export SharePoint user information list to CSV(Excel) file using PowerShell in 2 steps”

Do you have a better solution or question on this topic? Please leave a comment