Export “User Information List” to Excel in SharePoint

At one point of 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 user information list.

  • Navigate to Site Settings >> People and Groups. Now your browser URL should be something like “http://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:
    http://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:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Variables
$SiteUrl="http://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 is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

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

  • December 12, 2019 at 9:23 AM

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

    Reply
  • September 9, 2019 at 4:25 PM

    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
  • December 17, 2018 at 8:42 PM

    This was awesome!

    Reply
  • February 26, 2018 at 5:57 PM

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

    Reply
  • August 5, 2017 at 10:33 PM

    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
  • July 24, 2017 at 8:35 AM

    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
  • March 24, 2017 at 5:10 PM

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

    Reply
  • April 22, 2016 at 3:27 AM

    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