Skip to content

Instantly share code, notes, and snippets.

@michaellwest
Last active June 4, 2024 19:13
Show Gist options
  • Save michaellwest/453165b6f92db2b6add8553fa291679d to your computer and use it in GitHub Desktop.
Save michaellwest/453165b6f92db2b6add8553fa291679d to your computer and use it in GitHub Desktop.
Import content from a CSV using Sitecore PowerShell Extensions.
<#
.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
}
}
}
}
}
}
Id Name External Id Title Content
Sample Item 1 1 Everyday is a great day <p>This is the day that the lord has made. I will rejoice and be glad in it.</p>
Sample Item 2 This is the second day. <p>It is good.</p>
{0DF11E8F-2E94-484F-A774-7A4BB5B227A8} Sample Item 3 3 This is the third day. <p>It is even better.</p>
@michaellwest
Copy link
Author

michaellwest commented Jul 24, 2017

  • 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(...)..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment