Exporting ALL fields and contents in a List to .CSV in Powershell

Question

So I've been trying for the past 4 days now to write a full automated Powershell script that will connect to SharePoint Online, automatically login with credentials (with password located in a file), locate a List Title, and export all fields (Name, Description, Owner, etc.) and items in each field.

I believe it's just about there, but I'm having a problem with the foreach loop returning everything. This is making me pull my hair out and any help would be more than greatly appreciated.

Here's the full code (some parts found online and some parts hand coded):

#Load SharePoint CSOM Assemblie
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Defining Variables
$SiteUrl = "https://site.sharepoint.com"
$ListName = "ListTitle"
$Path = "C:\Users\Username\Desktop\filename.csv"
$UserName = "[email protected]"
$Password = Get-Content "C:\Users\Username\Desktop\password.txt" | ConvertTo-SecureString -Force

#Automated Login Credentials
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $password) 
$Context.Credentials = $credentials 

#Retrive the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$context.Load($ListItems)
$context.ExecuteQuery()

# Turn item into a catch array
$ListItemCollection = @()

#Fetch each list item value to export to excel
$ListItems | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]

#Add the object with above properties to the Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $Path -NoTypeInformation

Write-host "Deed has been done!"

So I can name all fields manually in the foreach loop, but is there a way to just pull everything instead?

Thanks so much!

Answer 1

Try :

$ListItems | % {
    $propertiesValues = New-Object PSObject;
    $currentItem = $_;
    $_.FieldValues.Keys | ? {$_ -ne "MetaInfo"} | % {  
           Add-Member -InputObject $propertiesValues -MemberType NoteProperty -Name $_ -Value $currentItem[$_]
        };
    $propertiesValues}

and pipe it to Export CSV. Tested on Pshell 4.0.

Answer 2

I had a crack at this because I'm currently migrating an old SharePoint 2010 Foundation server to another a platform.

So, no. It isn't actually an answer to the question about SharePoint Online. But it answers the question in the head line :)

What really got me stomped with SharePoint 2010/PowerShell 2.0 was that the list item properties wasn't listed with the object (and that .Net object notation isn't fully supported). You need to know that they actually are available as hash table lookups :/

$item["Title"]
$item["CustomProp_1"]
etc.

Anyhow...

function Get-mySPListContent {

    [CmdletBinding()]
    param (
        $List
    )

    if ($List.BaseType.ToString() -ne 'DocumentLibrary') {
        
        $ParentWeb = $List.ParentWeb.Url
        $Title = $List.Title 
        $Fields = $List.Fields | Select StaticName, Title, Type, FieldValueType

        [array]$ExtractedList = $null

        foreach ($Item in $List.Items) {

            $ListObject = New-Object PSObject
            foreach ($FieldName in $Fields | Select -ExpandProperty StaticName) {
                Add-Member -InputObject $ListObject -MemberType NoteProperty `
                    -Name $FieldName `
                    -Value $Item[$FieldName]
            }# end fields

            $ExtractedList += $ListObject

        }# end items

        return New-Object PSObject | Select `
            @{l='ParentWeb'; e={$ParentWeb}},
            @{l='Title'; e={$Title}},
            @{l='Fields'; e={$Fields}},
            @{l='List'; e={$ExtractedList}}
    
    }# endif

}

And to use the function...

Add-PSSnapin Microsoft.SharePoint.PowerShell

(Get-SPSite).AllWebs | Select -ExpandProperty Lists | foreach {
    Get-mySPListContent $_ | foreach {
        $_.List | Export-Csv -Path "$($_.Title).csv" -NoTypeInformation
    }
}