SP2013 and Powershell : Export mapping between SPContentDatabase and Sites to CSV

Question

TL;DR :

I'm trying to get all the SPSites (preferably only the URLs) contained by each SPContentDatabases mounted on each one of my WebApps into a csv. In the edit, I explain how I could break the Collection into a string, but still struggle to make this string look like a list.

Context

I am trying to generate a csv by WebApplication. This CSV will contain a mapping between the SPContentDatabases and the sites they contain, by WebApplication. It will display WebApplication name, SPContentDatabase name, the CurrentSiteCount property and all the Site's URL in each SPContentDatabase.

What I have and is working

To today, I have succeeded by having several Out-GridView pop-up such as :

Here is the code I used to generate the gridviews (note that this will prompt me several pop-ups as I have several WebApplications):

If ((Get-PsSnapin |?{$_.Name -eq "Microsoft.SharePoint.PowerShell"})-eq $null)

{

    Write-Host -ForegroundColor White " - Loading SharePoint Powershell Snapin"

    Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction Stop

}



$ListItemCollection = @()

Get-SPWebApplication | % {

Write-Host "Working on $($_.DisplayName)"

$outfile = [System.String]::format("C:\temp\mapping_SPContentDB\SPContentDBMappingOf__{0}.csv", $_.DisplayName)

Write-Host "Le fichier sera généré :$outfile"

Get-SPContentDatabase -WebApplication $($_.DisplayName)| %{
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -Name "WebApp" -Value $_.WebApplication
$ExportItem | Add-Member -MemberType NoteProperty -Name "SPContentDB" -Value $_.Name
$ExportItem | Add-Member -MemberType NoteProperty -Name "Total Sites" -Value $_.CurrentSiteCount
$ExportItem | Add-Member -MemberType NoteProperty "sites" -Value $_.Sites
$ListItemCollection += $ExportItem
}

$ListItemCollection | Out-GridView -PassThru
}

So, as you can see in through the Out-GridView format, I get my results. sites being an object of different Sites properties. (at most, I would love to only have say the URLs of the different sites and subsites contained by the SPContentDatabases.

What is my problem

Now, I would like to export these results into a .CSV, because I need one of my key-user to be able to read it (and play with it if he wants to). I only modified the last line

$ListItemCollection | Out-GridView -PassThru }

By

$ListItemCollection | Export-Csv -Path $outfile -NoTypeInformation }

Unfortunately, in the final CSVs, I am not getting the Sites information correctly. Instead, I get "Microsoft.SharePoint.Administration.SPSiteCollection" which is the class of the object.

Now, i am asking you :

How to turn this into a csv table inside a csv table ? Even if each line is a new SP SIte URL !

Thanks a lot for reading ;)

---- EDIT 30/07/2020 ---

Thanks to @chelsea_MSFT, I found how to break the collection into a string, except now I have a problem with the format of the cell containing the sites URLs.

The only problem I still have is that I would like to do a carriage return inside the cell of my csv. Like, the csv will be transformed into a xlsx, so I would like, at the end, that the lines are broken to give a good looking list, like the following image :

The following code got me the collection down to a list, but the format is not good.

If ((Get-PsSnapin |?{$_.Name -eq "Microsoft.SharePoint.PowerShell"})-eq $null)

{

    Write-Host -ForegroundColor White " - Loading SharePoint Powershell Snapin"

    Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction Stop

 }



$ListItemCollection = @()
$CarriageReturn = "`r`n"

Get-SPWebApplication | % {

Write-Host "Working on $($_.DisplayName)"

$outfile = [System.String]::format("C:\temp\mapping_SPContentDB\SPContentDBMappingOf__{0}.csv", $_.DisplayName)

Write-Host "Le fichier sera généré :$outfile"

Get-SPContentDatabase -WebApplication $($_.DisplayName)| %{
$sitesList = ""
    foreach($site in $_.Sites) {
            $sitesList+=$site.URL
            $sitesList+=$CarriageReturn 
    }
    
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -Name "WebApp" -Value $_.WebApplication
$ExportItem | Add-Member -MemberType NoteProperty -Name "SPContentDB" -Value $_.Name
$ExportItem | Add-Member -MemberType NoteProperty -Name "Total Sites" -Value $_.CurrentSiteCount
$ExportItem | Add-Member -MemberType NoteProperty "Sites" -Value $sitesList
$ListItemCollection += $ExportItem
}

$ListItemCollection | Export-Csv -Path $outfile -NoTypeInformation }

Here is the visual result of that code in a csv, where I converted the datas :

So, any ideas on how I could insert carriage returns inside a data cell in a csv ?

Thanks a lot !!

--- End of Edit ---

Answer 1

The property value for SPContentDatabase.Sites is SPSiteCollection, which is a collection of SPSite objects or site collections.

That is why you are getting Microsoft.SharePoint.Administration.SPSiteCollection as results. You need to run an extra command to split the collection into strings.

And for the CSV file format, I have tested with the script you provide and the spreadsheets turn out well. The Export-Csv cmdlet is all you need to format a csv file.

---

UPDATE:

This the script I use to test:

If ((Get-PsSnapin |?{$_.Name -eq "Microsoft.SharePoint.PowerShell"})-eq $null) {
    Write-Host -ForegroundColor White " - Loading SharePoint Powershell Snapin"
    Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction Stop
}

$ListItemCollection = @()
$CarriageReturn = "`r`n"

Get-SPWebApplication | % {
    Write-Host "Working on $($_.DisplayName)"
    $outfile = [System.String]::format("C:\Temp\mapping_SPContentDB\SPContentDBMappingOf__{0}.csv", $_.DisplayName)
    Write-Host "Le fichier sera généré :$outfile"

    Get-SPContentDatabase -WebApplication $($_.DisplayName)| %{
        $sitesList = ""
        foreach($site in $_.Sites) {
            $sitesList+=$site.URL
            $sitesList+=$CarriageReturn 
        }
            
        $ExportItem = New-Object PSObject
        $ExportItem | Add-Member -MemberType NoteProperty -Name "WebApp" -Value $_.WebApplication
        $ExportItem | Add-Member -MemberType NoteProperty -Name "SPContentDB" -Value $_.Name
        $ExportItem | Add-Member -MemberType NoteProperty -Name "Total Sites" -Value $_.CurrentSiteCount
        $ExportItem | Add-Member -MemberType NoteProperty "Sites" -Value $sitesList
        $ListItemCollection += $ExportItem
    }

    $ListItemCollection | Export-Csv -Path $outfile -NoTypeInformation
}

And this is the output: