Monday, March 30, 2015

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:

Export user information list to excel in SharePoint 2013:
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 "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!
Alternate method:
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.



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

1 comment :

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

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...