Code Sample – SPO dump out list items to CSV

  • Article

I had to work on a SharePoint Online scenario the other day where the requirement was to dump out a specific list's items to a CSV, which will be consumed by another application. This list also had People fields. Below sample can be utilized for this scenario – and can be tweaked easily to meet other business requirements.

 

#Sample provided As-Is – Use after sufficient testing.
#replace these details – User name, domain, Password. (Also consider using Get-Credential to enter password securely as script runs)
#Ensure there is a folder called C:\OUTPUT
$username
=
"admin@domain.onmicrosoft.com"

$password
=
"pwd"

$url
=
"https://domain.sharepoint.com"
$securePassword
=
ConvertTo-SecureString
$Password
-AsPlainText
-Force

#install the SharePoint Online client SDK
[system.reflection.assembly]::LoadWithPartialName('Microsoft.SharePoint.Client')
[system.reflection.assembly]::LoadWithPartialName('Microsoft.SharePoint.Client.Runtime')

# connect/authenticate to SharePoint Online and get ClientContext object.
$clientContext
=
New-Object
Microsoft.SharePoint.Client.ClientContext($url)
$credentials
=
New-Object
Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
$clientContext.Credentials = $credentials

if (!$clientContext.ServerObjectIsNull.Value)
{
    Write-Host
"Connected to SharePoint Online site: '$Url'"
-ForegroundColor
Green

}

#The list name I have used is CSVTEST1, you will have to change to your list's name.
$web
=
$clientContext.Web
$clientContext.Load($web)
$clientContext.ExecuteQuery()
$list
=
$web.lists.GetByTitle('csvtest1')
$all_Items_caml
=
[microsoft.sharepoint.client.camlquery]::CreateAllItemsQuery()
$allitems =
$list.GetItems($all_Items_caml)
$clientContext.Load($list)
$clientContext.Load($allitems)
$clientContext.ExecuteQuery()

#Defining the data table that holds all the list data in custom fields.
$datatable
= @()

#Iterating through the list items with an AllItems query -> You will have to take care of list throttling scenarios.
#Multi values fields like names and email addresses are saved as semi colon separated strings - within the same custom field.
#These will need unpacking using custom code, where the CSV is being consumed.
#The people field in this list is called XPEOPLE, and this can hold multiple values.
foreach($listitem
in
$allitems)
{
        $emails
=
''
        foreach($p
in
$listitem['xpeople'])
        {
            $u
=
$web.GetUserById($p.LookupId)
            $clientContext.Load($u)
            $clientContext.ExecuteQuery()
            $emails=$emails+$u.email+';'
        }  

        $peoplenames
=
''

($listitem['xpeople']
|
select
lookupvalue).lookupValue |
%{$peoplenames=$peoplenames+$_+';'}

        #Use the SPList Field names below -> Use InternalNames as seen in SchemaXML
$obj
=
new-object
-TypeName
'PSObject'
-Property @{
        Title = $listitem['Title']
        Name = $listitem['Name']
        Phone = $listitem['Phone_No']
        PeopleCount =     $listitem['xpeople'].count
        PeopleEmailAddresses = $emails
        PeopleNames = $peoplenames       
    }
    $datatable
+=
$obj
}

#Dumping out the datatable to CSV.
$datatable
|
Export-Csv
C:\output\test.csv

#Testing the CSV by importing into a variable - OPTIONAL.
$all
=
Import-Csv
C:\output\test.csv
$all[0].PeopleEmailAddresses