Sitecore: How to import content with minimum effort

Very often (in testing perspective or vital necessity) there is a need to import data into the Sitecore. There are many tools that can be used for such purpose. But I want to propose you something very simple, something readily available, and yet something powerful and flexible enough to build a reusable component. In such a case I would use Sitecore Powershell Extensions.

Format

Firstly, let’s decide which format of data could be used. There are several common cases:

  • File with data that are stored in a special format. In such case special algorithm to serialize/desialize data should be created and shared with interested persons.
  • Excel file. In such case special tools should be used to read/write data from it.
  • SCV. Pretty simple and well-known.

As we are going to implement and maintain simple solution, let’s choose the last one – SCV.

Functionality

With the usage of CSV we would do the following:

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

Implementation

Headers

First, make sure the CSV files have headers. The script will match the names to the data template so we won’t have to do anything extra. And make sure there’s a Name column. The following will be used to name the items:

"Id","Number","Name","Code"
2001437132,"2012622645","Joe Smith",903
2036387321,"2230137432","Bob Jones",700
2205389293,"2102372796","John Sutton",750
2104387124,"2202373114","David South",1009

Name Normalization

There can be all kinds of symbols in the Name column so make sure we normalize it before we use it to name items. We 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

For the majority of cases a simple name-to-name translation is all we 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. Anyway:

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

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

Import script

Here is a full script for import data from SCV file into the Sitecore:

function NormalizeName($name)
{
    # replace all special symbols with a single spaces
    $name = $name -replace "[^a-zA-Z0-9]", " " -replace "\s+", ""

    return $name.Trim()
}

$media = Get-Item "/sitecore/media library"
$folder = Get-Item "/sitecore/content"
$template = Get-Item "/sitecore/templates"

$dialog = Read-Variable -Parameters `
    @{ Name = "media"; Title = "Source"; Root="/sitecore/media library/"; Editor="item"}, `
    @{ Name = "folder"; Title = "Destination"; Root="/sitecore/"; Editor="item"}, `
    @{ Name = "template"; Title = "Type"; Root="/sitecore/templates/"; Editor="item"} `
    -Description "This script will convert CSV data into content items." `
    -Width 800 -Height 600 `
    -Title "Simple CSV Import Utility" `
    -OkButtonName "Import" `
    -CancelButtonName "Cancel"

if ($dialog -ne "ok")
{
    Exit
}

# Read media stream into a byte array
[system.io.stream]$body = $media.Fields["blob"].GetBlobStream()
try
{
    $contents = New-Object byte[] $body.Length
    $body.Read($contents, 0, $body.Length) | Out-Null
}
finally
{
    $body.Close()
}

# Convert the stream into a collection of objects
$csv = [System.Text.Encoding]::Default.GetString($contents) | ConvertFrom-Csv

$bulk = New-Object "Sitecore.Data.BulkUpdateContext"
try
{
    foreach ($record in $csv)
    {
        $name = NormalizeName $record.Name
        Write-Host "Normalized [$($record.Name)] => [$name]"

        $item = New-Item -Path $folder.Paths.FullPath -Name $name -ItemType $template.Paths.FullPath

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

        Write-Host "Created $($item.Paths.FullPath) for $($record.Name)"
    }
}
finally
{
    $bulk.Dispose()
}

Enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s