Have you ever needed to import data into Sitecore from an Excel or CSV file? It’s a common request and it’s actually also a really simple task thanks to Sitecore Powershell Extensions.

First of all, let’s assume you have a spreadsheet with the relevant information in - in this dataset, each row represents a set of changes to make to an item; the first column contains the item reference and the remaining columns are values for different fields within the item.

Below is an example of such a dataset, where we have a bulk set of changes to be made to the MetaTitle + MetaDescription fields. In this example, the item reference is an item path, but this could just as easily be an item ID.

1
2
3
4
ItemPath,MetaTitle,MetaDescription
/sitecore/content/home,Home,Some blurb about how lovely our website is
/sitecore/content/home/products,Products,Buy our products! All of them!
/sitecore/content/home/contact-us,Contact Us,Contact our lovely website

To prepare this data for the import, we want it in CSV format. If the data is within Excel, Excel allows you to Save As the spreadsheet to convert it to a CSV. Make sure it to include a header row in the file, as seen in the example above.

Then, within Sitecore Powershell we can use a very simple script to import the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$importData = Import-CSV "C:\Path\To\ImportData.csv"
foreach ( $row in $importData ) {
    $item = Get-Item -Path master:$($row.ItemPath) -ErrorAction SilentlyContinue
    
    if ($item) {
        $item.Editing.BeginEdit()
        $item["MetaTitle"] = $row.MetaTitle
        $item["MetaDescription"] = $row.MetaDescription
        $item.Editing.EndEdit() | Out-Null
    }
    else {
        Write-Host "Couldn't find: $($row.ItemPath)"
    }
}

And that’s all there is to it! The magic is in the Import-CSV command, which is part of regular Powershell, and imports all of the data into the $importData variable. The script can then iterate over this data and read each row to find the item and then make the updates. As long as the CSV file hash headers, each $row variable will have properties exposed that correspond to each of the columns, e.g. $row.ItemPath.

The -ErrorAction SilentlyContinue is there to swallow the exception that is raised if Get-Item can’t find the specified item. To handle this in a more user-friendly manner, we then perform a null-check on $item and if that fails we log out the ItemPath that could not be found.

Pretty simple, huh?

For an additional performance boost when importing your data, you can optionally wrap the import in an instantiation of the Sitecore.Data.BulkUpdateContext class, which prevents indexing + events from firing after each individual update:

1
2
3
4
5
6
7
Import-Function -Name New-UsingBlock
New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) {
    foreach ( ... ) {
        ...
    }
}