Content Import with Powershell: Treasure Hunt

[su_note note_color=”#fafafa”]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.[/su_note]

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()
}

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

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 | <code>Get-Member -MemberType Properties |</code>
ForEach-Object { $item[&quot;$($<em>.Name)&quot;] = $record.&quot;$($</em>.Name)&quot; }

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
{

<h1>run import</h1>

}
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!