How to bulk modify SKUs

Question

I'm looking for a way to bulk update SKUs. I looked into the ORM approach, but heard that was a bit slow during reindexing of each sku. I'm wondering if it's safe to modify it at the database level. What issues might I run into if I take this approach?

Answer 1

• Create a CSV File with Before and After SKUs In the first column, list your current SKUs and in the second column list the new SKUs.

Make sure this file is saved as a CSV file in the UTF-8 or ANSI encoding. You might run into problems with this if you create the file using Excel.

• Put the CSV File on Your Server Upload the CSV file to the var/export directory on your Magento server so that it’s path is /var/export/skudata.csv.
• Create the PHP Script On your server, in your Magento installation directory (the one where you see the app, var, skin, media, js and other directories), create a new file, save it, and name it updateskus.php.

=> updateskus.php :

<?php

include_once './app/Mage.php';
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$updates_file="./var/export/skudata.csv";
$sku_entry=array();
$updates_handle=fopen($updates_file, 'r');
if($updates_handle) {
    while($sku_entry=fgetcsv($updates_handle, 1000, ",")) {
        $old_sku=$sku_entry[0];
        $new_sku=$sku_entry[1];
        echo "<br>Updating ".$old_sku." to ".$new_sku." - ";
        try {
            $get_item = Mage::getModel('catalog/product')->loadByAttribute('sku', $old_sku);
            if ($get_item) {
                $get_item->setSku($new_sku)->save();
                echo "Sku updat successfully";
            } else {
                echo "Product Item not found";
            }
        } catch (Exception $e) {
            echo "Product can't getting from Magento: ".$e->getMessage()."<br>";
            return;
        }
    }
}
fclose($updates_handle);
?>

• Run the Script To run the script simply use your internet browser and navigate to http://yoursite.com/updateskus.php. If you have a multi-site setup use the master or primary site as set by your hosting provider.

When you run this script, You will get message about succuessfully sku updates.

Hope, It may be helpful for you.

Answer 2

For Magento, the SKU attribute isn't a key field in the database, so its data is as malleable as the product name or price. There will be no side effects to manipulating it in the database, but remember to clear your Magento cache after you are finished updating them.

Answer 3

I just completed an update of around 150k skus about 3 weeks ago or so using this solution:

<?php
/* 
1) Create a CSV File with Before and After SKUs. 
  - In the first column, list your current SKUs and in the second column list the new SKUs. 
  - Do not include headings in your CSV file. 
  - Be sure this file is saved as a CSV file in the UTF-8 or ANSI encoding. 
You might run into problems with this if you create the file using Excel.
2) Upload the CSV file to the var/export directory on your Magento server so that it’s path is /var/export/sku2sku.csv.
3) Run the Script
+ If you run into the following error, don’t worry too much. Just re-run the script and see if more SKUs get updated.
  - Cannot retrieve products from Magento: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
  - If you have a lot of SKUs to update you can expect the script to take several minutes at least, to complete.
*/

include_once '../app/Mage.php';
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$updates_file="../var/export/sku2sku.csv";

$sku_entry=array();
$updates_handle=fopen($updates_file, 'r');
if($updates_handle) {
    while($sku_entry=fgetcsv($updates_handle, 1000, ",")) {
        $old_sku=$sku_entry[0];
        $new_sku=$sku_entry[1];
        echo "<br>Updating ".$old_sku." to ".$new_sku." - ";
        try {
            $get_item = Mage::getModel('catalog/product')->loadByAttribute('sku', $old_sku);
            if ($get_item) {
                $get_item->setSku($new_sku)->save();
                echo "Success!";
            } else {
                echo "Not found!";
            }
        } catch (Exception $e) {
            echo "Cannot retrieve products from Magento: ".$e->getMessage()."<br>";
            return;
        }
    }
}
fclose($updates_handle);
?>

I had this file residing in a tools/ directory in the root of the magento installation. I ran it over SSH using PHP - it was NOT extremely fast, but it got the job done and done accurately.

CREDIT: https://gist.github.com/thagxt/052eb52e07f33ca4c3df

Answer 4

You can use an sql query to change sku like i used it :

UPDATE catalog_product_entity SET sku = REPLACE(sku, 'look_', '')
WHERE sku LIKE '%look_%'

EX : "look_EXAMPLE1" changed to "EXAMPLE1"