Content Import with Powershell: Treasure Hunt

I recently had to import various data sets as items into Sitecore. There are many tools one can use but I wanted something very simple, something readily available, and yet something powerful and flexible enough to build a reusable component. I figured I would use Sitecore Powershell Extensions.

For a treasure hunt we need a map, a few clues, and, of course, a treasure. Let’s go!

The Map

Wouldn’t it be great if we could:

  • Export data sets as CSV files and upload it into media library
  • Tell the import tool to take a CSV file and mail merge it with a data template matching column names with fields
  • Point the import tool at a location in the item tree and ask it to create items for each data record. And if asked nicely the tool would also output a processing log.

The Clues

Headers

First, make sure the CSV files have headers. The script will match the names to the data template so you won’t have to do anything extra. And make sure there’s a Name column. We will use this one to name the items:

"Id","Number","Name","Code"
2000437132,"2002622645","""DELL'ANNUNZIATA""-DR.D'ALESSANDRO N",90
2000387321,"2000137432","'LULU''SNC DI RAFFAELI",70
2000389293,"2002372796",".CIA DEL VECCHIO FRANCONE CAROLINA",75
2000387124,"2002373114","2 C  SPA",10

Name Normalization

There can be all kinds of symbols in the Name column so make sure you normalize it before you use it to name items. You can retain the original name in the Display Name. In my case a two step regex with a trim was all I needed:

function NormalizeName($name)
{
    $name = $name -replace "[^a-zA-Z0-9]", " " -replace "s+", ""
    
    return $name.Trim()
}

I am bucketing these items using a “three levels name” strategy so that’s why I don’t even leave spaces in there.

CSV -> Object

In Powershell it’s one line to read your data stream into a collection of typed objects:

$csv = [System.Text.Encoding]::Default.GetString($contents) | ConvertFrom-Csv

Object -> Item

I always liked convention over configuration. For the majority of cases a simple name-to-name translation is all you’d need. Let’s populate all item fields with a one-liner:

$record | ` 
    Get-Member -MemberType Properties | `
    ForEach-Object { $item["$($_.Name)"] = $record."$($_.Name)" }

BulkUpdateContext

Last but not least, make sure you silence index and events with BulkUpdateContext. That’s how your *.update packages are installed by the way. Always wondered why regular package installation wizard didn’t have an option to put the process into the bulk context, WFFM install would go a whole lot faster, for example. Anyway:

$bulk = New-Object "Sitecore.Data.BulkUpdateContext"

try 
{
    # run import
}
finally
{
    $bulk.Dispose()
}

The Treasure

Here’s a full script:

A few notes before I go. SPE is capable of handling uploads if you decide not to pre-upload your data. Use Receive-Files. The item editor in Read-Variable requires that the variable be of type Item (pre-initialized). @adamnaj said the editor might change it in the next version not to require it.

Enjoy!

Pavel Veller

4 comments on Content Import with Powershell: Treasure Hunt

Michael WestJuly 11, 2015 - Reply

What a fantastic use for SPE. Looking forward to the next treasure hunt!

Adam NajmanowiczJuly 11, 2015 - Reply

Brilliant Post Pavel!

Just as you said – the Read-Variable cmdlet not requiring initialization of item is already there and just waiting for the next release 🙂

/a

Another package install performance boost | Jeremy DavisJuly 27, 2015 - Reply

[…] the space of one day of internet reading I saw the Sitecore.Data.BulkUpdateContext class mentioned in a blog post and it came up in MVP forum discussions. In both cases, it was mentioned in the context of making […]

Harsh BaidNovember 22, 2016 - Reply

Hi Pavel,

Thanks for this excellent script. It has worked for most of the times. Recently I have faced one issue in this script w.r.t to File Encoding.

When trying to import UTF-8 based CSV its creating empty items in Sitecore and that too on the Parent Item of Selected folder instead of beneath the Selected folder.

This script in its present form is not importing/supporting UTF-8 properly. Can you please help how we can importing UTF-8 based CSV?

Add a Comment

Your email address will not be published. Required fields are marked *

Or request call back