This 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.