SharePoint Search Results to CSV

I recently came across the need to provide a report of all documents, list items, pages, etc., from a SharePoint 2010 farm, that contained a certain set of words. Search does this, however, there is no out-of-the-box method to export your search results from your site to a spreadsheet. Sure you could conduct your search and copy and paste what’s on the screen, but there’s the paging cursor to contend with. If your search yields anything above just a couple of pages you’re looking at a lengthy, and very boring, task.

PowerShell to the rescue! I’ve found myself uttering that phrase a lot lately. PowerShell saves me a lot of time. Being mostly developer-oriented and focused I often cracked open Visual Studio to knock out console apps left and right to perform a number of specialized tasks using the SharePoint Object Model. However, with my latest engagement being primarily focused on the administration side of things, I had finally sat down and really dug into PowerShell, something I’ve been meaning to do for quite some time. PowerShell makes short work of tasks such as the one demonstrated here.

After doing some research I came up with the following reusable script (this should work in SharePoint 2013 as well):

param ([string]$searchString = “$(Read-Host ‘Enter a search string:’)”,[Int]$searchRowLimit = “$(Read-Host ‘Row Limit (0 for unlimited):’)”,[string]$searchResults = “$(Read-Host ‘Path for results(CSV):’)”

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq ‘Microsoft.SharePoint.Powershell’}
if ($snapin -eq $null)
Write-Host “Loading SharePoint Powershell Snapin`r`n”
Add-PSSnapin “Microsoft.SharePoint.Powershell”

$proxy = Get-SPEnterpriseSearchServiceApplicationProxy -Identity “Search Service Application”
$query = New-Object $proxy
$query.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
$query.RowLimit = $searchRowLimit
$query.QueryText = $searchString
$resultTableColl = $query.Execute()
$resultTable = $resultTableColl.Item([Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults)
$resDataTable = $resultTable.Table
$resDataTable.Rows | Select-Object -Property Path,Title | Export-Csv -Path $searchResults -Encoding unicode

What this script amounts to is a PowerShell-based method of conducting non-paging cursored searches on your SharePoint farm. You are prompted for your search criteria, same text you would type into your site’s searchbox. You are then prompted for a row limit, and can provide the value 0 to just return everything. And, finally, the path of a filename you’d like to write the results to (in CSV format).

This script first gets a reference to your Search Service Application Proxy using the Get-SPEnterpriseSearchServiceApplicationProxy cmdlet. You may need to update the Identity value of this command if you named your Search Service Application differently. Then, using that proxy object we create a new KeywordQuery object. The ResultTypes for this query are set to the results most relevant to our search criteria. Assign the row limit based on the user input collected earlier. Assign the query text that had been entered. And then execute the query.

Depending on how large your farm is, this could take a minute or two… especially if you’re going with unlimited rows.

Next, we do some digging into the results object for the underlying data table, and use the Select-Object cmdlet to output just the specific properties we want (I went with the Path and Title) before piping them into Export-Csv.

For more information about gaining a competitive advantage with digital transformation, contact Red Level today.

Related Posts

Stay Up-To-Date.
Subscribe to The Red Letter

– Red Level's quarterly email featuring the people, ideas and events IT pros need to know.

Modern Workplace Webinar Series

How does Carhartt use technology to foster better communication and collaboration? Join us as we learn from Michigan business leaders – what works, what to avoid, and what's next.
[ read more ]