4

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?

  • Please check my answer. – Rohan Hapani Aug 2 '18 at 18:35
3
  • 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.

| improve this answer | |
0

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.

| improve this answer | |
0

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

| improve this answer | |
0

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"

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.