How to replace the sku number for 5000 products in magento

Question

I have (.csv) file having the two column

for example:

old sku new sku

123 456

I want to replace sku number of each products with new sku number.

I have more than 5000 products in my store.

Thanks in advance

Solution 1

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.

Solution 2

This code works for me very fast. I use it.

<?php

$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);

ini_set('display_errors', 'Off');
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

    $resource = Mage::getSingleton('core/resource');
    $writeConnection = $resource->getConnection('core_write');


$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) {
    $product_id = $get_item->getId();
    $writeConnection->update(
            "catalog_product_entity",
            array("sku" => $new_sku),
            "entity_id=$product_id"
    );
echo "successful";
} else {
echo "item not found";
}
} catch (Exception $e) {
echo "Cannot retrieve products from Magento: ".$e->getMessage()."<br>";
return;
}
}
}
fclose($updates_handle);
?>

Solution 3

The following code explains how to bulk update product sku based on the old sku from the CSV file.Please refer the tutorial for step by step explanation. http://www.pearlbells.co.uk/bulk-update-magento-products-sku/

function rewriteSku($newDatas) {
$i = 1;
foreach($newDatas as $newData) {
    $product = Mage::getModel('catalog/product')->loadByAttribute('sku', $newData[1]);
    if($product) { 
        $product->setSku($newData[0]);
        $product->save();
        echo  $i++.' '.$product->getName() . ' SKU updated';
        echo "\n";
    }
}

}

Solution 4

For the best performance, use:

   

$get_item->setSku($new_sku);
$get_item->getResource()->saveAttribute($new_sku, 'sku');

View more solutions

Comments

[Lawrence Cherone]

What about using a foreach loop, lookup the data and then replace it if found...

[Jimit Shah]

Thanks for the answer lawrenece. May be you get something wrong. I have excel file . I am asking is there any build in facilty in magento or mysql which replace my current sku number with new . I just import the excel file. I put your option in last option to make a php script and read the excel file and replace the exisiting sku with new.

[Saeven]

Seems like a great job for SQL, no programming required. I'm assuming that you have some kind of old_sku, new_sku relationship somewhere?

[Lawrence Cherone]

@JimitShah It clearly says .csv :/

[Jimit Shah]

magento store sku information in so many different tables. I want to know any of easy option into which I just upload a new (.csv) file with old and new sku code. that replace the sku code from old to new ?

[Ravi Patel]

using magent0 magmi import.

[Fabian Schmengler]

Loading and saving the whole product model for each row will consume lots of memory for 5000 products and is likely to fail. For jobs like this I'd recommend using Mage_Core_Model_Resource_Iterator or even direct SQL queries.

[bish]

As this codes helped you it will not be a helpful answer for the opener as you didn't changed it for his problem and you didn't gave any explanations. Please provide more information by editing your answer

[jehzlau]

This works fast. Just tried it now. Don't forget to change catalog_product_entity to your table name with suffix. :)

[snh_nl]

Works fine. Only problem is a little slow ... 6secs save per SKU

[snh_nl]

(update) this will not work : saveAttribute for SKU. SKU is not an EAV attribute and needs to be saved/updated differently ...