Export Sitecore Item Data using Sitecore PowerShell

I heard a lot about Sitecore PowerShell – it’s the great tool and one of the best module on Sitecore Marketplace. Also the most downloaded module crossing SIM. One fine day I was installing SXA, that also said — Before you install the SXA package, please make sure that you have installed: Sitecore PowerShell extensions (full 4.5 for Sitecore 8) from https://marketplace.sitecore.net/Modules/S/Sitecore_PowerShell_console.aspx. Then I started exploring PowerShell from the Video Series by Michael West on YouTube. These are awesome videos to learn and understand Sitecore PowerShell. And once you have viewed the videos, after that when you look at the script, you will easily understand it. For every Sitecore Developer, it’s worth exploring Sitecore PowerShell. You’ll really like and enjoy with it. You won’t open Visual Studio to code, you will only open Sitecore for scripting – as it says – Sitecore PowerShell ISE (Integrated Scripting Environment).

 

Export Sitecore Items Data — We are using this tool a lot to give the reports or data to the client, created in a .aspx file – It was written by my friend. And then I modified it based on requirements. After exploring PowerShell. I thought to implement it in PowerShell. And it got ready in couple of hours as I knew how to work with Sitecore PowerShell 😉

Here we go:

Below script will tell me what item language(s) are present under the current context item and store the result in variable $languageTypes as a Hashtable.

$SubItemLanguages = Get-ChildItem -Recurse . -Language * -Version * | Sort-Object | Select-Object {$_.Language} -unique

#Hashtable
$languageTypes = @{}

Foreach($language in $SubItemLanguages) { 
    $textData = $language.'$_.Language';
    $languageTypes[$textData.Title] = $textData.Name
}

$languageTypes

Below script will tell me what item template(s) are present under the current context item and store template name and template Id in a variable called $templateTypes as a Hashtable. I’ve added a condition for not to include folder item template Id!

$SubItemTemplates =  Get-ChildItem -Recurse . -Language * -Version * | Sort-Object | Select-Object {$_.TemplateID}, {$_.TemplateName}

#Hashtable
$templateTypes = @{}

Foreach($itemTemplate in $SubItemTemplates) { 
    if($itemTemplate.'$_.TemplateID' -ne "{A87A00B1-E6DB-45AB-8B54-636FEC3B5523}"){
    $templateTypes[$itemTemplate.'$_.TemplateName'] = $itemTemplate.'$_.TemplateID'
    }
}

$templateTypes

We then check whether any of them (templateTypes or languageTypes) are having the count more than one — i.e Are there any items having more than one language versions or are many items with different template underneath current context item? If yes — then we’ll provide a dialog to select the language/template if any.

If there are only one language and template items then no need show prompt, just take the value and proceed further.

if($languageTypes.Count -gt 1 -or $templateTypes.Count -gt 1){

$props = @{
    Parameters = @(
        @{Name="languageToCheck"; Title="Select Language"; Options=$languageTypes; Tooltip="Selected Item contain sub-items with the following languages. Which language items you would like to export?"; Editor="combobox"} 
        @{Name="templateToCheck"; Title="Select Template"; Options=$templateTypes; Tooltip="Selected Item contain sub-items with the following templates. Which template items you would like to export?"; Editor="combobox"} 
    )
    Title = "Export Item"
    Description = "Choose the criteria for the report."
    Width = 550
    Height = 300
    ShowHints = $true
}


$result = Read-Variable @props

if($result -eq "cancel"){
    exit
}

}
else{
    $templateToCheck = $SubItemTemplates.'$_.TemplateID'
    $languageToCheck = $SubItemLanguages.'$_.Language'
}

 

 

Now based on the data we have following script will fetch items data and provide an option to export data to Excel, CSV, HTML, JSON, and XML.

$SubItems = Get-ChildItem -Language $languageToCheck -Version * -Recurse . | Where-Object { $_.TemplateID -match $templateToCheck  } 

