I got this pnp PowerShell to retrieve hub sites and associated sites. Can someone please help on how to get the output to a csv with the header? Right now it's writing to the host.

List all sites being a hub site or associate to a hub site

$results = Submit-PnPSearchQuery -Query 'contentclass=sts_site' -RefinementFilters 'departmentid:string("{*",linguistics=off)' -TrimDuplicates $false -SelectProperties @("Title","Path","DepartmentId","SiteId") -All -RelevantResults

Filter out the hub sites

$hubSites = $results |? { $_.DepartmentId.Trim('{','}') -eq $_.SiteId  }

Loop over the hub sites

foreach( $hub in $hubSites ) {
    Write-Host $hub.Title - $hub.Path -ForegroundColor Green
    # Filter out sites associated to the current hub
    $associatedSites = ($results |? { $_.DepartmentId -eq $hub.DepartmentId -and $_.SiteId -ne $hub.SiteId })
    foreach($site in $associatedSites) {
        Write-Host "`t"$site.Title - $site.Path -ForegroundColor Yellow

Export-csv is your friend , both for exporting listitems and custom objects : https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6


A demo about exporting SharePoint list data to CSV using PowerShell for your reference:

 #Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items where Status is "In Progress"
 $list.Items |  Where-Object { $_["Status"] -eq "In Progress"} | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Status" -value $_["Status"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Priority" -value $_["Priority"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.txt" -NoTypeInformation                       

#Dispose the web Object

You can change it as your requirement.

Export SharePoint List Items to CSV using PowerShell

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.