Export “User Information List” to Excel in SharePoint

At one point in time, I needed to export all users of a site collection to Excel, and my trick to export user information list to Excel as follows:

How to Export user information list to excel in SharePoint?

Follow these steps to export the user information list.

  • Navigate to Site Settings >> People and Groups. Now your browser URL should be something like “https://SHAREPOINT-SITE.com/_layouts/15/start.aspx#/_layouts/15/people.aspx?MembershipGroupId=22”. Just replace the value for “MembershipGroupId” to “0” to get the “All People” view which lists all users of the site (which is nothing but “User Information List”).
  • From here, Get the GUIDs of your User Information List and view. Here is How to Get the GUID of SharePoint list or View?
  • Replace “List GUID” and “View GUID” of yours in this URL and copy-paste it in the browser:
    https://SHAREPOINT-SITE-URL.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={LIST-GUID}&View={VIEW-GUID}&CacheControl=1
  • Navigate to the above URL, it should open the query, launch Microsoft Excel, and Import all users from User Information List to Excel!Export User Information List to Excel in SharePoint

Export User Information List to CSV using PowerShell:

Here is the PowerShell script to export the SharePoint User Information List to a CSV:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Variables
$SiteUrl="https://intranet.crescent.com"
$OutPutFile = "C:\UserInfoList.csv" 

#Get Web and User Information List
$web = Get-SPWeb $siteUrl
$UserInfoList = $Web.Site.RootWeb.Lists["User Information List"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()
 
 #Get All List items where Status is "In Progress"
 $UserInfoList.Items | foreach {
 $ExportItem = New-Object PSObject 
 $ExportItem | Add-Member -MemberType NoteProperty -name "User Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job Title" -value $_["Job Title"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "About Me" -value $_["About Me"]
 
 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
$ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation  
Write-host "User Information List Exported to $($OutputFile) for site $($SiteURL)"

$web.Dispose()

This exports all users and groups of the site collection to CSV file.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

9 thoughts on “Export “User Information List” to Excel in SharePoint

  • Awesome sir, your article saved me half day.. Thank you.

    Reply
  • Thanks for sharing!!! I need to filter user account. I need to display only active users. I dont need to display group emails. How to apply couple of filters to the user properties in this script !!!Thanks in advance

    Reply
  • This was awesome!

    Reply
  • Hurrah! In the end I got a webpage from where I know how to really take valuable data concerning my sttudy and knowledge.

    Reply
  • I’ve read some excellent stuff here. Certainly worth bookmarking for revisiting.

    I surprise how much effort you set to make one of these great informative site.

    Reply
  • Very efficiently written story. It will be helpful to anyone who usess it,
    as well as myself. Keep up the good work –
    i will definitely read more posts.

    Reply
  • when I run the command it only exports the first 500 how can I get the entire list?

    Reply
  • Salaudeen … you are the man! .searched for user information list export and you have already done…Thanks a ton for sharing!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *