Update SKUs with dataflow en-masse from a .csv file

Last modified by krlecarp on Wed, September 29, 2010 18:15
Source|Old Revisions  

Introduction

Sometimes it can be necessary to change lots of SKUs, i.e. if products are to be carried over from one model year to the next. Going into every product and changing the SKUs takes time, it can also result in typo errors. This script makes the task a little easier, particularly if using compound SKUs of the format: PRODUCTID-SIZE-COLOUR e.g. 96105-M-001, 96105-L-001, 96105-XL-001... Here the size/colour codes stay the same, it is just the main product ID that needs adjusting. This script will need modification to suit your own codes, however, in so doing, the practical experience should get you up to speed on the API.

'Installation' Notes

This script takes a csv file where each line has an old SKU followed by a comma (,) and a new SKU. The API is used to take each old/new pair and update accordingly. However, you are going to have to amend this script to suit your SKU codes. This script is specific to a shop where the SKU is made up of a product ID followed by a dash, the size code, another dash and then a colour code. The old/new pairings expected by this script are just the product ID’s, it then updates all of the products with a matching XXXX-SIZE-COLOUR pattern, matching the XXXX for all of them. Hence, the format of your .csv file is:

XXXX1, YYYY1 XXXX2, YYYY2 ... XXXXN, YYYYN

After you have adapted the code to your purpose I suggest making a DB backup and running a test on a couple of SKU’s. It can take a minute or two to be able to search for the new codes in the backend, Magento is slow sometimes, particularly with the API. You will also need to put in values for your API connector, hostname, user and pass. You may also need to change where the sku_update.csv file goes. Place the script out of harms way in you user directory, and not in www docroot. Run it with the command line flavour of PHP with: php sku_update.php > test.txt Check the output and if you want more verbose info on products uncomment the var_dump line. If all works as planned then run it with the full list of required changes, overnight as it can take a while.

Code

<?php
// sku_update.php
// 17 September 2009

// Takes /var/www/html/api/sku_update.csv and updates sku codes accordingly
// File is comma separated with old pid followed by new pid

$host= 'yourshop.co.uk';
$client= new SoapClient('http://'.$host.'/index.php/api/soap/?wsdl');

// Can be added in Magento-Admin -> Web Services with role set to admin
$apiuser= 'APIUSER';
$apikey = 'APIPASS';

// Login to SOAP API with the above credentials
$sess_id= $client->login($apiuser, $apikey);

$updates_file="/var/www/html/api/sku_update.csv";
$sku_entry=array();
$sku_from=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 "Updating ".$old_sku." to ".$new_sku."nn";
    try
    { $filters = array('sku' => array('like'=>'%'.$old_sku.'%'));
      $matching_skus=$client->call($sess_id, 'product.list', array($filters));
      foreach($matching_skus as $matching_sku)
      { //var_dump($matching_sku);
        $sku_from=explode("-",$matching_sku['sku']);
        $sku_to=$new_sku."-".$sku_from[1]."-".$sku_from[2];
        echo "SKU: ".$matching_sku['sku']." -> ".$sku_to."n";
        try
        { $do_it=$client->call($sess_id, 'product.update', array($matching_sku['sku'], array('sku'=>$sku_to)));
          sleep(10);
        }
        catch (Exception $e)
        { echo "WSDL call failed!n";
          return;
        }
      }
    }
    catch (Exception $e)
    { echo "Cannot retrieve products from Magenton".$e."nn";
      return;
    }
  }
  fclose($updates_handle);
}
?>

Another Method

This modification is much faster because it uses Magento models, rather than API. It can be placed in the Magento root “scripts” folder for easy access.

<?php
include_once '../app/Mage.php';
Mage::app();

$updates_file="/home/public_html/xxx/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);



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs