Wednesday, 29 September 2010

Get all column values from a SharePoint item using PowerShell

There are many column values associated with a single item in SharePoint that are not easily visible using the browser UI or SharePoint Designer – for example, the item GUID, Content Type ID, various paths and URLs to the item, and others. Sometimes, it can also be handy to view a list of internal column names and values for determining what to use in content queries, data views, PowerShell scripts, XSLT, etc.

The script below enables you to specify a single item or file in a list or document library and output a table containing a full list of the column display names, internal names, and their associated values for the item.

First, run the following script:

function Get-SPItemValues {
    #Ask for the web, list and item names
    $WebName = Read-Host "Please enter the web address:"
    $ListName = Read-Host "Please enter the list or library name:"
    $ItemName = Read-Host "Please enter the item title or file name:"

    #Set up the object variables
    $web = Get-SPWeb $WebName
    $list = $web.Lists[$ListName]
    [string]$queryString = $null

    #Check if the item is a file or list item and run a different query accordingly
    if ($list.BaseType -eq "DocumentLibrary") {
        $queryString = "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + $ItemName + "</Value></Eq></Where>"
    }
    else
    {
        $queryString = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + $ItemName + "</Value></Eq></Where>"
    }

    #Create the CAML query to find the item
    $query = New-Object Microsoft.SharePoint.SPQuery
    $query.Query = $queryString
    $item = $list.GetItems($query)[0]

    #Walk through each column associated with the item and
    #output its display name, internal name and value to a new PSObject
    $item.Fields | foreach {
        $fieldValues = @{
            "Display Name" = $_.Title
            "Internal Name" = $_.InternalName
            "Value" = $item[$_.InternalName]
        }
        New-Object PSObject -Property $fieldValues | Select @("Display Name","Internal Name","Value")
    }

    #Dispose of the Web object
    $web.Dispose()
}

Once you have run the script, you can call the function using the following command:

Get-SPItemValues

You will be then asked to enter a site name….

image

….a list or document library name….

image

….followed by the title (if a list item) or file name (if in a document library) of the item.

image

The output from the command will look similar to the screenshot below:

image

As the script creates the table using the New-Object PSObject command, you can output it in a number of different ways and formats. For example, to output the table sorted by the “Display Name” column, type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name"

To output the table contents to a CSV file, type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name" | Export-Csv -NoType -Path c:\columnvalues.csv

To output the table contents to a grid view, where the output is displayed in an interactive table (requires Microsoft .NET Framework 3.5 with Service Pack 1), type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name" | Out-GridView

Below is a screenshot showing how the grid view looks when using Windows PowerShell ISE:

image

26 comments:

  1. Hi! Love the site, you are quite the guru :)
    I am trying to enumerate items in certain folders within a list (SPDocumentLibrary), and then add or update metadata values based on the filename. Do you have any tips regarding enumerating/addressing specific folders, and the items within them using PowerShell?

    Any guidance would be much appreciated!

    ReplyDelete
  2. I just posted the question above, and it looks quite complex, to use GetItemsInFolder, you must pass in a view and a folder, of type SPView and SPFolder respectively. So to get the view I did
    $views=$list.Views
    foreach ($view in $views) {$view | select Title,ID} to get the guid, because the spview argument is required to be of type System.GUID, so then:
    $view=$list.GetView("I PASTED GUID HERE")

    so now one argument is taken care of, but I am stuck trying to address/assign the folder, again of type SPFolder...

    ReplyDelete
  3. $web.Folders[4].SubFolders[1].SubFolders[0].SubFolders[0] gets one of the many folders I am interested in addressed, but there has got to be a better way of doing this. I will shut up for a while, lol.

    ReplyDelete
  4. Hi,

    That's a lot of questions! If you want to get a folder and view from a list, here are a couple of examples:

    To get a view (assuming you have already assigned your $list variable, this command allows you to use the display name of the view rather than the GUID.

    $view = $list.Views["All Documents"]

    To get a folder low down in a hierarchy, I tend to prefer the SPWeb.GetFolder method, as follows:

    $folder = $web.GetFolder("Documents/HR/Policies/Expenses")

    ReplyDelete
  5. Hi I am getting the below error when i execute the script for fetching data from a list. Please let me know what is wrong.

    Cannot index into a null array.
    At D:\Powershell\ListValues.ps1:39 char:29
    + "Value" = $item[ <<<< $_.InternalName]
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

    New-Object : Cannot validate argument on parameter 'Property'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again.
    At D:\Powershell\ListValues.ps1:41 char:38
    + New-Object PSObject -Property <<<< $fieldValues | Select @("Display Name","Internal Name","Value")
    + CategoryInfo : InvalidData: (:) [New-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.NewObjectCommand

    ReplyDelete
  6. Difficult to know for sure, but it looks like the Title of the list item or name of the document hasn't been specified correctly.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi

    If I wanted to modify your function to include the type ... I thought this would work but it doesn't

    #output its display name, internal name and value to a new PSObject
    $item.Fields | foreach {
    $fieldValues = @{
    "Display Name" = $_.Title
    "Internal Name" = $_.InternalName
    "Type" = $._Type
    "Value" = $item[$_.InternalName]
    }
    New-Object PSObject -Property $fieldValues | Select @("Display Name","Internal Name", "Type", "Value")
    }

    #Dispose of the Web object
    $web.Dispose()

    ReplyDelete
  9. Hi Phil.
    I'm looking for a java script I can place into a Content Editor Web Part on a SharePoint site containing a list of projects. The code needs to send a weekly summary of specific information from certain columns for only the priority projects. Could someone help me construct this code, or maybe something similar already exists?
    Thanks!

    ReplyDelete
  10. Good function - how would you modify the function to display this information for all item versions, (or show version history?)

    Thanks

    ReplyDelete
  11. Brilliant function. Can I use this to loop through all items in all lists and libraries and get all column-values printed to a csv-file?

    ReplyDelete
  12. @Ulrich did you ever get any help for your request?

    ReplyDelete
  13. This is awesome and so hard to find anywhere else. I wonder if you would know how to access one of the fields (let's say "Modified") using the $item.Fields object? I've tried all sorts of ways, but only getting the InternalName of the field, not the value.

    $item.Fields | foreach {

    write-host "Field = " $item."Modified" <-???

    $fieldValues = @{
    "Display Name" = $_.Title
    "Internal Name" = $_.InternalName
    "Value" = $item[$_.InternalName]
    }
    New-Object PSObject -Property $fieldValues | Select @("Display Name","Internal Name","Value")
    }

    ReplyDelete
    Replies
    1. Jakistra, try this...

      $item.Fields | foreach {
      $fieldValues = @{
      "Display Name" = $_.Title
      "Internal Name" = $_.InternalName
      "Value" = $item[$_.InternalName]
      }

      $object = New-Object PSObject -Property $fieldValues | Select @("Display Name","Internal Name","Value")
      # Write-Host $object."Display Name"
      $intName = $object."Internal Name"
      if ($intName -eq "PhotoURL") {
      $v = $object."Value"
      Write-Host $v
      }
      }

      Delete

  14. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    five night at freddys 4 | fireboy and watergirl | five night at freddys |
    2048 game| tanki online 2

    ReplyDelete