Sitecore: Import CSV Data using Powershell extensions

What is Sitecore Powershell Extensions (SPE)?
SPE is a Sitecore development accelerator tool which provides a command line interface and scripting environment capable of making native calls to the Sitecore API.

Powershell has an Import-CSV command that can be used to read a CSV file to a collection. You can then loop over the collection of rows and do the desired operation on each row. If you data is in Excel or any other spreadsheet format export it to CSV first.

Lets say we need to import a list of Key/Value pairs (Dictionary Entry) in to Dictionary Folder into Sitecore. The CSV file will need to have two columns called Key and Phrase. We can use the US States list as an example dataset. Here is a partial screenshot of the Dataset. 

We’ll be importing this data set into a Dictionary Folder in the Sitecore Content tree. Create a Dictionary Folder item using the Dictionary Folder template located at /sitecore/templates/System/Dictionary/Dictionary folder. Name the Dictionary folder item as States. We’ll be putting all the imported states into this folder.

Powershell Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
$dropListPath =  "master:/sitecore/content/GlobalData/DropListOptions/States"

# Get the item for the Dictionary Folder Container
$dictionaryFolder = Get-Item -Path $dropListPath

#
# Read CSV file
# 1st row needs to have header of Key, Phrase. Other option is to specify the header on the Import-CSV method call using the -Header string[] parameter
#

$csv = Import-CSV "C:\inetpub\wwwroot\powerproducts2016\Data\States.csv" -delimiter ","

#
# Wrap the foreach loop in the BulkUPdateContext so we do not fire events and prevent indexing after individual create/update opertaion
#
New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) {
   
    foreach($row in $csv) {
       
        $name = SanitizeName $row.Key
        $itemPath = $dropListPath + "/" + $name
       
        $item = Get-Item -Path $itemPath -ErrorAction SilentlyContinue
       
        #check is item already exits
        if($item -eq $null)
        {
            Write-Host ("Creating New Item at: " +  $itemPath)
            Write-Host ("Name:" + $name)
            try
            {
                $item = New-Item -Path $itemPath -ItemType "/sitecore/templates/System/Dictionary/Dictionary entry"
                Write-Host "Item created: " $item.Name
            }
            catch{
                Write-Host "Failed to create Item: " $item.Name
                Write-Host $_.Exception.Message
            }
        }
        else{
            Write-Host ("Updating Item:" + $item.Name)
        }
        $item.Editing.BeginEdit()
        $item["Key"] = $row.Key
        $item["Phrase"] = $row.Phrase
        $item.Editing.EndEdit() | Out-Null # we don't want to send the output to the console otherwise it will print False in the console.
    }
}

#
# Clean the name in case it has restricted characters
# Todo: use Sitecore.Data.Items.ItemUtil.ProposeValidItemName($args[0])
#
function SanitizeName{
 $args[0].Replace(".", "").Replace(" ", "").Replace("/", "").Replace("-", "").Replace("&", "").Replace(":", "").Replace("""", "").Replace("#", "")
}

Sitecore PowerShell ISE Output


Sitecore Content Tree after running the script

First we get the reference to the Dictionary Folder followed by the actual call to the Import-CSV powershell method.  Sitecore.Data.BulkUpdateContext is used to optimize in cases where there may be a lot of data and you desire to suppress events and indexing. For each row in the imported CSV we try and see if the item already exists. -ErrorAction SilentlyContinue parameter to the Get-Item method will make it not throw an exception and we can just do a null check instead. The script then insert or updates the row depending on whether it exists or not. We also sanitize the name prior to creating an item incase our Key has invalid characters.