$SubItems

if($SubItems.Count -eq 0){
    Show-Alert "No items found!"
} else {
    $props = @{
        Title = "Export Item"
        InfoTitle = "Total $($SubItems.Count) items found!"
        InfoDescription = "Export Item Data"
        PageSize = 100
    }
    [string[]] $columns = "ItemPath"
    [string[]] $columns +=  "Language"
    $fields = $SubItems | Get-ItemField
    [string[]] $columns +=  $fields
    
    $SubItems |
        Show-ListView @props -Property $columns
}

Show-ListView — Makes life very simple!

Note: Make sure you select the appropriate context item in PowerShell before you execute this script. Here I’m adding two additional columns “ItemPath” and “Language” along with the fields. These two columns data will be helpful when we want to import data back to Sitecore. Based on these two columns respected item and language version should get updated when we import the data.

 

 

Below is the full script — You can execute it in Powershell SPE.

$SubItemLanguages = Get-ChildItem -Recurse . -Language * -Version * | Sort-Object | Select-Object {$_.Language} -unique

#Hashtable
$languageTypes = @{}

Foreach($language in $SubItemLanguages) { 
    $textData = $language.'$_.Language';
    $languageTypes[$textData.Title] = $textData.Name
}

$SubItemTemplates =  Get-ChildItem -Recurse . -Language * -Version * | Sort-Object | Select-Object {$_.TemplateID}, {$_.TemplateName}

#Hashtable
$templateTypes = @{}

Foreach($itemTemplate in $SubItemTemplates) { 
    if($itemTemplate.'$_.TemplateID' -ne "{A87A00B1-E6DB-45AB-8B54-636FEC3B5523}"){
    $templateTypes[$itemTemplate.'$_.TemplateName'] = $itemTemplate.'$_.TemplateID'
    }
}

if($languageTypes.Count -gt 1 -or $templateTypes.Count -gt 1){

$props = @{
    Parameters = @(
        @{Name="languageToCheck"; Title="Select Language"; Options=$languageTypes; Tooltip="Selected Item contain sub-items with the following languages. Which language items you would like to export?"; Editor="combobox"} 
        @{Name="templateToCheck"; Title="Select Template"; Options=$templateTypes; Tooltip="Selected Item contain sub-items with the following templates. Which template items you would like to export?"; Editor="combobox"} 
    )
    Title = "Export Item"
    Description = "Choose the criteria for the report."
    Width = 550
    Height = 300
    ShowHints = $true
}

$result = Read-Variable @props

if($result -eq "cancel"){
    exit
}

}
else{
    $templateToCheck = $SubItemTemplates.'$_.TemplateID'
    $languageToCheck = $SubItemLanguages.'$_.Language'
}

$SubItems = Get-ChildItem -Language $languageToCheck -Version * -Recurse . | Where-Object { $_.TemplateID -match $templateToCheck  } 

$SubItems

if($SubItems.Count -eq 0){
    Show-Alert "No items found!"
} else {
    $props = @{
        Title = "Export Item"
        InfoTitle = "Total $($SubItems.Count) items found!"
        InfoDescription = "Export Item Data"
        PageSize = 100
    }
    [string[]] $columns = "ItemPath"
    [string[]] $columns +=  "Language"
    $fields = $SubItems | Get-ItemField
    [string[]] $columns +=  $fields
    
    $SubItems |
        Show-ListView @props -Property $columns
}
Close-Window

Sitecore MarketPlace Module — Sitecore Item Exporter

This module contains Sitecore Item at following Path:

/sitecore/system/Modules/PowerShell/Script Library/Export Items/Content Editor/Context Menu/Export Items

Once you install this module — You’ll get an option to export Sitecore Items in the context menu.

Note: I’ve restricted the accessibility for “Export Items” option only to Administrator and for items below the Content Item. You can easily provide the access for this option to any role or specific items by updating the Rule.

Kudos to SPE Team!