Magento Mass Import and Export of Product Unit Price and Tier Price Changes

created May 14, 2012, last updated May 14, 2012.

.
closeThis post was last updated 11 years 10 months 7 days ago, some of the information contained here may no longer be actual and any referenced software versions may have been updated!

If you ever need to manipulate basic product information in Magento on a large scale, for example mass price changes using the built in Magento export and import feature can be a very slow process, simply changing the price on a 1000 products takes a very long time to process using the default Magento importer.

Maybe you want to simply increase or decrease product pricing by a certain percentage, add or remove VAT or just reprice everything. I had the requirement to take products from a Wholesale store into a new Retail store and manipulate all the pricing, including tier pricing with VAT and a percent change.

Its possible to export all the pricing and make the changes in excel and reimport the data, but this is a slow process and the built in product importer does not support tier pricing at all so I needed to look at another way of mass changing magento pricing that allows me to change all product pricing including tier pricing quickly and easily.

There are two ways to go about this with PHP, you can perform the price change on the original pricing and export the data and then reimport it, or you can perform the price changes directly on the Magento database. I chose the former as I want to be sure my data is correct before I make any changes, with the data exported you can look at it in Excel and confirm it is correct before importing it.

MAGENTO UNIT PRICE AND Tier Pricing EXPORT

First lets look at exporting the unit price and tier price of all products to HTML so we can get a good look at it. There are a few examples of how to get product data out of Magento with PHP around, I found that most of them did not process grouped products the way they are used in my Magento store correctly so I adapted the code for my requirements which included testing for invisible simple products that were children of grouped products as well as manipulating the pricing for my requirements with a percent change for price changes and VAT.

I made three versions one to output all unit and tier pricing directly to HTML, and two others to ouput unit pricing and tier pricing seperately to a .csv file formatted correctly for a MAGMI import.

Lets look at exporting all the products with existing unit and tier prices to HTML, here is a link to working example with the PHP code below

http://dev.gaiterjones.com/magento/myexport/exportAllPricesHTML.php

Here we can see all the unit pricing and tier pricing clearly displayed and whatever price manipulation is being applied.

Price Manipulation

My requirement was to change all product prices by a certain percentage and to add VAT at a certain percentage. So the formula was something like

$priceNew=$priceOld + ($priceOld * ($percentChange / 100));

$priceNew=$priceOld + ($priceOld * ($VATRate / 100));

So again using the code above lets us add a 10% price increase and VAT at 20% and we can check the increased pricing and tier pricing. This link will show the price change, or you can enter the values in the HTML form.

http://dev.gaiterjones.com/magento/myexport/exportAllPricesHTML.php?percentIncrease=10&percentVAT=20

We are just displaying price data here, nothing in the Magento database is being changed! I know VAT is also simply a percent change in price, so in the above example percent change of 30% would be the same as 10% increase + 20% VAT. The script currently only checks for a % change, so 0% change and 15% VAT won’t work.

Here is the PHP code for the HTML export, it should be ran from a sub folder of your Magento installation, i.e. magento/myexport/exportAllPricesHTML.php.

You can download all the files using the link below.

<?php
// Magento export tier price of all product and calculate % increase + VAT formatted for MAGMI import
// blog.gaiterjones.com
// 15.05.2012
//

// get Magento
require_once '../app/Mage.php';
umask(0);
Mage::app();
Mage::app()->loadArea(Mage_Core_Model_App_Area::AREA_FRONTEND);

header("Content-type:text/octect-stream");
header("Content-Disposition:attachment;filename=exportMyTierPrices.csv");

if (empty($_GET["maxTiersToExport"])) { $maxTiersToExport=4; } else { $maxTiersToExport=$_GET["maxTiersToExport"]; }
if (empty($_GET["percentChange"])) { $percentChange=0; } else { $percentChange=$_GET["percentChange"]; }
if (empty($_GET["percentVAT"])) { $percentVAT=0; } else { $percentVAT=$_GET["percentVAT"]; }

// load store
$storeId    = Mage::app()->getStore()->getId();
$product    = Mage::getModel('catalog/product');
$products   = $product->getCollection()->addStoreFilter($storeId)->getAllIds();

// write header
echo '"sku","tier_price:_all_"'. "\n";

// loop through all products
foreach($products as $productid)
{
// load product data
$product = Mage::getModel('catalog/product')->load($productid);

$existingTierPrice = $product->tier_price;

// ignore grouped products
if($product->getTypeId() == "grouped") {continue;}

	// get tier prices
	if ($existingTierPrice)
	{
		$sku = $product->getSku();
		$unitPrice = round($product->getPrice(),2);
		$output= '"'. $sku. '","';
		$numItems = count($existingTierPrice);
		$i = 0;

		foreach($existingTierPrice as $key=>$value)
		{

			if ($i===$maxTiersToExport) { break; } // limit tiers

			$tierPrice = round($value['price'],2);

				if ($percentChange != 0) { $tierPrice=priceManipulation($tierPrice,$percentChange,$percentVAT); }

				if($i+1 == $numItems)
				{
					$output=$output. round($value['price_qty'],1). ':'. $tierPrice. '"';
				} else {
					$output=$output. round($value['price_qty'],1). ':'. $tierPrice. ';';
				}

			$i++;
		}
			echo $output. "\n";

	}

}

