Update Magento SKU with EAN + Product ID

Question

I´m trying to mass update my product´s SKU with a string made up of EAN (dash) Product ID.

E.g.

EAN=1234 & ID=44

Result=1234-44

I´m happy to use either PHP or a MySQL statement that I can copy/paste into phpMyAdmin.

Please be as specific as possible as you are dealing with a total beginner.

Not sure what you mean, but every product will have its own EAN number provided by manufacturer. Actually, it could happen that I might forget to fill in some EAN numbers so it could be blank. For this reason I guess would be better to format SKU like this ID-EAN (instead of EAN-ID, if EAN doesn´t exists, then use ID without dashes. e.g. 44 Sounds resonable? — Pablo, Jun 08 '15 at 10:21

score 0 · Answer 1 · answered Jun 10 '15 at 12:46

0

Create a file Updatesku.php inside your magento root folder and write the below code in that file and then execute it with below URL http://www.yourdomain.com/Updatesku.php

<?php
require_once('app/Mage.php'); 
umask(0);
Mage::app();
$_productCollection = Mage::getModel('catalog/product')
               ->getCollection()->addAttributeToSelect('*');
foreach($_productCollection as $_product)
{   
     try
     {
          $ean = $_product->getAttEan();
          $id  =  $_product->getEntityId();
          $newSku = $ean . '-' . $id;
          $_product->setSku($newSku);
          $_product->save();
     }
     catch(Exception $e){
          echo $e->getMessage();
     }
}

answered Jun 10 '15 at 12:46

Manashvi Birla

2,837
3
14
28

This is the result: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO `catalog_product_entity_int` (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), ON DUPLICATE KEY UPDATE `value` = VALUES(`value`).....and so on. – Pablo Jun 10 '15 at 15:00
Actually, most of the SKUs were modified as I wanted but some of them were left blank – Pablo Jun 10 '15 at 16:16
@Pablo check my second answer. This will update all the SKus (which were left blank) in the format you desired. – Manashvi Birla Jun 11 '15 at 06:08

score 0 · Answer 2 · answered Jun 11 '15 at 06:07

In order to upadate all those SKUs which were letf blank , make the below changes in your file.

<?php
    require_once('app/Mage.php'); 
    umask(0);
    Mage::app();
    $_productCollection = Mage::getModel('catalog/product')
                   ->getCollection()
                   ->addAttributeToFilter('sku',array('null' => true))
                   ->load();
    foreach($_productCollection as $_product)
    {   
         try
         {
              $ean = $_product->getAttEan();
              $id  =  $_product->getEntityId();
              $newSku = $ean . '-' . $id;
              $_product->setSku($newSku);
              $_product->save();
         }
         catch(Exception $e){
              echo $e->getMessage();
         }
    }

anz · Answer 3 · 2015-06-11T05:42:38.033

-1

The following code should do.... Tweak it to your liking..

UPDATED

<?php

require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app();

// Loading products that satisfies the criteria
$productList = Mage::getModel('catalog/product')
                ->getCollection()
                ->addAttributeToSelect('*')
            //  ->addAttributeToFilter('sku', array('like' => '%-T')) //if criteria
                ; 

//Writing to Magento Log
$totalString = count($productList)." Products were found.";         
Mage::log($totalString, null, 'listProducts.log');
echo $totalString;

if(count($productList)){
    foreach($productList as $product){
        //updating products now
        try{
            $EAN = $product->getAttEan(); // sample EAN, eg $EAN = '1234';
            $ID = $product->getEntityId(); // sample  ID, eg, $ID = $product->getSku();
            $newSku = $EAN . '-' . $ID;//build your new custom sku as per need
            $product->setSku($newSku);
            //update other if you want

            $product->save();
            $skuString = "Product with SKU : ".$product->getSku()." updated!!";
            Mage::log($skuString, null, 'listProducts.log');
            echo $skuString;
        }catch(Exception $ex){
            Mage::log($ex->getMessage(), null, 'listProducts.log');
            echo $ex->getMessage();
        }
    }
    echo "Custom Sku has been updated Succesfully";
}

?>

edited Jun 11 '15 at 05:42

answered Jun 08 '15 at 10:35

anz

987
7
21

Should I paste this code into notepad, name it updatesku.php and run it? Sorry, I have no idea of what I´m doing,,, – Pablo Jun 08 '15 at 10:49
See the updated answer. You can save it in a PHP file in the root of your Magento Installation, and run in from your browser. You need to populate the $EAN and $ID as per your need. Take DB backup before you do it, to be absolutely safe. Take care of the security concerns by deleting the file once it's done, or renaming a difficult filename. – anz Jun 08 '15 at 11:10
It didn´t quite work. The page stayed loading for a long white and after that all products now have a dash (only) for SKU – Pablo Jun 08 '15 at 14:38
Don´t you need to know the EAN attribute code which is att_ean? – Pablo Jun 08 '15 at 14:46
how can I populate EAN and ID? Sorry, I have no idea of php and MySQL – Pablo Jun 09 '15 at 16:13
what is EAN - is it specific to your store?? what about ID - is it product id or product sku? – anz Jun 10 '15 at 07:15
European Article Number (EAN) is a unique number provided by the manufacturer of each product which I store in a text field (att_ean) against each product. ID is the unique code that Magento assigns to each product in the DB. SKU is a unique ID code which the user (usually warehouse manager) assigns to each product to facilitate stock management amongst other things. I want to create SKUs by using a combination of the two. – Pablo Jun 10 '15 at 09:23
Check the updated answer. Save this file content in to a file called "custom_sku.php" and save it to the root of your Magento installation. Then you can run the file from the browser as , www.yourmagentourl.com/index.php/custom_sku.php – anz Jun 11 '15 at 05:47

Update Magento SKU with EAN + Product ID

3 Answers3

UPDATED