Using Sitecore Powershell Extensions to import CSV data
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.
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:
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.
-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: