none
Extract custom columns and their related values

    Question

  • Hello,

    I inherited a SharePoint 2010 Foundation site that contains about 40 custom columns and about 10 of those custom columns are of the type "Choice".  Is there a way using Powershell or something else to export to a .csv file a list of the custom columns and if they are the type "choice" to show the list of what the various choices are for each column?

    Regards,

    Tom

    Monday, June 23, 2014 10:10 PM

Answers

  • Hi Tom,

    Use the following PowerShell command, you can export the values of the columns from a list into a .csv file:

    $MyWeb = Get-SPWeb "http://sitename"
    $MyList = $MyWeb.Lists["ListName"]
    $exportlist = @()
    $Mylist.Items | foreach {
    $obj = New-Object PSObject -Property
     @{            
     “Column1” = $_["Column Name in SharePoint"]            
    "Column2" = $_["Column Name in SharePoint"]                   
    }
    $exportlist += $obj  
    $exportlist | Export-Csv -path 'C:\FilePath\Filename.csv'}

    More information, please refer to the links:

    http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/A_9182-Export-data-from-SharePoint-2010-List-using-Management-Shell.html

    http://meandmysharepoint.blogspot.com/2012/08/export-sharepoint-list-data-into-csv.html

    I hope this helps.

    Thanks,

    Wendy

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wendy Li
    TechNet Community Support


    Wednesday, July 02, 2014 9:37 AM
    Moderator
  • Well it took a while but try this script, i'll be chucking up a post on my blog to match.

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue
    
    #BlackList Group Names
    #These are the known groups you get in a non publishing team site:
    $blackList = @(
        "_Hidden",                                                                         
        "Base Columns",                                                                    
        "Content Feedback",                                                   
        "Core Contact and Calendar Columns",                                               
        "Core Document Columns",                                                         
        "Core Task and Issue Columns",                                                          
        "Custom Columns",                                                            
        "Display Template Columns",                                                          
        "Document and Record Management Columns",                                                
        "Enterprise Keywords Group",                                                             
        "Extended Columns",                                                             
        "JavaScript Display Template Columns",                                                   
        "Reports",                                                                  
        "Status Indicators"
    )
    
    #Get the web
    $web = Get-SPWeb "http://portal.tracy.com/sites/cthub"
    
    #Get the columns (note, these are named fields) 
    #Also filter out the sealed fields
    $Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
            -not $_.Sealed -and $_.Group -ne "_Hidden"
        }
    
    
    #Print out the number of columns
    Write-Host "Number of columns found: " $Columns.Count
    
    #Create empty array
    $ColumnDetailsArray = @()
    
    #Loop through each choice and print the name
    foreach ($entry in $columns)
    {
        $choicesArray = @()
        Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
        
        #Loop through the choices and print those out
        foreach ($choice in $entry.Choices)
        {
            #Add each choice to the (local) array
            Write-Verbose ("  Choice: {0}" -f $choice)
            $choicesArray += $choice
        }
        #Create a result object to hold our data
        $ColumnDetailsArray += New-Object PSObject -Property @{
                            "Name" = $entry.InternalName
                            "Group" = $entry.Group
                            "Choices" = $choicesArray
                        }
    }
    #Create a starter XML for the system to work with
    [xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
        (Get-Date -f ddMMyyyy), 
        $web.Title
    
    foreach ($group in $ColumnDetailsArray | select Group -Unique)
    {
        $groupText = $group.Group
    
        #Check to see if the group name is in our blacklist
        if (-not $blackList.Contains($groupText))
        {
            Write-Verbose "Group name: " $groupText
            $groupElement = $xml.CreateElement("Group")
            $groupElement.SetAttribute("Name", $groupText)
        
            #loop through the results and genearate an xml
            foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
            {
                #Create an element to hold the top level item
                $columnElement = $xml.CreateElement("Choice")
                $columnElement.SetAttribute("Name", $column.Name)
    
                #Loop through the choices and add entries for each
                foreach ($choice in $column.Choices)
                {
                    $choiceElement = $xml.CreateElement("Choice")
            
                    $choiceElement.InnerText = $choice
                    #Note that you need this Pipe Out-Null to prevent it writing to the console
               $columnElement.AppendChild($choiceElement) | Out-Null } #Once it's built add the element to the root node $groupElement.AppendChild($columnElement) | Out-Null } $xml.root.AppendChild($groupElement) | Out-Null } else { Write-Verbose "Group skipped:" $groupText } } $xml.Save("C:\ResultFolder\ColumnSummary.xml")


    Friday, July 04, 2014 5:43 PM

All replies

  • Always.

    Try this to start you off:

    #Get the web
    $web = Get-SPWeb "http://sharepoint.domain.com/sites/sitecollection/subsite"
    
    #Get the columns (note, these are named fields)
    $Columns = $web.Fields | ? { $_.TypeName -eq "Choice"}
    
    #Print out the number of columns
    Write-Host "Number of columns found: " $Columns.Count
    
    #Loop through each choice and print the name
    foreach ($entry in $columns)
    {
    Write-Host ("Field Name: {0}" -f $entry.InternalName)
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
    Write-Host ("  Choice: {0}" -f $choice)}
    }

    It's not tested but it should be most of what you need. Give it a try and post up your code once you've got it working.


    Edit: one warning, that'll get all choice columns, not just the custom ones. They're difficult to pick out.
    • Edited by Alex Brassington Monday, June 23, 2014 10:32 PM
    • Proposed as answer by sk2014 Tuesday, June 24, 2014 10:12 AM
    • Unproposed as answer by Imnot4D2 Tuesday, June 24, 2014 8:11 PM
    Monday, June 23, 2014 10:24 PM
  • Thanks for the quick reply.  It doesn't put out any values just blank entries for

    Number of columns found:

    Field Name:

    Choice:

    Not sure what I need to add to your script as I'm a Powershell novice.

    Tom

    Tuesday, June 24, 2014 2:08 PM
  • Hi Tom,

    Use the following PowerShell command, you can export the values of the columns from a list into a .csv file:

    $MyWeb = Get-SPWeb "http://sitename"
    $MyList = $MyWeb.Lists["ListName"]
    $exportlist = @()
    $Mylist.Items | foreach {
    $obj = New-Object PSObject -Property
     @{            
     “Column1” = $_["Column Name in SharePoint"]            
    "Column2" = $_["Column Name in SharePoint"]                   
    }
    $exportlist += $obj  
    $exportlist | Export-Csv -path 'C:\FilePath\Filename.csv'}

    More information, please refer to the links:

    http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/A_9182-Export-data-from-SharePoint-2010-List-using-Management-Shell.html

    http://meandmysharepoint.blogspot.com/2012/08/export-sharepoint-list-data-into-csv.html

    I hope this helps.

    Thanks,

    Wendy

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wendy Li
    TechNet Community Support


    Wednesday, July 02, 2014 9:37 AM
    Moderator
  • Well it took a while but try this script, i'll be chucking up a post on my blog to match.

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue
    
    #BlackList Group Names
    #These are the known groups you get in a non publishing team site:
    $blackList = @(
        "_Hidden",                                                                         
        "Base Columns",                                                                    
        "Content Feedback",                                                   
        "Core Contact and Calendar Columns",                                               
        "Core Document Columns",                                                         
        "Core Task and Issue Columns",                                                          
        "Custom Columns",                                                            
        "Display Template Columns",                                                          
        "Document and Record Management Columns",                                                
        "Enterprise Keywords Group",                                                             
        "Extended Columns",                                                             
        "JavaScript Display Template Columns",                                                   
        "Reports",                                                                  
        "Status Indicators"
    )
    
    #Get the web
    $web = Get-SPWeb "http://portal.tracy.com/sites/cthub"
    
    #Get the columns (note, these are named fields) 
    #Also filter out the sealed fields
    $Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
            -not $_.Sealed -and $_.Group -ne "_Hidden"
        }
    
    
    #Print out the number of columns
    Write-Host "Number of columns found: " $Columns.Count
    
    #Create empty array
    $ColumnDetailsArray = @()
    
    #Loop through each choice and print the name
    foreach ($entry in $columns)
    {
        $choicesArray = @()
        Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
        
        #Loop through the choices and print those out
        foreach ($choice in $entry.Choices)
        {
            #Add each choice to the (local) array
            Write-Verbose ("  Choice: {0}" -f $choice)
            $choicesArray += $choice
        }
        #Create a result object to hold our data
        $ColumnDetailsArray += New-Object PSObject -Property @{
                            "Name" = $entry.InternalName
                            "Group" = $entry.Group
                            "Choices" = $choicesArray
                        }
    }
    #Create a starter XML for the system to work with
    [xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
        (Get-Date -f ddMMyyyy), 
        $web.Title
    
    foreach ($group in $ColumnDetailsArray | select Group -Unique)
    {
        $groupText = $group.Group
    
        #Check to see if the group name is in our blacklist
        if (-not $blackList.Contains($groupText))
        {
            Write-Verbose "Group name: " $groupText
            $groupElement = $xml.CreateElement("Group")
            $groupElement.SetAttribute("Name", $groupText)
        
            #loop through the results and genearate an xml
            foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
            {
                #Create an element to hold the top level item
                $columnElement = $xml.CreateElement("Choice")
                $columnElement.SetAttribute("Name", $column.Name)
    
                #Loop through the choices and add entries for each
                foreach ($choice in $column.Choices)
                {
                    $choiceElement = $xml.CreateElement("Choice")
            
                    $choiceElement.InnerText = $choice
                    #Note that you need this Pipe Out-Null to prevent it writing to the console
               $columnElement.AppendChild($choiceElement) | Out-Null } #Once it's built add the element to the root node $groupElement.AppendChild($columnElement) | Out-Null } $xml.root.AppendChild($groupElement) | Out-Null } else { Write-Verbose "Group skipped:" $groupText } } $xml.Save("C:\ResultFolder\ColumnSummary.xml")


    Friday, July 04, 2014 5:43 PM
  • Hi Alex,

    Might be worth getting this in the solutions gallery and posting this to the Wiki too.  It's a great solution

    Cheers
    Steven


    Steven Andrews
    SharePoint Business Analyst: LiveNation Entertainment
    Blog: baron72.wordpress.com
    Twitter: Follow @backpackerd00d
    My Wiki Articles: CodePlex Corner Series
    Please remember to mark your question as "answered" if this solves (or helps) your problem.

    Thursday, July 10, 2014 12:36 PM
    Answerer
  • Thanks, I've spotted a few things to clean up but once I've done that i'll add it to the repository and wiki.

    Thursday, July 10, 2014 12:43 PM