Update Sitecore Items using Sitecore PowerShell with CSV Data

Hello Sitecorians,

 

Are you looking for a quick and easy way to update Sitecore Items — Sitecore PowerShell does that job very easily.

 

In my previous blog — I provided the PowerShell script/module by which you can export data using Sitecore PowerShell Extensions. If you see that script/file properly – The first two header rows are “ItemPath” and “Language“. These columns are required for the below script to update the data. I’ve kept the language column as required for this because I work on Sitecore Site having more than 10 languages and in that case, we need a language option so that the correct language version gets updated. And other header rows in the file are the field names. Once I have exported the data in CSV – do the required updates in Excel and import the same file using the below script. It will import updated data back to Sitecore Items.

 

I referred to a couple of blogs for importing CSV files and in all, we have to provide the Path for the file but for providing the path, that file should reside on the server. So I looked for some other way around it and found an easy way to upload the file, process it, and then delete it. Let me know if you have any other ideas/suggestions for it.

Sample CSV File: Click Here

 

First, we need to upload the file on the server – Here I’m uploading the file on the server in the Data Folder, and then using the Import-Csv command, file data will be imported.

#Upload the file on the Server in temporary folder
#It will create the folder if it is not found
$dataFolder = [Sitecore.Configuration.Settings]::DataFolder
$tempFolder = $dataFolder + "\temp\upload"
$filePath = Receive-File -Path $tempFolder -overwrite

if($filePath -eq "cancel"){
    exit
}

$resultSet =  Import-Csv $filePath

Once the file is available on the server and we have got the path and data in $resultSet variable, It then iterates through the data and updates the Item field value. Make sure headers are present as it’s referred as field names and based on that each field value will be updated. At the end, it will remove the file from the server.

$rowsCount = ( $resultSet | Measure-Object ).Count;

    if($rowsCount -le 0){
        Remove-Item $filePath
        exit
    }
    
    Write-Log "Bulk Update Started!";
    
    foreach ( $row in $resultSet ) {
        $currentItem = Get-Item -Path $row.ItemPath -Language $row.Language -ErrorAction SilentlyContinue
        if ($currentItem){
         $fields = $currentItem | Get-ItemField
         foreach($field in $fields){
             if($row -match $field) {
                  $currentItem.$field = $row.$field
             }
         }
        }
        else {
            $logThis =  "Couldn't find: " + $row.ItemPath + " with Language Version: " + $row.Language 
            $logThis
            Write-Log $logThis
        }
    }
    
    $logInfo = "Bulk Update is Completed!";
    $logInfo
    Write-Log $logInfo

Remove-Item $filePath

Below is the full script, you can copy and execute it in Sitecore PowerShell! 😉

#Upload the file on the Server in temporary folder
#It will create the folder if it is not found
$dataFolder = [Sitecore.Configuration.Settings]::DataFolder
$tempFolder = $dataFolder + "\temp\upload"
$filePath = Receive-File -Path $tempFolder -overwrite

if($filePath -eq "cancel"){
    exit
}

$resultSet =  Import-Csv $filePath

$rowsCount = ( $resultSet | Measure-Object ).Count;

    if($rowsCount -le 0){
        Remove-Item $filePath
        exit
    }
    
    Write-Log "Bulk Update Started!";
    
    foreach ( $row in $resultSet ) {
        $currentItem = Get-Item -Path $row.ItemPath -Language $row.Language -ErrorAction SilentlyContinue
        if ($currentItem){
         $fields = $currentItem | Get-ItemField
         foreach($field in $fields){
             if($row -match $field) {
                  $currentItem.$field = $row.$field
             }
         }
        }
        else {
            $logThis =  "Couldn't find: " + $row.ItemPath + " with Language Version: " + $row.Language 
            $logThis
            Write-Log $logThis
        }
    }
    
    $logInfo = "Bulk Update is Completed!";
    $logInfo
    Write-Log $logInfo

Remove-Item $filePath

Screenshots:

Good Reads:

https://www.kasaku.co.uk/2016/04/10/importing-csv-data-in-sitecore/

Happy Sitecore PowerShelling! 🙂