SharePoint Online: Export List Items to CSV using PowerShell
Requirement: Export SharePoint online list items to CSV file from client side.
PowerShell script to Export SharePoint List Items to CSV from Client Side (CSOM):
Here is how you can export SharePoint online list to csv via PowerShell
SharePoint online PowerShell to Export List to CSV:
While the above script exports selected columns to Excel file, lets alter it a bit to export all columns data to Excel.
Related Posts:
PowerShell script to Export SharePoint List Items to CSV from Client Side (CSOM):
Here is how you can export 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="Salaudeen@crescent.com" $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 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, this script gets all list items under a list, but doesn't recursively gets items of folders and sub-folders of the list (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 Excel file, lets alter it a bit to export all columns 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:
Related Posts:
SharePoint Online: Export List Items to CSV using PowerShell
Reviewed by Salaudeen Rajack
on
March 09, 2016
Rating:

Looks good thankyou
ReplyDeleteI am getting the below error: with sdk version 15 and 16.
ReplyDeleteException 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
DeleteHello,
ReplyDeleteHow 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.
Deletegreat
ReplyDeleteWhen trying to Export a list to CSV using your 3rd script, i get the following error in line 27:
ReplyDelete+ $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!
DeleteFirst, you were right about that. I had to change the Username from "Domain/Username" to "Username@Domain".
DeleteBut 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...