How To Add, Change SKU In Bulk Magento Product List

How To Add, Change SKU in Bulk Magento Product list is not as simple as it may sound by there is a quick solution to add, change SKU numbers within your existing huge product database. Stock Keeping Unit aka SKU is typically a unique, alphanumeric, merchant-defined identifier for the product with the primary intention to make this number human-readable, meaningful and concise. Many big ecommerce open source application uses SKU but i have seen some e-commerce open source applications which completely neglect this attribute.

Magento uses SKU explicitly as a “unique identifier for the particular product, across all stores and websites”. Keeping Product Title be the primary differentiation won’t help especially if the store is multi-site and multi-lingual. As per a study when people start their online store they don’t give much attention to the SKU numbers but keep on adding as many products as they can which leads to trouble we are talking about. Now, they end up having thousands of products in their database without proper SKU numbers. Tracking becomes difficult, heavy ordering takes a toll on back office.

What to do when you have thousands of products in the database without proper SKU?

If possible store owners should plan a strategy well in advance to avoid this situation by inserting a well planned SKU numbers for every products they add or import but if that’s not the case here is what you can do.

Simple Approach To Change SKU

  1. Export the entire product table
  2. After successful export you will find a new CSV file stored under /var/export directory on your server.
  3. Download this CSV file
  4. Edit the SKU field in the downloaded CSV file manually or using macros.
  5. Clean the product table data.
  6. Import the final CSV again. System => Import/Export =>Profiles. Select “Type” under Data Format to CSV/Tab Seperated. Now click on Upload File, and browse for your .csv file and click “Save and Continue Editing”. Now go to “Run Profile” and select your file from the drop-down menu. Click the button underneath to run the import.
  7. Check product associations, related products etc.

The above method is the simplest one which doesn’t involve programming aspects but it’s painful to edit huge list of products manually.

Programmatic Approach To Change SKU

If you have a little hands on Magento coding then you can follow the programmatic approach to this problem. Steps are given below:

  1. Download the entire product table as CSV file which includes SKU numbers
  2. Add a new column called “New SKU” just after SKU field in the CSV file
  3. Populate this new SKU field with the desired SKU values (manually or by copying from other sheet).
  4. Run the following code

I am assuming that you can read the CSV file as an array where your products are in the following format:

SKU, NEW SKU
SKU1, NEW SKU1
SKU2, NEW SKU2
………………so on

Read the .CSV file as an array then after getting the final array simply loop over products as

1
2
3
4
5
6
//Assuming the $products are coming after csv-> array conversion. 
foreach($products as $product){
    $old_sku = $product[0];
    $new_sku = $product[1];
    Mage::getModel('catalog/product')->loadByAttribute('sku', $old_sku)->setSku($new_sku)->save();
}

You can change the SKU easily with this approach, the loop will fetch all the old sku numbers and replace them with new sku numbers which you have provided as a new column in your CSV file. I would love to hear your ideas or experiences of changing SKU numbers programmatically. Subscribe our RSS to receive latest Magento updates.

  • http://www.kleinebolletjes.nl Werner Visser

    I don’t understand option 1. Which fields do you export to a CSV file ? And when you just change the SKU in the CSV file with a new one…. how does Magento know which product to update since this SKU is the identifier.

    Regards,
    Werner Visser
    http://www.kleinebolletjes.nl

  • margesh

    product_id should be the primary identifier, make sure that you have that when you export your data as CSV.

  • Tom

    Could you tell me where to put this code like would it go in

    app/code/core/Mage/Catalog/Model/Convert/Adapter/product.php

    and then after what exactly.  I have been playing with this and several other way with no success.

  • Tom

    I have tried several way on adding this little line
    of code, and get no results. I have added it right after the “public
    function saveRow(array $importData)” which is on line 527 in my
    installation. Then in my export csv file I added a column “new_sku”.

    foreach($products as $product){
    $sku = $product[0];
    $new_sku = $product[1];
    Mage::getModel(‘catalog/product’)->loadByAttribute(‘sku’, $sku)->setSku($new_sku)->save();
    }

    If there is anyone that could tell me what I am doing wrong please help me. The version is 1.5.1

  • Michiel

    Thank you for sharing this! It helped me a lot.

    To answer Tom’s question: I solved this by adding a temporary php file to the root of my Magento installation.

    In the first two lines, I added:

    require_once 'app/Mage.php';
    Mage::app();

    and all went well…

  • Olga Vergej

    For bulk update of products any data fields we are using Advanced Admin Products Manager by Iksanika – which allow us to not make any coding and big CSV updating. It simply allow us to select fields which we whould like to update and show them in Product Manage interface with editable cell in the grid – so we simply change values in the cells of the grid for each specified product and click update. Very nice, simple and nice tool – it is same lots of our time. So we don’t need additional developer effort.

  • Alex

    It is also possible to solve this task through magento API with simple script, which will get collection of products and make specific changes in SKU. Or if it is problematic to write/order script for specific task you can use install external extension, from my point of view Advanced Admin Products Manager is one of the best in bulk products attributes update, including SKUs and any other products attributes.