Magento: Programmatically Updating SKUs in Bulk

May, 08, 2012

Magento does not make it easy to change SKUs in bulk. Try to do it via the import/export or dataflow profiles and you run into all kinds of problems. There is, however, a fairly easy way to do it and it simply involves adding a php script to your server and a CSV file of before and after SKUs. Here’s the step-by-step;

Note: Be sure to test this with only one or two product SKUs before doing it with all your SKUs. Also, be sure to backup your database before attempting this.

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. 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/sku2sku.csv.

3. 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”.

Paste the following php code into updateskus.php and save the file.

<?php

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 "successful";
} else {
echo "item not found";
}
} catch (Exception $e) {
echo "Cannot retrieve products from Magento: ".$e->getMessage()."<br>";
return;
}
}
}
fclose($updates_handle);
?>

4. 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 the page opens you should see confirmation messages that your SKUs were updated. Your SKUs should now be successfully updated.

If you’re finished updating the SKUs, remove the CSV and PHP files that you added to the server.

5. Errors

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.

This method was tested on a multi-site Magento Community 1.6.1 installation.

This method is based on this Magento Commerce article.

(Update Sep. 20, 2013) One of the readers noted that the script modified the ‘Main Website’ store view. When this happened, it resulted inĀ  products no longer using the ‘default’ data such description, name, etc. The reader suggested replacing the line “Mage::app();” with “Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);”. I haven’t tested it myself, but it looks like it should work, so I updated the code shown above. Thanks, Andrew!

Posted in Web Development | Tagged as: , , | 27 Comments

27 Responses to Magento: Programmatically Updating SKUs in Bulk

  1. qimaoya says:

    hi, thanks a lot. I followed all steps, but when I run updateskus.php, it shows 404 not found. Would you please help me?

    • leonh says:

      Are you sure you put updateskus.php in the main Magento directory? This is the directory where you see the app, var, skin, media, js and some other directories.

      • qimaoya says:

        yes, I am sure. I guess that is htaccess problem. As I upload a test.php at root directory, it also show 404 not found. I use HostGator VPS service.

  2. Edward says:

    Hi,

    Same here, also server error. Any way of working around the right permissions?

    • leonh says:

      Sorry, I can’t be of much help with that. I didn’t have to change any permissions when I did this on our server.

  3. nick says:

    I’m 99% there but I think I’m doing something wrong. All goes well, I get a page that says all the changes are successful, but sku’s still don’t change. Should I have to maybe reindex right after the script is done. I’m using magento 1.4.1.1.

    B.T.W for people with a really high number of products you may time out the process if it takes more then 5 minutes. In your php script at the top after:

    <?php

    Add this code:

    ini_set('max_execution_time', 300); //300 seconds = 5 minutes

    300 is usually the default to make it something really big if you need.

    • leonh says:

      I don’t know why it’s not working for you. Perhaps it only works on Magento 1.6. Good tip on setting the max execution time!

  4. This is a great post, very simple but it worked perfectly. I am running magento 1.7.0.2 on my website.

    Thanks again

  5. Diszo Sasil says:

    Another approach to update multiple SKUs.

    loadByAttribute(‘sku’, $oldSku);
    if ($product) {
    $product->setSku($newSku)->save();
    echo sprintf(“Renaming SKU [%s] to [%s] has been successfully completed.”.PHP_EOL,$oldSku);
    } else {
    echo sprintf(“SKU [%s] not found.”.PHP_EOL,$oldSku);
    }
    }catch (Exception $e) {
    echo “Cannot retrieve products SKU: “.$e->getMessage().PHP_EOL;
    continue;
    }
    unset($product);
    }
    ?>

  6. Diszo Sasil says:

    Another approach to update multiple SKUs.

    loadByAttribute('sku', $oldSku);
    if ($product) {
    $product->setSku($newSku)->save();
    echo sprintf("Renaming SKU [%s] to [%s] has been successfully completed.".PHP_EOL,$oldSku);
    } else {
    echo sprintf("SKU [%s] not found.".PHP_EOL,$oldSku);
    }
    }catch (Exception $e) {
    echo "Cannot retrieve products SKU: ".$e->getMessage().PHP_EOL;
    continue;
    }
    unset($product);
    }
    ?>

  7. Simon says:

    I followed the instructions and copied and pasted, and got the error… then I noticed my php code was not closed. Add ?> to close php code. Then it worked. Had to run it a couple of times for instance to update all of the skus. hope this helps for those that are getting server errors.

  8. Andrew says:

    Thanks for this. I used it to update a couple of hundred skus in my store. I noticed two issues though:

    1. The updated skus will be listed as out of stock. This is easily fixed by reindexing the Stock Status Index under SYSTEM > INDEX MANAGEMENT.

    2. Any product that had its sku updated by this script becomes impossible to edit. It’s like something about this process causes Magento to lock the products. Price, description, and other details about the product become frozen. I can edit them on the backend and they save there, but the changes never appear on the frontend. I’ve tried flushing the cache and reindexing but nothing has solved this.

    Anyone seen this behavior? Thoughts?

    Thanks!

    • leonh says:

      I didn’t run into either of these problems when I used the code. It’s been a while since I’ve worked with Magento, so I’m having a hard time imagining what could be causing the products to be “locked” like that. The only possibility that comes to mind right now is that you might be trying to edit the products while in the wrong store view (i.e. you may not be in the default store view). That wouldn’t necessarily have anything to do with the SKU change, but it may give you an idea of where to start looking.

      • Andrew says:

        Thanks! That was exactly the problem. When I ran the script it modified the “Main Website” store view. Because SKUs are global attributes, I didn’t notice that as it still changed the SKU everywhere. However it resulted in the updated products no longer using their default values for anything (price, description, etc.).

        The issue can be avoided by updating this line:


        Mage::app();

        to this:


        Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

        Cheers,
        Andrew

        • leonh says:

          Thanks, Andrew! I’ve updated the code shown on the page to include your modified line.

          • Igor says:

            Pls, help me to change skus like these ones…

            LICOTA 00000003 LICOTA TBD-00008A
            LICOTA 00000004 LICOTA ASP-A2010
            LICOTA 00000005 LICOTA ASP-A40
            LICOTA 00000006 LICOTA BAD12100
            LICOTA 00000007 LICOTA BAD12150
            LICOTA 00000008 LICOTA BAD10252
            LICOTA 00000009 LICOTA BADA0302
            LICOTA 00000010 LICOTA TWT-10068A
            LICOTA 00000011 LICOTA TWT-10068B
            LICOTA 00000012 LICOTA BTX33020
            LICOTA 00000013 LICOTA BTX37520
            LICOTA 00000014 LICOTA BTX33025
            LICOTA 00000015 LICOTA BTX37525
            LICOTA 00000016 LICOTA BTX33030
            Thx in advance

  9. Vaida vd Vlekkert says:

    Thank you very much for this useful script!!! Worked perfectly for us.

  10. Christopher says:

    This worked awesome for our site! We modified the script to run better and so you dont have to run it multiple times.

    setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

    $session = $_GET[‘session’];

    if (empty($session)) {
    $session = uniqid(md5(rand()), true);
    }

    $script = basename(__FILE__);
    $updates_file = “./var/export/sku2sku.csv”;
    $log_file = ‘./var/export/sku_’.$session.’.csv’;
    $sku_entry = array();
    $entries = file($updates_file);
    $lines = count($entries);
    $updates_handle = fopen($updates_file, ‘r’);
    $log_string = file_get_contents($log_file);

    if (empty($log_string)) {
    $log_array = array();
    } else {
    $log_array = (array)json_decode($log_string);
    }

    $js = <<<SCR

    setTimeout(function() {
    location.href = ‘{$script}?session={$session}’;
    }, 5000);

    SCR;

    if($updates_handle) {
    $done = 0;
    $count = 0;
    $limit = 100;
    while($sku_entry = fgetcsv($updates_handle, 1000, “,”)) {
    $old_sku = $sku_entry[0];
    $new_sku = $sku_entry[1];
    echo “Updating “.$old_sku.” to “.$new_sku.” – “;

    if (!$log_array[$old_sku]) {
    try {
    $get_item = Mage::getModel(‘catalog/product’)->loadByAttribute(‘sku’, $old_sku);

    if ($get_item) {
    $get_item->setSku($new_sku)->save();
    $log_array[$old_sku] = 1;
    echo “successful”;
    $count++;
    } else {
    echo “item not found”;
    }
    } catch (Exception $e) {
    echo “Cannot retrieve products from Magento: “.$e->getMessage().””;
    break;
    }
    } else {
    echo ‘successful’;
    }

    $done++;
    echo “\n”;

    if ($count >= $limit) {
    break;
    }
    }

    if ($done < $lines) {
    echo $js;
    }
    }

    $log_string = json_encode($log_array);
    @unlink($log_file);
    file_put_contents($log_file, $log_string);

    fclose($updates_handle);

  11. Christopher says:

    Code didnt post right – commented it

    // setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

    // $session = $_GET[‘session’];

    // if (empty($session)) {
    // $session = uniqid(md5(rand()), true);
    // }

    // $script = basename(__FILE__);
    // $updates_file = “./var/export/sku2sku.csv”;
    // $log_file = ‘./var/export/sku_’.$session.’.csv’;
    // $sku_entry = array();
    // $entries = file($updates_file);
    // $lines = count($entries);
    // $updates_handle = fopen($updates_file, ‘r’);
    // $log_string = file_get_contents($log_file);

    // if (empty($log_string)) {
    // $log_array = array();
    // } else {
    // $log_array = (array)json_decode($log_string);
    // }

    // $js = <<<SCR
    //
    // setTimeout(function() {
    // location.href = ‘{$script}?session={$session}’;
    // }, 5000);
    //
    // SCR;

    // if($updates_handle) {
    // $done = 0;
    // $count = 0;
    // $limit = 100;
    // while($sku_entry = fgetcsv($updates_handle, 1000, “,”)) {
    // $old_sku = $sku_entry[0];
    // $new_sku = $sku_entry[1];
    // echo “Updating “.$old_sku.” to “.$new_sku.” – “;

    // if (!$log_array[$old_sku]) {
    // try {
    // $get_item = Mage::getModel(‘catalog/product’)->loadByAttribute(‘sku’, $old_sku);

    // if ($get_item) {
    // $get_item->setSku($new_sku)->save();
    // $log_array[$old_sku] = 1;
    // echo “successful”;
    // $count++;
    // } else {
    // echo “item not found”;
    // }
    // } catch (Exception $e) {
    // echo “Cannot retrieve products from Magento: “.$e->getMessage().””;
    // break;
    // }
    // } else {
    // echo ‘successful’;
    // }

    // $done++;
    // echo “\n”;

    // if ($count >= $limit) {
    // break;
    // }
    // }

    // if ($done < $lines) {
    // echo $js;
    // }
    // }

    // $log_string = json_encode($log_array);
    // @unlink($log_file);
    // file_put_contents($log_file, $log_string);

    // fclose($updates_handle);

  12. baghulia says:

    Awesome, it definetely saved us many time & problems with dataflow/export profiles… Thks leonh!

  13. John Smith says:

    Saved me a lot of time, thanks a lot

  14. Tom amos says:

    Thanks for this! We had a store with over 4000 products in. The data sheet from our client had a space before half of the product id’s which was missed by ourselves on import. We where wondering why in hell the products where not all updating to find an annoying space inbetween a few of the skus on setup. This took us no time to resolve. The only thing i would say is we had to extend the time out from 30 seconds but this only too 2 mins to do.
    Thanks again!

  15. Anthony A says:

    Thank you so much for this and especially the really clear instructions. Fist time I have ever run a script and worked perfectly on Magento 1.7.02

    Thanks!!!!

  16. Liona says:

    I was using this some time and agree that it helps. For people that are not acquainted with coding I can advise this – https://www.mag-manager.com/useful-articles/how-to/how-to-bulk-update-magento-product-skus/

  17. Manoj Prabhu says:

    Skus updated correctly. But i faced big problem. When Update the product skus then tier prices was deleted. There is any way to retrieve the tier price or otherwise how to update the skus without affecting tire price.

Leave a Reply

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

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: