Import content from a CSV using Sitecore PowerShell Extensions.

ImportWizardFromCSV.ps1

<#
    .SYNOPSIS
        Data Import Wizard provides a way to generate or update content from an external file.
        
    .DESCRIPTION
        The import file uses the properties "Name" and "Id" to help match existing items.
        
    .NOTES
        Requires Sitecore PowerShell Extensions 4.6 or newer.
        
    .NOTES
        Michael West
#>

function Write-LogExtended {
    param(
        [string]$Message,
        [System.ConsoleColor]$ForegroundColor = $host.UI.RawUI.ForegroundColor,
        [System.ConsoleColor]$BackgroundColor = $host.UI.RawUI.BackgroundColor
    )
    
    Write-Log -Object $message
    Write-Host -Object $message -ForegroundColor $ForegroundColor -BackgroundColor $backgroundColor
}
$defaultProps = @{
    "Title" = "Data Import Wizard"
    "Icon" = "Apps/32x32/UI.png"
    "ShowHints" = $true
}

$selection = [ordered]@{
    "<b>Import</b> and update items using a specified Template."=1;
    "<b>Update</b> existing items based on the item <b>Id</b>, item <b>Name</b>, or field name."=2;}
$props = @{
    "Parameters" = @(
        @{ Name = "info"; Title = "Getting Started"; Value = "The <b>Data Import Wizard</b> provides two modes of operation to help you avoid manual data entry."; Editor = "info" },
        @{ Name = "wizardMode"; Title = "Wizard Mode"; Value = "2"; Options=$selection; Editor="radio" }
    )
    "Description" = "Get started with the Data Import Wizard by choosing the mode below."
    "OkButtonName" = "Next"
}
$result = Read-Variable @props @defaultProps

if($result -ne "ok") { exit }

$uploadDirectory = Join-Path -Path $SitecoreDataFolder -ChildPath "temp"
$importFilePath = Receive-File -Overwrite -Title $defaultProps.Title -Description "Choose a valid CSV file to import." -Icon $defaultProps.Icon -Path $uploadDirectory -OkButtonName "Next"

if(-not(Test-Path -Path $importFilePath)) { exit }

$importData = Import-CSV $importFilePath
$columnNames = $importData | Get-Member -MemberType NoteProperty | Where-Object { "Name","Id" -notcontains $_.Name } | Select-Object -ExpandProperty Name
$sourceFieldNames = $columnNames | ForEach-Object { $mappings = [ordered]@{} } { $mappings[$_] = $_ } { $mappings }
$fieldMappings = $sourceFieldNames

# Create a list of field names used as an alternative to Id and Name.
$customKeyOptions = [ordered]@{"-- Skip --" = ""} + $sourceFieldNames

# Used to determine where queries should start.
$destinationNode = @{$true=(Get-Item -Path "master:\content\home");$false=(Get-Item -Path "master:\content")}[(Test-Path -Path "master:\content\home")]

# Create a list of field names on the Standard Template. This will help us filter out extraneous fields.
$standardTemplate = Get-Item -Path "master:" -ID "{1930BBEB-7805-471A-A3BE-4858AC7CF696}"
$standardTemplateTemplateItem = [Sitecore.Data.Items.TemplateItem]$standardTemplate
$standardFields = $standardTemplateTemplateItem.OwnFields + $standardTemplateTemplateItem.Fields | Select-Object -ExpandProperty key -Unique

if($wizardMode -eq 1) {

    $props = @{
        "Parameters" = @(
            @{ Name = "info"; Title = ""; Value = "Import new items matched with the specified Template. You can avoid creating duplicates by providing the item <b>Id</b>, item <b>Name</b>, or field name."; Editor = "info" },
            @{ Name = "destinationNode"; Title = "Destination Node"; Value = $destinationNode; Tooltip = "Represents the parent node in the tree where new items should be created."; Editor = "droptree"; Mandatory = $true; Source = "datasource=/sitecore/content" },
            @{ Name = "templateItem"; Title = "Item Type"; Value = (Get-Item -Path "master:" -ID "{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}"); Tooltip = "Represents the Template used for new item creation and allows for automatic field matching."; Editor = "droptree"; Mandatory = $true; Source = "datasource=/sitecore/templates"},
            @{ Name = "customKey"; Title = "Field Name"; Value = ""; Tooltip = "Alternative to using the <b>Id</b> choose a field name as the unique identifier for matching items; typically a field found in an external system."; Options = $customKeyOptions; Editor = "combo" }
        )
        "Description" = "Add new items at the specified location."
        "OkButtonName" = "Next"
        "Height" = 450
    }
    $result = Read-Variable @props @defaultProps
    
    if($result -ne "ok") { exit }
    
    if($templateItem -eq $null) { exit }
    
    $selectedTemplateItem = [Sitecore.Data.Items.TemplateItem]$templateItem
    $selectedTemplateFields = $selectedTemplateItem.OwnFields + $selectedTemplateItem.Fields | Select-Object -ExpandProperty key -Unique
    
    $filterFields = $selectedTemplateFields | Where-Object { $standardFields -notcontains $_ } | Sort-Object
    $lookupNames = @{}
    $templateFieldNames = [ordered]@{"-- Skip --"=""}
    $filterFields | ForEach-Object { 
        $templateFieldNames[$_] = $_
        $compressedName = $_.Replace("-","").Replace("_","").Replace(".","").Replace(" ","")
        $lookupNames[$compressedName] = $_
    }
    
    $fieldMappingPrefix = "fieldMapping-"
    
    $parameters = @()
    $parameters += @{ Name = "info"; Title = "Field Mapping"; Value = "Each label indicates a field in the CSV. Choose from the dropdown the appropriate <b>$($selectedTemplateItem.Name)</b> Template field."; Editor = "info" }
    foreach($sourceFieldName in $sourceFieldNames.Keys) {
        $compressedImportName = $sourceFieldNames[$sourceFieldName].Replace("-","").Replace("_","").Replace(".","").Replace(" ","")
        $destinationFieldName = $lookupNames[$compressedImportName]
        if(!$destinationFieldName) { $destinationFieldName = "" }
        $parameters += @{ Name = "$($fieldMappingPrefix)$($sourceFieldName)"; Title="$($sourceFieldName)"; Options=$templateFieldNames; Value=$destinationFieldName; Columns=6;}
    }
    
    $props = @{
        "Parameters" = $parameters
        "Description" = "Map fields from CSV to $($selectedTemplateItem.Name) Template."
        "OkButtonName" = "Next"
    }
    $result = Read-Variable @props @defaultProps
    
    if($result -ne "ok") { exit }
    
    $fieldMappings = Get-Variable -Name "$($fieldMappingPrefix)*" | 
        Where-Object { $_.Value } | ForEach-Object { $mappings = [ordered]@{} } { $mappings[$_.Name.Replace($fieldMappingPrefix,"")] = $_.Value } { $mappings }
} elseif ($wizardMode -eq 2) {
    
    $stopOptions = @{
        "Item not found" = 1
        "Item missing field" = 2
        "Item has a duplicate" = 3
    }
    $props = @{
        "Parameters" = @(
            @{ Name = "info"; Title = ""; Value = "The import relies on the <b>Id</b> to match items. Optionally, an item <b>Name</b> or field name can be used as a fallback if the <b>Id</b> is missing or unknown. Using the item <b>Id</b> should dramatically reduce update times and the risk of updating the wrong items."; Editor="info" },
            @{ Name = "destinationNode"; Title = "Destination Node"; Value = $destinationNode; Tooltip = "Narrow the updates to a specific part of the tree when using the item <b>Name</b> or field name."; Editor = "droptree"; Mandatory = $true; Source = "datasource=/sitecore/content" },
            @{ Name = "customKey"; Title = "Field Name"; Value = ""; Tooltip = "Choose a field name used as the unique identifier for matching items. This is typically a field found in an external system."; Options = $customKeyOptions; Editor = "combo" },
            @{ Name = "stopConditions"; Title="Stop Conditions"; Options=$stopOptions; Tooltip="Choose the conditions that should halt the updates."; Editor="checklist"}
        )
        "Description" = "Choose where to scope the updates."
        "OkButtonName" = "Run"
    }
    $result = Read-Variable @props @defaultProps
    
    if($result -ne "ok") { exit }
}

function Check-Condition {
    param(
        [int]$Condition
    )
    
    if($stopConditions -contains $Condition) {
        $reason = $stopOptions.GetEnumerator()| Where-Object { $_.Value -eq $Condition } | Select-Object -First 1
        Write-Host "Stop condition: $($reason.Name)"; exit 
    }
}

Write-LogExtended "Running wizard in powerful ways."

New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) {
    $rowIndex = 0
    $useCustomKey = ![string]::IsNullOrEmpty($customKey)
    
    foreach ($row in $importData) {
        Write-Host ""
        $rowIndex++
        $lookupKey = @{$true=$row.Id;$false=$row.Name}[![string]::IsNullOrEmpty($row.Id)] 

        $item = $null
        if([Sitecore.Data.ID]::IsID($lookupKey)) {
            Write-LogExtended "> Find item by Id: $($lookupKey)"
            $itemPath = "master:\$($lookupKey)"
            if(Test-Path -Path $itemPath) {
                $item = Get-Item -Path $itemPath
            }
        } elseif($useCustomKey -and $row.$customKey) {
            Write-LogExtended "> Find item by $($customKey): $($row.$customKey)"
            $itemPath = "$($destinationNode.ItemPath)//*[@$($customKey) ='$($row.$customKey)']"
            $item = Get-Item -Path "master:" -Query $itemPath
        } else {
            if(![string]::IsNullOrEmpty($lookupKey)) {
                Write-LogExtended "> Find item by Name: $($lookupKey)"
                $itemPath = "$($destinationNode.ItemPath)//*[@@name='$($lookupKey)']"
                $item = Get-Item -Path "master:" -Query $itemPath
            }
        }
        
        if($item -eq $null) {
            if($wizardMode -eq 1) {
                $itemPath = "$($destinationNode.ItemPath)/$($row.Name)"
                Write-LogExtended "[A] $($itemPath)" -ForegroundColor Green
                if([Sitecore.Data.ID]::IsID($lookupKey)) {
                    $item = New-Item -Path $itemPath -ItemType $templateItem.ID -ForceId $row.Id
                } else {
                    $item = New-Item -Path $itemPath -ItemType $templateItem.ID
                }
            } else {
                Check-Condition -Condition 1
                Write-LogExtended "> Item Path $($itemPath) not found. If searching by a custom field perhaps the field name is mismatched." -ForegroundColor White -BackgroundColor Red
            }
        } else {
            if(($item).Count -gt 1) {
                $item = $item | Select-Object -First 1
                Check-Condition -Condition 3
                Write-LogExtended "> Found duplicate items when matching. Taking the first item $($item.ItemPath)" -ForegroundColor Yellow
            }
            
            Write-LogExtended "[U] $($item.ItemPath)"
            foreach($fieldKey in $fieldMappings.Keys) {
                $fieldName = $fieldMappings[$fieldKey]
                
                if($item.Fields[$fieldName]) {
                    $item.$fieldName = $row.$fieldKey
                } else {
                    $compressedName = $fieldName.Replace("-","").Replace("_","").Replace(".","").Replace(" ","")
                    if($item.Fields[$compressedName]) {
                        Write-LogExtended "> Matched field '$($fieldName)' to '$($compressedName)'. Consider updating CSV to match template field names." -ForegroundColor Yellow
                        $item.$compressedName = $row.$fieldKey
                    } else {
                        Check-Condition -Condition 2
                        Write-LogExtended "> Skipped missing field $($fieldKey)." -ForegroundColor Yellow
                    }
                }
            }
        }
    }
}   

samplecontent.csv

• Add ability to match by custom field such as UserId.
• Check for existence of the home item in the droptree selectors.
• Query should use double slash //*
• Fix issue with $customKey not matching because of the .Replace(...)..