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:

01-UploadFile

02-Bulk Update Complete Message

Good Reads:

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

Happy Sitecore PowerShelling! 🙂

9 thoughts to “Update Sitecore Items using Sitecore PowerShell with CSV Data”

  1. this is helpful. have you considered modifying it to accommodate tenants/sites? The data folder for each tenant and site would differ. The path statement in these examples would not work with Sitecore 9

  2. I Want to update content of three different fields(SchoolDisplayName, SchoolName and InstitutionName) and content is bucket able and not for all the content for few of them i want to update and rest will be same.

    I have created CSV file in which taken 4 fields(item path, SchoolDisplayName, SchoolName and InstitutionName ) and now required script for that identify on the basis of item path and update content in these three field)

    Thanks in advance

  3. Hi,

    I have a field called “State”. So while executing this script I am getting an error “‘State’ is read only property”. My assumption is that it is getting referred by workflow state. How to resolve this?

    1. Hi Vivek,

      Thanks for referring this post. I haven’t done it before. While I was working on this scenario, I saw that there is no default import function provided for XLSX file. So the only way is using Import-Csv command. You can save your XLSX file in CSV format and import that using the given script. I just now tried that for you and it worked. 🙂

Leave a Reply to Bharath Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.