SharePoint Server: Export List Items to CSV using PowerShell

Requirement: Export SharePoint List items to a CSV file.

Export SharePoint List to CSV using PowerShell

How to Export SharePoint list data to CSV using PowerShell:

This script exports the SharePoint list to CSV using PowerShell. It retrieves all list items, filters them based on the provided column value, and then creates a property to hold the list item values and then appends the objects which hold the list item values to an array.

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

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "https://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 another case, required to export all fields from the user information list (UIL) to CSV.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Variables
$SiteUrl="https://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.

sharepoint list export to excel powershell

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="https://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

To export a list to a CSV in SharePoint Online, use: How to Export SharePoint Online List to CSV using PowerShell?

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

56 thoughts on “SharePoint Server: Export List Items to CSV using PowerShell

  • I have multiple subsite and list name is same all subsite, running to below script given error please help me the code, the out require in single csv file.

    Error: Export-Csv cannot bind argument to parameter “InputObject” because it is null

    $SiteUrl=”subsite Url”
    $ListName=”Project”
    $OutPutFile = “C:\Temp\ListData.csv”

    #Get Web and List
    $webs = Get-SPWeb $SiteUrl
    foreach ($web in $webs.AllWebs)
    {
    $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 -Append
    Write-host -f Green “List ‘$ListName’ Exported to $($OutputFile) for site $($SiteURL)”

    Reply
  • Hi. Thanks for the code. Can this be applied to export data from the Microsoft 365 platform – for example {entity}.sharepoint.com? I’ve been using this successfully from an internally hosted SharePoint site (i.e. teamsites.{entity}.com), but my client is migrating all of their SharePoint sites to 365. Thanks

    Reply
  • HI Christian,

    it errors for me, can you please provide your code here?

    Reply
  • Hi ..How can i export files from all document library where column value is null in Sharepoint On-premise ?

    Reply
  • Hi. Thanks for this script. I need to export only some columns. How i can do that? Thanks!

    Reply
    • Use the first script in this article and map desired columns. E.g. If you want to add “Title” column to the CSV, use: $ExportItem | Add-Member -MemberType NoteProperty -name “Title” -value $_[“Title”]

      Reply
  • I’m trying to adapt your script to export items from a list on all subsites (webs) in a site collection using ForEach($Web in $Site.AllWebs), but am having trouble. The same list exists on all subsites and I need to dump out the data, either to a single CSV using -append, or multiple files. Can you help?

    Reply
    • Use -Append switch in Export-CSV cmdlet. If you want to export to multiple files from subsites,use:
      $ListItemCollection | Export-CSV “c:$($List.Title)_$($Web.Title).txt” -NoTypeInformation

      Reply
  • HI Salaudeen,

    The code for all internal field is working fine for me

    Could you help provide code for all internal fields with all versions metadata
    https://www.sharepointdiary.com/2018/01/sharepoint-online-export-list-version-history-to-excel-using-powershell.html

    Is working fine.. but few fields values are having comma .. so export data having problem

    Could you help provide code for all internal fields with all versions metadata

    Reply
  • Hello,

    i would want to export all items with internal names and all versions the data to CSV file.

    i ran your script which you posted ” Export All fields in the list to Excel using PowerShell.

    I need all internal fiels and all versions at time

    Reply
  • Hi Salaudeen,

    Please provide the complete code for the specific view in the list with all the fileds in that view.

    drop me an email to : shafisharepoint@gmail.com

    Reply
  • Hi Salaudeen,

    I ran your script on our server and got 400,000 items and my server told me that there was not enough memory avialable.
    Is there a way to limit the records to 10,000 or so and than tell the script which items I want? So kinda like first run is 1 to 10,000, second run is 10,001 to 20,000 and so on?

    Reply
  • I keep getting the “cannot index into null array”. The list I am trying to work with is populated. How can I fix this?

    Reply
  • Hi, I trying to concatenate the list ID with another column. Something like this:
    $ExportItem | Add-Member -MemberType NoteProperty -name “EXTERNALID” -value $_[“EXTERNAL”] $_[“ID”]
    any help would be much appreciated.

    Reply
  • Hello,

    i would want to export all items with the data to CSV file.

    i ran your script which you posted ” Export All fields in the list to Excel using PowerShell”

    #Read more: https://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html#ixzz5eZNKGqor

    but i got the empty csv file. kindly help me.
    i have updated the URL and list name like

    $SiteUrl=”myurl”
    $OutPutFile = “C:UserInfoList.csv”

    #Get Web and User Information List
    $web = Get-SPWeb $SiteUrl
    $UserInfoList = $Web.listname

    is there anything else do i need to change..?

    Reply
    • Hi There,

      Second Script has been updated for easier use. Just change these three variables: $Web, $List, $OutputFile

      Reply
  • Hello Salaudeen,

    i would want to export all list items with the data to CSV file.
    i Ran your script which you posted “Export All fields in the list to Excel using PowerShell”

    but it is giving empty file. could you please help me to export all list items with the data to CSV file.

    thank you in advance !!

    #Read more: https://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html#ixzz5eZL9L1y6″

    Reply
  • How do i get the complete urls of all the items in the same script. I want the item url and not the id or title.

    Reply
    • Here you go: $ItemURL = $(“{0}{1}?ID={2}” -f $Web.Url.Replace($Web.ServerRelativeUrl,”), $Item.ParentList.DefaultDisplayFormUrl,$Item.ID)

      Reply
  • Thanks! Nice script.

    When my sharepoint list items are blank. CSV is coming without header.

    Can we pull the list items with headers even if the list items are blank.

    Vivek

    Reply
  • Question, how would I change it so I’m able to export different types of columns such as dates, a text field or a user filed otherwise its throwing an error.

    Reply
    • Based on the column type, You have to retrieve its value differently. E.g. For column type like People Picker and Lookup. Search this blog for such scripts!

      Reply
  • Hi Rajak,

    I need to extract columns(fields) which are available only in the default view. Could you please advise the change in the script. Also I don’t want internal name to be there in extracted report rather displayname. I tried

    $ExportItem | Add-Member -MemberType NoteProperty -name $Field.DisplayName -value $_[$Field.InternalName]

    But it didn’t work.
    Kindly advise.

    Reply
    • Hi Saurav, Here is the code you wanted:

      $web = Get-SPWeb “https://portal.crescent.com”
      $list = $web.Lists[“Academy”]
      $DefaultView = $list.Defaultview
      $ViewFields = $DefaultView.viewFields
      #Array to Hold Result – PSObjects
      $ListItemCollection = @()

      $list.items | foreach {
      $ExportItem = New-Object PSObject
      foreach($field in $ViewFields)
      {
      $ExportItem | Add-Member -MemberType NoteProperty -name $Field -value $_[$Field]
      }
      #Add the object with property to an Array
      $ListItemCollection += $ExportItem
      }
      #Export the result Array to CSV file
      $ListItemCollection | Export-CSV “c:results.csv” -NoTypeInformation

      Reply
  • Hi Rajak,

    I need to extract columns(fields) which are available only in the default view. Could you please advise the change in the script. Also I don’t want internal name to be there in extracted report rather displayname. I tried

    $ExportItem | Add-Member -MemberType NoteProperty -name $Field.DisplayName -value $_[$Field.InternalName]

    But it didn’t work.
    Kindly advise.

    Reply
  • Thanks:) Rajak, Great work. You killed my frustration.

    Reply
  • Hi,

    Your code is awesome, I have folder hierarchy in Document Library, So When I execute the code it is not exporting data inside folders. How can I achieve this please help me.

    Reply
  • I want to export pages library data, What if we have data inside folders.
    I want to export each folder data to separate csv file.

    Reply
  • Love you script! I do have a question. Is there a way when export dates to exclude the time (my output has [date, 0:00])? Also, some of my fields which have names it exports and id number and #. (ex: 755#,John Smith). Is there a way to exclude that info?

    Reply
  • @Again Reddy,
    run this below code before executing/calling the sharepoint script.
    add-pssnapin microsoft.sharepoint.powershell

    Reply
  • Hi Rajack, I am trying to run your code in window poweshell. It is giving below error.. I am sharepoint user and trying import lists into CSV file… How do I overcome below error.. Thanks
    “Get-SPWeb : The term ‘Get-SPWeb’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.”

    Reply
  • HI Rajack,
    I got one more issue . export list is getting one of the columns as 72465.1234 instead of 72465

    Reply
  • Hi rajack,
    nice script .

    I m trying to use for a view in a list.
    i dont need any status .
    How can i achieve this ?

    Reply
    • HI Rajack,
      it worked but the script is not pulling whole items .

      its pulling only the first row only

      Reply
    • The given code pulls all rows with status=”In progress” { $_[“Status”] -eq “In Progress”} . Remove that part and try. Line # 13 should be:

      $list.Items | foreach {

      Instead of:

      $list.Items | Where-Object { $_[“Status”] -eq “In Progress”} | foreach {

      Reply
  • Nice PoweShell script, it is really very helpful for exporting all items from Sharepoint list to csv files format. I tried the following above script to export SharePoint list data into CSV files and I also found good application (https://www.lepide.com/sharepoint-audit/) which enables to automate export reports and get real time alerts on detecting changes to servers, farms, content, groups, users, permissions, sites, lists, libraries, files/folders, etc.

    Reply
  • Great script! Thank you for sharing this. How do I modify this to only export a specific Date?

    Reply
  • Hi Rajack,

    Many Thanks for your response. I achieve it through the Powershell Script.

    But when i try to run it via batch file and invoke the Administrator: Sharepoint 2010 management shell and through that when i run myscript.ps1 it worked fine on Manually running the batch file.

    But when i try to execute my batch file through vb.net code it shows the following error in powershell : “The term ‘Get-SPWeb’ is not recognized as the name of a cmdlet,function,script file, or operable program “. Am also added “Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue” in my script.

    How to resolve it. Help me.

    Thanks.

    Vaishnavi.

    Reply
  • Hi Rajack,

    Your Script is really Great and very useful for beginners also .
    Am new to Sharepoint . We are using sharepoint Foundation (2010)Server-1 and Server-2. I have a scenario. I need to export a filtered items in a list from one server and moved it to another server. After moved the items to 2nd server, the particular items will be removed from 1st server.
    For Eg :
    In 1st Server My list name is TestList and have a lot of fields one of while is Stage. When field Stage =”Archive” it must be moved to 2nd server which is having a same list template with name “ArchiveTestList”. After moving it the particular rows will be removed from 1st server List.

    Please Help me. Thanks in Advance.

    Vaishnavi

    Reply
    • Hi There,

      When you say: 1st Server and 2nd server, Are they different SharePoint environments? If so, You can write a PowerShell script to do copy-move operations. However, This script would be either scheduled basis or On-demand, But not on list data update event!

      Reply
  • It worked. Thanks!

    Reply
  • I like your script. What if I just want to export a specific view instead of all the list items? I’m pretty sure it’s just a minor tweak to the code but I couldn’t figure it out. Thanks.

    Reply
    • Simple,

      #Get the View
      $view = $list.Views[“YOUR-VIEW-NAME”]

      #Get All items from the view
      $items = $list.GetItems($view)

      Reply
    • hi boss, your code work great!
      i also want to show only the specific view. but, it still showing all of the view.
      this is my code, i’m right? if i want to make it same with specific view, i need to rearrange the exportitem align. What i want is, if i change at view (the arrangement of column) without need to change hardcode, so it will change it according to the view. But it’s not showing.

      #Get the Target List
      $list = $web.Lists[“test”]
      #Get the View
      $view = $list.Views[“Today”]

      #Get All items from the view
      $items = $list.GetItems($view)

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

      #Get All List items where Status is “In Progress”
      $items | foreach {
      $ExportItem = New-Object PSObject
      $ExportItem | Add-Member -MemberType NoteProperty -name “Full Name” -value $_[“Title”]
      $ExportItem | Add-Member -MemberType NoteProperty -Name “Contact Number” -value $_[“Contact Number”]
      $ExportItem | Add-Member -MemberType NoteProperty -name “Id1” -value $_[“Id1”]
      $ExportItem | Add-Member -MemberType NoteProperty -name “Id2” -value $_[“Id2”]
      $ExportItem | Add-Member -MemberType NoteProperty -name “Id3” -value $_[“Id3”]
      $ExportItem | Add-Member -MemberType NoteProperty -name “Reference Number” -value $_[“Reference Number”]

      #Add the object with property to an Array
      $ListItemCollection += $ExportItem
      }
      #Export the result Array to CSV file
      $ListItemCollection | Export-CSV $savePath -NoTypeInformation

      #Dispose the web Object
      $web.Dispose()

      Thank you for the best code! 🙂

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *