Sitecore Powershell Extensions - Upload data to Sitecore from a csv file

As Sitecore developers or content managers working with large amounts of data we often need a way to update items in bulk using CSV files. In this blog post, I'll walk you through how to use a script to update fields in Sitecore items based on a corresponding CSV file entry.

In this specific example, we will use Price Codes already existing in Sitecore items and update the corresponding Price field. However, this script can be extended to support different scenarios.

The final version of the script is the following:

 $fieldRequiredValidator = {  
     if([string]::IsNullOrEmpty($variable.Value)){  
       $variable.Error = "Please provide a value."  
     }  
   }  
   $dialogProps = @{  
     Parameters = @(  
                @{ Name = "itemPath"; Title="Root"; Tooltip="The root of the items to be updated"; Source="Datasource=/sitecore/content/"; editor="droptree";},  
       @{ Name = "csvPath"; Value=""; Title="Search Text"; Tooltip="The path to the csv file in the server"; Placeholder="CSV path"; Columns=12; Validator=$fieldRequiredValidator;}  
     )  
     Description = "This wizard allows you to select a csv file to load prices based on a price code"  
     Title = "Price Uploader"  
     Width = 700  
     Height = 575  
     OkButtonName = "Proceed"  
     CancelButtonName = "Abort"  
     ShowHint = $true  
     Icon = [regex]::Replace($PSScript.Appearance.Icon, "Office", "OfficeWhite", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)  
   }  
   $result = Read-Variable @dialogProps  
   if($result -ne "ok") {  
     Exit  
   }  
 
 $csvData = Import-CSV $csvPath   
 $items = Get-ChildItem -Item $itemPath -Recurse | Where-Object { $_.TemplateName -eq "TEMPLATE NAME GOES HERE" -Or $_.TemplateName -eq "TEMPLATE2 NAME GOES HERE"}  
 [System.Collections.ArrayList]$reportItems = @()  

 foreach ($item in $items) {  
   $itemId = $item.ID 
   $csvRow = $csvData | Where-Object { $_.PriceCode -like $item["PriceCode"] } | Select-Object -First 1  
   if ($csvRow -and ![string]::IsNullOrEmpty($item["PriceCode"])) {  
     $price = $csvRow.Price  
     Write-Host "Updating $($item.Paths.Path) - $($price)"  
     $item.Editing.BeginEdit()  
     $item["Price"] = $price  
     $item.Editing.EndEdit()  
     $reportItems.Add($item)  
   }  
 }  
   $reportProps = @{  
     Property = @(  
       "ID","Name","ItemPath","PriceCode","Price"  
     )  
     Title = "Items Report"  
     InfoTitle = "Details"  
     InfoDescription = "The following report shows the items that got price updates"  
   }  
   $reportItems | Show-ListView @reportProps  
   Close-Window  

Before we proceed with using the script, let's take a look at what it does. The script performs the following steps:

1. 1. Presents a dialog box to the user, allowing them to select a CSV file containing price information and the location where the items to be updated live.
2. 
   
3. 
   
   
4. 2. Loads the CSV data and identifies the items to update based on a specific template. The template names have to be specified in the script where the "TEMPLATE NAME GOES HERE" placeholders are.

$items = Get-ChildItem -Item $itemPath -Recurse | Where-Object { $_.TemplateName -eq "TEMPLATE NAME GOES HERE" -Or $_.TemplateName -eq "TEMPLATE2 NAME GOES HERE"}

1. 3. Updates the field value "Price" for each item based on the corresponding PriceCode row in the CSV data.

foreach ($item in $items) {  
   $itemId = $item.ID 
   $csvRow = $csvData | Where-Object { $_.PriceCode -like $item["PriceCode"] } | Select-Object -First 1  
   if ($csvRow -and ![string]::IsNullOrEmpty($item["PriceCode"])) {  
     $price = $csvRow.Price  
     Write-Host "Updating $($item.Paths.Path) - $($price)"  
     $item.Editing.BeginEdit()  
     $item["Price"] = $price  
     $item.Editing.EndEdit()  
     $reportItems.Add($item)  
   }  
 }  

1. 4. Generates a report showing the items that were updated along with their relevant details.

Automating repetitive tasks is crucial for enhancing productivity in Sitecore content management. The "Price Uploader" script demonstrated in this blog post simplifies the process of updating items in Sitecore using CSV files. Feel free to modify and extend this script to suit your unique requirements.

Happy scripting!