Export all the columns with their data values in a

Question

I have to export all the columns in a particular SharePoint list along with the data contained in the columns.

I am currently able to get all column names but not data. Need help with that. Below is my code.

$url = "$url"
$listName = "$list"
$path ="c:\ColumnsOfList.csv"

$web = Get-SPWeb $url
$list = $web.Lists.TryGetList($listName)
$list.ContentTypes | % { $_.FieldLinks } | select Name |  Export-Csv -Path $path

Answer 1

There are a few different ways to do it, but the big thing to understand is that you'll need to iterate through the items in the list (not just through the fields on the list).

$url = "$url"
$listName = "$list"
$path ="c:\ColumnsOfList.csv"

$web = Get-SPWeb $url
$list = $web.Lists.TryGetList($listName)
$fields = $list.ContentTypes | %{ $_.FieldLinks } | select Name
$items = @() #array to store objects representing list items
$list.items | %{ 
    $item = $_; 
    $hash = @{}; #hash table to store field name-value pairs
    $fields | %{ $hash[$_.Name] = $item[$_.Name]  }; 
    $items += new-object psobject -Property $hash }
$items | Export-Csv -Path $path

Note that this approach tries to get all the items in the list, which is potentially inefficient and will fail if the list surpasses the list view threshold (by default throttled to 5000 items).

To access a filtered subset of list items, create an SPQuery object with appropriate CAML to select the desired items, then call $list.GetItems($spquery) instead of accessing the $list.items property directly.

Edit: Updated code to export display names of columns instead of internal names

$url = "$url"
$listName = "$list"
$path ="c:\ColumnsOfList.csv"

$web = Get-SPWeb $url
$list = $web.Lists.TryGetList($listName)
$fields = $list.ContentTypes | %{ $_.FieldLinks } | select Name, DisplayName
$items = @() #array to store objects representing list items
$list.items | %{ 
    $item = $_; 
    $hash = @{}; #hash table to store field name-value pairs
    $fields | %{ $hash[$_.DisplayName] = $item[$_.Name]  }; 
    $items += new-object psobject -Property $hash }
$items | Export-Csv -Path $path