PowerShell Script to export SharePoint List in Excel File

This script exports SharePoint list to csv using PowerShell. The script is suitable if you want to export list from Task Scheduler.

Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "https://project/test"

#Get the Target List
$list = $web.Lists["TestList"]

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

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

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

#Dispose the web Object
$web.Dispose()

When you insert in Excel

If you want to filter list export just insert line  Where-Object { $_["City"] -eq "New York"}


#Get the Web
$web = Get-SPWeb -identity "https://sbprojekti/test"

#Get the Target List
$list = $web.Lists["TestList"]

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

 #Get All List items
 $list.Items |  Where-Object { $_["City"] -eq "New York"} |foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

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

#Dispose the web Object
$web.Dispose()