0

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 : screenshot of successful GridView with the information I need

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. Screenshot of the information I get in the CSV file

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 :

Expected result in excel-transformed csv

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 :

Bad format result

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

Thanks a lot !!

--- End of Edit ---

0

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:

enter image description here

| improve this answer | |
  • thank you @Chelsea_MSFT, but > You need to run an extra command to split the collection into strings is exactly what I was asking :) I would like to have at least a string with carriage return and on each line the site's URL. All this string in a single cell of the csv. How do you think it is doable ? (I am very new in Powershell, sorry) – Gaelle Jul 28 at 11:54
  • @Gaelle Sorry but that is something I am having trouble with too. Did you have a chance to look into the other sample I provide? The “foreach” function is what we need to split the collection, I’m just having trouble completing this action with the original script you provide. – Chelsea_MSFT Jul 29 at 1:43
  • finally I could do something but I still have trouble with carriage return inside a csv cell. I think it is not supported by the format. I did the following : $carriageReturn = "rn" $sitesList = "" foreach($site in $_.Sites) { $sitesList+=$site.URL $sitesList+=$carriageReturn } Unfortunately, the URL are taking new lines – Gaelle Jul 29 at 14:29
  • @Gaelle Where should this snippet go in your original script? I think the parameters “$carriageReturn = "rn" $sitesList = ""” does the trick to put the URLs into new lines, right? – Chelsea_MSFT Jul 30 at 6:42
  • @Chelsy, I have edited my question so you can see the snippet in the original script. The return carriage is not well interpreted in the csv, because it is creating ne csv lines.not new lines inside the cell. – Gaelle Jul 30 at 9:26

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.