Export SharePoint List Items to CSV using PowerShell

Requirement: Export SharePoint List items to a CSV file.

Export SharePoint List to CSV using PowerShell

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 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 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.

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="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

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

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

54 thoughts on “Export SharePoint List Items to CSV using PowerShell

  • September 15, 2021 at 12:08 AM

    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
  • April 30, 2021 at 1:20 PM

    HI Christian,

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

    Reply
  • September 14, 2020 at 4:40 PM

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

    Reply
  • September 8, 2020 at 8:04 PM

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

    Reply
    • September 9, 2020 at 5:08 AM

      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
  • August 19, 2020 at 6:47 PM

    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
    • August 20, 2020 at 6:37 AM

      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
  • May 15, 2020 at 5:49 PM

    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
  • May 15, 2020 at 5:56 PM

    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
  • April 10, 2020 at 6:53 AM

    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 : [email protected]

    Reply
  • April 9, 2020 at 2:33 PM

    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
  • September 30, 2019 at 7:43 PM

    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
  • May 20, 2019 at 9:44 PM

    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
  • February 4, 2019 at 1:45 PM

    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
    • February 5, 2019 at 10:55 AM

      Hi There,

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

      Reply
  • February 4, 2019 at 1:36 PM

    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
  • October 19, 2018 at 1:01 PM

    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
    • October 22, 2018 at 9:59 AM

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

      Reply
  • November 4, 2017 at 4:42 AM

    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
  • August 23, 2016 at 12:20 AM

    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
    • August 29, 2016 at 12:49 PM

      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
  • August 20, 2016 at 2:05 PM

    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
    • August 29, 2016 at 12:46 PM

      Hi Saurav, Here is the code you wanted:

      $web = Get-SPWeb “http://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
  • August 20, 2016 at 2:04 PM

    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
  • April 22, 2016 at 10:31 AM

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

    Reply
  • April 19, 2016 at 12:41 PM

    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
  • April 19, 2016 at 6:15 AM

    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
  • April 1, 2016 at 10:54 PM

    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
  • July 17, 2015 at 7:38 PM

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

    Reply
  • July 9, 2015 at 9:31 PM

    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
  • May 6, 2015 at 3:31 PM

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

    Reply
  • March 16, 2015 at 8:14 PM

    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
    • March 26, 2015 at 4:45 PM

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

      its pulling only the first row only

      Reply
    • June 3, 2015 at 6:20 AM

      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
  • November 11, 2014 at 9:46 AM

    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 (http://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
  • October 21, 2014 at 1:22 AM

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

    Reply
  • April 23, 2014 at 2:05 PM

    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
  • April 16, 2014 at 12:04 PM

    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
    • April 22, 2014 at 1:01 PM

      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
  • March 27, 2014 at 5:29 PM

    It worked. Thanks!

    Reply
  • March 21, 2014 at 8:03 PM

    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
    • March 21, 2014 at 9:21 PM

      Simple,

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

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

      Reply
    • June 3, 2015 at 3:16 AM

      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