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

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

By Global SharePoint in PowerShell, SharePoint on
One comment

 49,766 total views,  2 views today

In this 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

  • 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 user e-mail address from list Item by ID in SharePoint using PowerShell.
  • How to get SPUser by user ID in 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 the 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>'

Summary:

Hence in this PowerShell script, we have learned the below with respect to exporting the user formation list data to 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 list Item by ID using PowerShell in SharePoint.
  • How to get SPUser by user ID in PowerShell.

You may also like the following SharePoint PowerShell tutorials:

 

Tags: , , , , , , ,

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

  1. Pingback: How to replace root site in SharePoint Online from Microsoft 365? | Global SharePoint Diary