Import and Export data outside Sitecore

In some projects, I have been asked to export and import data that was in Sitecore so that non-technical personal could easily work with it. It could be everything from exporting users’s personal data to updating product information in a Sitecore Commerce site. The requirements were that these non-technical personal shouldn’t have to learn anything with Sitecore to do their work. My solution was to export relevant Sitecore data to an Excel file, and create a script in order to import the updated data to Sitecore.

We have this hierarchical list of product items under /sitecore/content/exhibitor/Product items/Products. The items could be of different templates: Product, EBMSProduct or LinkProduct. We are interested in managing some of their properties, but not all of them. Some of those properties, like “Serie”, are keys to other Sitecore objects.

Export Sitecore data to an Excel/CSV file

Get-Item -Path master:// -Query "/sitecore/content/exhibitor/Product items/Products//[@@TemplateName = 'Product' or @@TemplateName = 'EBMSProduct' or @@TemplateName = 'LinkProduct']" -Language "" | Show-ListView -Property `
@{ Name="Item path"; Expression={$_.FullPath}},
@{ Name="Template"; Expression={$_.TemplateName}},
@{ Name="Displaynamn"; Expression={$_."_Display name"}}, @{ Name="Intro"; Expression={$."Intro"}},
@{ Name="Språk"; Expression={$_.Language}},
@{ Name="Artikelnr"; Expression={Split-Path -Path $_.FullPath -Leaf -Resolve}},
@{ Name="Serie"; Expression={get-item -ID $_."Serie" -Path master:// | select-object -Property Name}},
@{ Name="USP"; Expression={$_."USP"}},
@{ Name="Egenskaper (kort)"; Expression={$_."PropertiesShort"}},
@{ Name="Viktig att-tänka-på"; Expression={$_."ImportantNote"}},
@{ Name="Beskrivning lång"; Expression={$_."Description"}},
@{ Name="Egenskaper lång"; Expression={$_."PropertiesFull"}},
@{ Name="Tillbehörstext"; Expression={$_."Accessories"}},
@{ Name="Tillståndstext"; Expression={$_."Permits"}},
@{ Name="Villkorstext"; Expression={$_."Terms"}},
@{ Name="Taggar för filtrering"; Expression={$_."ProductTags"}},
@{ Name="Link (LinkProductOption)"; Expression={$_."Link"}}

We get all the items under /sitecore/content/exhibitor/Product items/Products with the correct Template. We then use the command Show-ListView to show the data in a table in SPE, with the columns defined in the parameter -Property

When this table opens, we can verify the data and then save it in a CSV file.

Import data from an Excel/CSV file to Sitecore

Before executing this script, be sure that the CSV file is in the UTF-8 encoding.

The script reads the file (Import-CSV) and specifically ask for a “;” as a column delimiter.

It then loops through all rows, getting Sitecore Items with the path from the first column (Get-Item). If the item exists, it is updated with the data from the file, with some logic based on the item’s template.

$importData = Import-CSV "C:\Olivier\example_produkt_taggar_import.csv" -Delimiter ";"
$cols = $importData[0].psobject.properties.name
$i = 0;
foreach ( $row in $importData ) {
    IF ([string]::IsNullOrWhitespace($row.$($cols[0]))){continue}
	$language = $row.$($cols[4])
    $item = Get-Item -Path master:$($row.$($cols[0])) -ErrorAction SilentlyContinue -Language $language
	# Write-Host $i $item."__Display name"
    $DiamondsId = "{00A922C7-2834-4C9E-92F9-51130AC408C7}"
	$SpaceWallId = "{2BFCB42A-EC2E-4426-A66F-3B296222C42F}"
	$template = $row.$($cols[1])
    if ($item) {
		$i++;
		$series = $row.$($cols[6])
        $item.Editing.BeginEdit()
		if (!$template.Contains("Option")) {
			$item."Intro" = $row.$($cols[3])
			$item."USP" = $row.$($cols[7])
			$item."PropertiesShort" = $row.$($cols[8])
			$item."ImportantNote" = $row.$($cols[9])
			$item."Description" = $row.$($cols[10])
			$item."PropertiesFull" = $row.$($cols[11])
			if ($series) {
				if ($series.Contains("Diamonds")) {
					$item."Serie" = $DiamondsId
				} else {
					if ($series.Contains("Spacewall")) {
						$item."Serie" = $SpaceWallId
					}
				}
			}
		} 
		if ($template.Contains("Link") -and $template.Contains("Option")) {
			$item."Link" = $row.$($cols[16])
		}
		$item."ProductTags" = $row.$($cols[15])

        $item."__Display name" = $row.$($cols[2])
        
		$item.Editing.EndEdit() | Out-Null
    }
    else {
        Write-Host "Couldn't find: $($row.$($cols[0]))"
    }
}

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: