SharePoint Online: Export List Items to CSV using PowerShell
Requirement: Export SharePoint Online list items to a CSV file.
How to Export Sharepoint Online list items to Excel or CSV?
Do you need to export data from a SharePoint Online list to CSV? There could be several reasons why you might want to export SharePoint Online list items to Excel or CSV. For example, you may want to create a backup of your data, you may need to migrate your data to another platform or another application for further analysis or manipulation. Whatever the reason, Microsoft has made this process quite simple. This blog post will show you how to export SharePoint Online list items to CSV using PowerShell and the web browser interface. Let’s get started!
How do I Export a SharePoint list to CSV? To export the SharePoint Online list to a CSV File, Simply navigate to the list and click on “Export to CSV” from the “Export” menu, and SharePoint exports the list data to a CSV file. It’s that simple!
PowerShell Script to Export SharePoint List Items to CSV
Although you could export the list data to a CSV file manually, as described above – There are some situations You may have to automate the process. I’ll show you how to use PowerShell to export SharePoint Online list items to CSV format.
Here is how you can export the SharePoint Online list to CSV via PowerShell:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/poc"
$ListName="Employee"
$ExportFile ="c:\Scripts\ListRpt.csv"
$UserName="[email protected]"
$Password ="Password goes here"
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials
#Get 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()
#Array to Hold List Items
$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"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
#Add the object with the above properties to the Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation
Write-host "List data Exported to CSV file successfully!"
Please note, that this script gets all list items under a list but doesn’t recursively get items of folders and sub-folders (If you have folders and sub-folders). To retrieve all list items recursively, just change the CAML query part as:
#Get all List items from the library Including Items in Sub-Folder
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq></Where></Query></View>"
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
SharePoint Online PowerShell to Export List to CSV
While the above script exports selected columns to the Excel file, let’s alter it to export all column data to Excel.
#Load SharePoint CSOM Assemblies
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"
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/"
$ListName= "Projects"
$ExportFile ="c:\ListItems.csv"
#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Credentials
#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$FieldColl = $List.Fields
$Ctx.Load($ListItems)
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()
#Array to Hold List Items
$ListItemCollection = @()
#Fetch each list item value to export to excel
Foreach($Item in $ListItems)
{
$ExportItem = New-Object PSObject
Foreach($Field in $FieldColl)
{
if($NULL -ne $Item[$Field.InternalName])
{
#Expand the value of Person or Lookup fields
$FieldType = $Item[$Field.InternalName].GetType().name
if (($FieldType -eq "FieldLookupValue") -or ($FieldType -eq "FieldUserValue"))
{
$FieldValue = $Item[$Field.InternalName].LookupValue
}
else
{
$FieldValue = $Item[$Field.InternalName]
}
}
$ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $FieldValue
}
#Add the object with above properties to the Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation
Write-host "List data Exported to CSV file successfully!"
And the output:
Export Larger SharePoint Online Lists to a CSV File
What if your list has more than 5000 items? How about getting values from special fields such as MMS, Multi-lookup, URL, etc.?
#Load SharePoint CSOM Assemblies
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"
#Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
$ExportFile ="C:\temp\Projects.csv"
$BatchSize = 500
#Get Credentials to connect
$Cred = Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
#Get All List Fields
$FieldColl = $List.Fields
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()
#Filter List fields - Skip Read only, hidden fields, content type and attachments
$ListFields = $FieldColl | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne "ContentType") -and ($_.InternalName -ne "Attachments") }
#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @"
<View Scope='RecursiveAll'>
<Query>
<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
</Query>
<RowLimit Paged="TRUE">$BatchSize</RowLimit>
</View>
"@
#Array to Hold List Items
$ListItemCollection = @()
#Get List Items in Batch
Do
{
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Fetch each list item value to export to excel
Foreach($Item in $ListItems)
{
$ExportItem = New-Object PSObject
Foreach($Field in $ListFields)
{
If($NULL -ne $Item[$Field.InternalName])
{
#Handle Special Fields
$FieldType = $Field.TypeAsString
If($FieldType -eq "User" -or $FieldType -eq "UserMulti" -or $FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti")
{
$FieldValue = $Item[$Field.InternalName].LookupValue -join "; "
}
ElseIf($FieldType -eq "URL") #Hyperlink
{
$URL = $Item[$Field.InternalName].URL
$Description = $Item[$Field.InternalName].Description
$FieldValue = "$URL, $Description"
}
ElseIf($FieldType -eq "TaxonomyFieldType" -or $FieldType -eq "TaxonomyFieldTypeMulti") #MMS
{
$FieldValue = $Item[$Field.InternalName].Label -join "; "
}
Else
{
#Get Source Field Value
$FieldValue = $Item[$Field.InternalName]
}
}
$ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $FieldValue
}
#Add the object with above properties to the Array
$ListItemCollection += $ExportItem
}
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation
Write-host "List data Exported to CSV file successfully!"
Export SharePoint Online List Items to CSV using PnP PowerShell
To export the SharePoint Online list to CSV, use this PowerShell script. This script exports selected field values from the given list. The first thing you need to do is, connect to your SharePoint Online Site URL. Then, get all list items from the target list that you want to export. Finally, Run the Export-CSV cmdlet and specify the path where you would like to export the SharePoint list data to a CSV file. The Export-Csv cmdlet also has other useful parameters such as Append, Encoding, etc.
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/projects"
$ListName = "Projects"
$SelectedFields = @("ProjectName","Project_x0020_Manager", "StartDate")
$CSVPath = "C:\Temp\ListData.csv"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get List items from the list
$ListItems = Get-PnPListItem -List $ListName -Fields $SelectedFields -PageSize 500
#Iterate through each item and extract data
$ListDataColl = @()
$ListItems | ForEach-Object {
$ListData = New-Object PSObject
#Get the Field Values of the item as text
$ListItem = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText
ForEach($Field in $SelectedFields)
{
$ListData | Add-Member Noteproperty $Field $ListItem[$Field]
}
$ListDataColl += $ListData
}
#Export data to CSV
$ListDataColl
$ListDataColl | Export-CSV $CSVPath -NoTypeInformation
This will export all the list items from the specified list into a CSV file named “ListData.csv” in the “C:\Temp” folder on your computer. You can then open this file in Excel or another spreadsheet program to view the data. Make sure you have the PnP PowerShell module installed before running this script.
How about exporting all available field values from a SharePoint Online list? Well, Here is the PnP PowerShell to export SharePoint online list items to CSV:
#Config Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Access Requests"
$CSVPath = "C:\Temp\ListData.csv"
$ListDataCollection= @()
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
$Counter = 0
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000
#Get all items from list
$ListItems | ForEach-Object {
$ListItem = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText
$ListRow = New-Object PSObject
$Counter++
Get-PnPField -List $ListName | ForEach-Object {
$ListRow | Add-Member -MemberType NoteProperty -name $_.InternalName -Value $ListItem[$_.InternalName]
}
Write-Progress -PercentComplete ($Counter / $($ListItems.Count) * 100) -Activity "Exporting List Items..." -Status "Exporting Item $Counter of $($ListItems.Count)"
$ListDataCollection += $ListRow
}
#Export the result Array to CSV file
$ListDataCollection | Export-CSV $CSVPath -NoTypeInformation -Encoding UTF8
This script gets all list items and exports them to a CSV file. If you want to apply filters to the list items, Say you wish to get all list items created in the past 30 days, you can use:
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 | ? { $_["Created"] -gt (Get-Date).AddDays(-30) }
Related Posts:
Is there a way to export all the lists to separate excel? we have about 180+ lists and would like to be done in one go. please advise?
This is what I am doing , Can you suggest if anything wrong in this :
$ExportItem | Add-Member -MemberType NoteProperty -Name “Description” -value $_[“Description”]
Thanks , If I understood you , I think I am already using double quotes :
$ExportItem | Add-Member -MemberType NoteProperty -Name “Description” -value $_[“Description”]
Try: $ExportItem | Add-Member -MemberType NoteProperty -Name “Description” -value “`”$($_[“Description”])`””, or use the last script in this article.
Hi, I am facing issue with Muli line text column (Plain Text) , While exporting to CSV using Powershell , It gives output in two different rows when there is New line ( or multiple paragraps) in Description field. Can you help please.
Try to wrap the field value in double quotes.
@Salaudeen, great script! How would you deal with multiline columns, which are configured with “Append changes to existing text”? At present this script kicks out a blank entry.
I was throttled with 429 when I used your PNP script to export List to csv. Is there a way to avoid being throttled while using PNP scripts?
You got to handle it with a pause! “Start-Sleep -s 5” inside Try.. Catch.
Can we export list data based on List View Or apply filter criteria in the script ?
Yes! You can apply CAML-Query filter as in this article. Or filter by view using: SharePoint Online: Get All Items from a List View using PowerShell
Any way to export *all* list data (with a column for the list name) to a single CSV?
As long as all lists are with same columns – Yes! You can append to same single CSV!
How can export-csv output a rich text column with only line break text, without unnecessary tags?
Example “div class …”
Use: $ListItem[$FieldName] -replace ‘<[^>]+>’,”
After all, is it a character string replacement? I wondered if there was any good way to do this, even if there was a tag as text, but it was helpful. Thank you very much.
is there any script export all documents inventory in SharePoint Online Site (more than 5000 items)?
Here you go: SharePoint Online: Get Document Library Inventory (Folder-SubFolder-File Structure) using Powershell
Hello i have a csv with urls of 20 sites which have a risk list 6 coloumns in each
Im trying to build a script that takes all of the items from this and puts it into a CSV i will run it once a week i have the below but stuck on how to pipe all the items into the csv
$SiteURL = “C:\sites.CSV”
$CSVFile = get-content $SiteURL
#Load SharePoint CSOM Assemblies
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
#Config Variables
$UserName=”[email protected]”
$Password =”password”
$ListName=”risk”
$ViewName=”All Items”
$items = $ListName.items
$items.count;
Foreach ($c in $CSVFile) {
Try {
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($c)
$Ctx.Credentials = $Credentials
#Get the View
$List = $Ctx.web.Lists.GetByTitle($ListName)
$View = $List.Views.GetByTitle($ViewName)
$Ctx.Load($View)
$Ctx.ExecuteQuery()
#Get all list items from the view
$CAMLQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$CAMLQuery.ViewXml = $View.ViewQuery
$ListItems = $List.GetItems($CAMLQuery)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Iterate throgh each item:
ForEach($Item in $ListItems) {Write-host $Item[“Title”,”phone”]}
Write-host “Total List Items Found in the Given View: $($ListItems.Count)” -ForegroundColor Green
}
Catch {
write-host -f Red “Error Getting List Items from the List View!” $_.Exception.Message
}
}
#Read more: https://www.sharepointdiary.com/2018/01/sharepoint-online-get-all-items-from-list-view-using-powershell.html#ixzz5gFyFhSwh
When trying to Export a list to CSV using your 3rd script, i get the following error in line 27:
+ $Ctx.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
And my CSV-file turns out empty. As a total newbie to SharePoint and Powershell I don’t have the slightest clue, what I could’ve done wrong and totally rely on your help to get my Boss happy.
Thanks in Advance and greetings from Germany.
Hi There, As it errors in Line#27, Check your parameters and credentials.. Happy to help!
First, you were right about that. I had to change the Username from “Domain/Username” to “[email protected]”.
But second, the created csv-file still turns out blank, so I brought in some catches to find the error and there’s still an error in line 27. It says:
“Error at Microsoft.SharePoint.Client.ClientContext.ExecuteQuery()
Exception Message: Exception calling “ExecuteQuery” with “0” argument(s):
“The IDCRL response header from server ‘https://xxx.yyy.zz/’ is not valid. The response header value is ‘NTLM’. The response status code is ‘Unauthorized’. All response headers are ‘SPRequestGuid=bf09a99e-1180-808c-0e48-c20b49d51bdd, request-id=bf09a99e-1180-808c-0e48-c20b49d51bdd, X-FRAME-OPTIONS=SAMEORIGIN, SPRequestDuration=2, SPIisLatency=0, MicrosoftSharePointTeamServices=15.0.0.4823, X-Content-Type-Options=nosniff, X-MS-InvokeApp=1; RequireReadOnly, Content-Length=16, Content-Type=text/plain; charset=utf-8, Date=Wed, 05 Dec 2018 12:54:30 GMT, Server=Microsoft-IIS/8.5, WWW-Authenticate=NTLM, X-Powered-By=ASP.NET’.”
Failed Item: ”
I already figured out, that it correlates to the authentication method the sharepoint server uses, but I don’t know how to use NTLM for authentication.
Do you have any further ideas on how to get it running with NTLM?
Thanks in Advance again…
great
Hello,
How to get the multi value lookup column value for all the documents in library using powershell in sharepoint online and export to CSV.
Thanks in Advance.
Please help to modify the above script to include multi lookup value and choice column value in CSV file.
I am getting the below error: with sdk version 15 and 16.
Exception calling “ExecuteQuery” with “0” argument(s): “Identity Client Runtime Library (IDCRL) could not look up the realm information for a federated sign-in.”
On checking the variable $List it says The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
please advice me how to resolve this. thank you.
Double check the credentials you’ve supplied. Try reinstalling SharePoint Online SDK https://www.microsoft.com/en-us/download/details.aspx?id=42038 or SPO Management Shell from here: https://www.microsoft.com/en-us/download/details.aspx?id=35588
Looks good thankyou