Sunday, April 28, 2013

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 list to CSV
Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue
 
#Variables
$SiteUrl="http://opera.crescent.com"
$OutPutFile = "C:\UserInfoList.csv"
 
#Get Web and User Information List
$web = Get-SPWeb $SiteUrl
$UserInfoList = $Web.SiteUserInfoList
Write-host "Total Number of Items Found:"$UserInfoList.Itemcount

#Array to Hold Result - PSObjects
$ListItemCollection = @()
  
 #Get All List items where Status is "In Progress"
 $UserInfoList.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 "User Information List Exported to $($OutputFile) for site $($SiteURL)"
 
$web.Dispose()



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

28 comments :

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

    ReplyDelete
    Replies
    1. Simple,

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

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

      Delete
    2. 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! :)

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

    ReplyDelete
    Replies
    1. 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!

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

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

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

    ReplyDelete
  6. 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 ?

    ReplyDelete
    Replies
    1. Hi Sasikanth,

      Sure!

      #Get the View
      $view = $list.Views["YOUR-VIEW-NAME"]
      #Get All items from the view
      $items = $list.GetItems($view)

      Get List Items from SharePoint View in PowerShell

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

      its pulling only the first row only

      Delete
    3. 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 {

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

    ReplyDelete
  8. 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."

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

    ReplyDelete
  10. 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?

    ReplyDelete
    Replies
    1. For column type like People Picker and Lookup, You'll have retrieve column values bit different. Refer How to retrieve People Picker column values using PowerShell

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

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

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

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

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

    ReplyDelete
    Replies
    1. 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

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

    ReplyDelete
    Replies
    1. 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!

      Delete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...