Export SharePoint List Items to CSV using PowerShell

Requirement: Export SharePoint List items to a CSV file.

Export SharePoint list data to CSV using PowerShell:
This script exports SharePoint list to csv using PowerShell. It retrieves all list items, Filters it based on the provided column value and then creates a property to hold the list item values and then appends the objects which holds the list item values to an array.

Finally, using the Export-CSV Cmdlet, we are exporting the data to CSV file.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "http://sharepoint.crescent.com/sites/Operations/"

#Get the Target List
$list = $web.Lists["Monthly Schedule Log"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items where Status is "In Progress"
 $list.Items |  Where-Object { $_["Status"] -eq "In Progress"} | foreach {
 $ExportItem = New-Object PSObject 
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Status" -value $_["Status"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Priority" -value $_["Priority"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.txt" -NoTypeInformation                        

#Dispose the web Object
$web.Dispose()

Export All fields in the list to Excel using PowerShell
In an another case, required to export all fields from the user information list (UIL) to CSV

Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue
 
#Variables
$SiteUrl="http://intranet.crescent.com"
$ListName="User Information List"
$OutPutFile = "C:\Temp\ListData.csv"
 
#Get Web and List
$web = Get-SPWeb $SiteUrl
$List = $Web.Lists[$ListName]
Write-host "Total Number of Items Found:"$List.Itemcount

#Array to Hold Result - PSObjects
$ListItemCollection = @()
  
 #Get All List items 
 $List.Items | ForEach {
 write-host "Processing Item ID:"$_["ID"]
 
   $ExportItem = New-Object PSObject 
   #Get Each field
   foreach($Field in $_.Fields)
    {
        $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $_[$Field.InternalName]  
    }
    #Add the object with property to an Array
    $ListItemCollection += $ExportItem

}    
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation 
Write-host -f Green "List '$ListName' Exported to $($OutputFile) for site $($SiteURL)"

This script exports all items in the UIL to CSV format. Here is the screenshot of the CSV file generated in Excel.

Export Large List data to CSV file in Batches:
When you have a larger list, reading and exporting all data in one stretch may end-up in memory and performance-related issues. So, here is how you can export list items in batches:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Set config variables
$WebURL="http://projects.crescent.com"
$ListName ="Projects"
$OutPutFile = "C:\temp\Report.csv"

#Delete the Output File if exists
If (Test-Path $OutPutFile) { Remove-Item $OutPutFile }
 
#Get Web and List Objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists[$ListName]
Write-host "Total Number of Items Found:"$List.Itemcount
  
#Define the CAML Query
$BatchSize = 500
$Query = New-Object Microsoft.SharePoint.SPQuery
$Query.ViewXml = @"
    <View Scope='Recursive'>
        <Query>
            <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        </Query>
        <RowLimit Paged='TRUE'>$BatchSize</RowLimit>
    </View>
"@

$Counter = 0
#Process items in batch
Do 
{
    #Get List Items
    $ListItems = $List.GetItems($Query)
    $Counter =  $Counter+$ListItems.Count
    Write-Progress -PercentComplete ($Counter / ($List.ItemCount) * 100) -Activity "Exporting List Items of '$($List.Title)'" -Status "Processing Items $Counter of $($List.ItemCount)"

    #Array to Hold Result - PSObjects
    $ListItemCollection = @()
   
    #Get All List items 
    $ListItems | ForEach {
        #write-host "Processing Item ID:"$_["ID"]
  
        $ExportItem = New-Object PSObject 
        #Get Each field - Exclude hidden
        Foreach($Field in $_.Fields | Where {!$_.hidden})
        {
            $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $_[$Field.InternalName]  
        }
        #Add the object with property to an Array
        $ListItemCollection += $ExportItem 
    }
    #Export the result Array to CSV file
    $ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation -append

    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)

Write-host "List data exported to CSV Successfully!" -f Green