How to change SKUs in bulk in Magento

A client dropped us a line the other day looking to change the SKUs on all of their products. Whilst most things can be changed in bulk using Magento’s import / export functionality, a product’s SKU isn’t one of them, because that functionality uses the SKU to identify the product – so you can’t go changing it mid-import.

It can still be done pretty quickly, though, using a small script and a CSV. The CSV just needs to contain two columns, and no headers – the first column should be the existing product SKU, and the second column should be the SKU you want it to change to.

The script looks as follows. First we hook into Magento in the usual way (this assumes that you’re putting the CSV and the script in the web root of your Magento installation) :

require_once 'app/Mage.php';
umask(0);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

Upload the CSV as a file called “skus.csv” in the same location, then the script can access it and loop through it :

if (($handle = fopen("skus.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

Then it gets the old SKU and the new SKU into variables :

$oldsku = $data[0];
$newsku = $data[1];

It tries to load the product associated with the old SKU, and if the product exists…

$product = Mage::getModel('catalog/product')->loadByAttribute('sku', $oldsku);
if($product) { 

…it sets the SKU to be the new SKU, saves the product, outputs confirmation, and moves on :

$product->setSku($newsku);
$product->save();
echo  $product->getName() . ' SKU updated';
echo "\n";
}
}
}
?>

And that’s it. Hit the file in a browser, or over SSH, and you’re away. The script can be downloaded below, and as usual we’re happy to answer questions in the comments.

22 comments

  • David C.

    Awesome, it just worked great, thanks a lot!!

    • Giles Bennett (author)

      You’re welcome.

      Kind regards,

      Giles

  • Pooja Gupta

    Hey Giles,

    Thanks for this awesome post. I am using Magento 2.0 for one of my e-commerce websites.
    So can you please help me out that how can i change the SKUs in the bulk becasue editing the skus of thousands of products can be time-consuming for me.

    Thanks in advance!

  • joel

    Hello,

    Great work. One thing, the script seems to be only updating the first record.

    Just me?

    • Giles Bennett (author)

      Joel,

      I believe so, yes – the script was tried and tested.

      Kind regards,

      Giles

  • jehzlau

    So sad, this doesn’t work anymore. Got this error after running your script in Magento 1.9.2.4. 🙁

    Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`jehzlau7_paris`.`paris_catalog_product_entity`, CONSTRAINT `FK_PARIS_CAT_PRD_ENTT_ATTR_SET_ID_PARIS_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `paris_eav_attribute_)’ in lib/Zend/Db/Statement/Pdo.php:228 Stack trace: #0 lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) #1 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) #4 /public_html in lib/Zend/Db/Statement/Pdo.php on line 235

    • Giles Bennett (author)

      Jehzlau,

      The script works fine – you’ve got an unrelated issue in your database integrity.

      Kind regards,

      Giles

      • jehzlau

        Hi Giles! Thanks! It works. I used your script again after a year of being here. I just found out that I was here last year after reading my own comment. Hahaha! 😀

  • krystian

    is it for magento2 or 1.x.x?

    • Giles Bennett (author)

      Krystian,

      Magento 1.x

      Kind regards,

      Giles

  • Mike Karthauser

    Mage::getModel(‘catalog/product’)->load() gets the product via its id (atleast in magento 1.9 which meant that your version was not getting all products.
    I’ve modified your script by changing the line to
    $product = Mage::getModel(‘catalog/product’)->loadByAttribute(‘sku’, $oldsku);

    which works for me.

    • Giles Bennett (author)

      Mike,

      Good catch, I’ve updated the script accordingly.

      Kind regards,

      Giles

  • Rommy

    Hi Giles,

    It so nice to find your blog.
    I found this article is very helpful for me.
    But I don’t understand the concept of Script.

    Do you have a whole video of How to apply this technique ?
    -how to save a script file?
    -where should I put the script file in the FTP?
    -how I upload a CSV ? (is it through data flow?)

    I really want to apply this, but don’t have idea of how to do it.
    It would be great if you have a video one by one step.

    Thank you !

    Rommy

    • Giles (author)

      Rommy,

      I’m afraid that the article assumes a certain level of coding knowledge – it sounds like you may be better getting a developer to help you with it.

      Kind regards,

      Giles

      • sandro

        Hello, First of all thanks for such a nice a tutorial. But i’m not very expert with magento core/ftp. Can you please explain a bit where i have to put this code / script.
        Thanks.

        • Giles Bennett (author)

          Sandro,

          Put it in the web root of your folder. If you’re not sure what that means, I suggest you hire a developer to help you.

          Kind regards,

          Giles

  • Mummy Ninja

    Btw, where’s the download link for the script? Or should I just copy all the separated codes and merge it into one .php file?

    • Giles (author)

      Sorry – good question. Not entirely sure why the download link isn’t showing for this particular file, so yes, I’d say just copy and paste the script snippets into one file for the time being.

      Kind regards,

      Giles

  • Mummy Ninja

    Awesome! Huge help! Thanks for sharing this! I was in the verge of editing all 10,000 SKUs this weekend, good thing there’s a fast approach. 😀

    • Giles (author)

      You’re welcome.

      Kind regards,

      Giles

Leave a Reply

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

Want to talk to us about your project?