kwizcom banner advertisement

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!Export User Information List to Excel in SharePoint
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.
Export "User Information List" to Excel in SharePoint Export "User Information List" to Excel in SharePoint Reviewed by Salaudeen Rajack on 4:12 PM Rating: 5

6 comments:

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

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

    ReplyDelete
  3. 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.

    ReplyDelete
  4. 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.

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

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.