function priceManipulation ($unitPrice,$percentChange,$percentVAT)
{
		// price manipulation
		// add percent change to unit price
		$newUnitPrice=$unitPrice + ($unitPrice * ($percentChange / 100));
		// add VAT %
		if ($percentVAT != 0) { $newUnitPrice=$newUnitPrice + ($newUnitPrice * ($percentVAT / 100)); }
		// round down to 0.x9 cents (if price is not zero)
		if ($newUnitPrice > 0) { $newUnitPrice=round($newUnitPrice,1) -0.01; }

		return($newUnitPrice);
}
?>

EXPORT UNIT and TIER PRICES to CSV AND IMPORT USING MAGMI

Two other similar PHP scripts are used to export the unit and tier price data to CSV. Click the links from the HTML page to export the data. The exported unit pricing and tier pricing data is exported in a CSV format readable by the excellent MAGMI Magento Mass import utility and I recommend you install this (with tier price plugin) and use it to import the exported unit and tier pricing csv data generated by these PHP scripts.

Magmi will import changes to pricing for 1000’s of products in seconds.

I have also included a price import PHP script that is useful for testing your price import data it will show straight away any errors and which product generated the error, something that is not so clear in MAGMI. Save your unit price CSV export file in the same folder as the scripts as importMyUnitPrices.csv for this to work. The import script comes courtesy of MagePsycho and only works with Unit Prices and not Tier Prices.

You can download all the scripts used for exporting data in this package.

 

Removing all Magento Tier Pricing

If you are changing the number of tier prices you might want to delete all the tier pricing data first and then import the changes. You can remove all Magento product tier pricing with this sql command

TRUNCATE TABLE  `catalog_product_entity_tier_price`

Caution

Be sure to do your price change export and import testing on a development or staging server before you apply any changes to your live database. After updating tier pricing be sure to refresh all your Magento caches so that the changes are recognised by the system.

Changing other Data

I was mainly interested in price changes here, but its possible to mass export and change other data too using these scripts, i.e. product names, SKU, inventory etc.

 

Comments

  1. Tom says:

    You know, when I search for Magento solutions on google, there’s normally a 1-2% chance I’ll find exactly what I’m looking for. This is one of those situations.

    I think I might play the lottery tomorrow, I could be onto a lucky streak here!

  2. Dan Kelly says:

    Hey PAJ,
    How would I change all the prices in my database around. For example I want to be able to update all prices to 1.5% and move all regular prices to the “Special price”

    http://screencast.com/t/SJCHCBNq4h

    I think I can export with your code and create a csv file.. I may can edit that and inport it back in…

    Just wanted to get your advice..

    Thanks, Dan

    • PAJ says:

      I would do exactly as you have suggested. Export your pricing data with no changes this will become your special price, then export the data with the 1.5% increase. Use excel to cut and paste the regular price into the special price field for all products and the import your new product pricing using MAGMI. Do it all on your dev/staging server first to test that it works.

      Peter.

  3. Winnie says:

    Would it be possible to use the same script but to bulk update the special price, Special Price From Date, and Special Price To Date fields of 10-15 products? We have specific promotions for every week and this script would definitely help.

    Thank you

      • Winnie says:

        Hello, I got to the point on how to upload magmi into root folder and such. But however a bit lost on how should I export the csv file into magmi. And what should I include in CSV file to edit fields of special price, Special Price From Date, and Special Price To Date. I have already exported a csv file from your script which contains the sku and price.

        Thank you so much!

      • PAJ says:

        Create a new .csv file in a text editor. It needs to look something like this

        sku,price,special_price,tier_price:_all_,special_from_date,special_to_date
        SKU1,9.99,6.99,5:5.99;10:4.99,2013-03-01,2013-03-31
        SKU2,99.99,49.99,5:39.99;10:29.99,2013-03-01,2013-03-31

        This is two products with special prices (and tier prices) for March. If you don’t need to change the tier take that part out. Hope that helps.

      • Winnie says:

        When I export, the csv file is cut off and missing around 200 or more products, is this normal?

        Thank you

  4. Elvin says:

    Hello PAJ

    I have used this code, I really appreciate your work. Sorry to say but I am facing one issue, I have placed the script “exportAllPricesHTML.php” to myexport folder and when I access that through url I am able to get an csv file called “exportMyTierPrices(2).csv” Now the issue is like when I open this file I am able to get 2 products sku and tier price but suddenly then an error occurs like this
    Fatal error: Maximum execution time of 30 seconds exceeded in /home/whole/public_html/lib/Zend/Db/Select.php on line 1179
    Can you please let me know what is the issue at the earliest please

    Thanks
    Regards
    Elvin M. Mclean

    • PAJ says:

      Hello,

      The error you are seeing is caused by the PHP script running longer than the maximum allowed time in your default host PHP settings. You can try and change this by adding the following to the export php script

      set_time_limit (600);

      Or you can change your default php settings to allow for a longer execution time. If you Google ‘php maximum execution’ you will find all the answers to resoving this issue.

This site uses Akismet to reduce spam. Learn how your comment data is processed.