Welcome Mass Importer Pro: Price Importer Ver 1.1.0 with new features

August 8, 2014  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Updates

Mass Importer Pro: Price Importer is the fastest price import tool available for Magento CE/EE.
Recently MagePsycho team released the most awaited features in Mass Importer Pro: Price Importer Ver 1.1.0

ChangeLog (0.1.5 – 1.1.0)

  • Compatible with Magento CE 1.3.x – 1.9.x & EE 1.12.x – 1.14.x
  • Added cron job based price importing with logging feature
  • Added shell/command line based price importing with logging feature
  • Added price rounding feature (normal rounding, rounding to nearest)
  • Added relative(fixed & percentage) pricing for special, tier & group prices.
  • Changed store view based pricing import to website based(as prices have either global or website level scope)
  • Added store wise price export
  • Added option for deleting special_price, special_from_date, special_to_date by marking the value with ‘x’ or ‘X’
  • Added button for checking csv file format (beside of Price Import button)
  • Added Change log file: Changelog.txt
  • Added Uninstallation file: Uninstall.txt
  • Refactored the code

New Features

1. Cron Job based price importing

To configure this, go to Mass Importer Pro > Manage Settings > Price Settings
You will see the following setting:

Price Importer: Cron Settings

Price Importer: Cron Settings

FieldDescription
Enable CronYes
File Import DirectoryPath from where CSV files will be imported via cron.
Default: var/magepsycho/massimporterpro/price_importerr/cron
Process Imported FilesWhat to do after the files are imported?
Available options are:
No Action
Move To Archive Folder
Delete
Start TimeAt what time cron will run.
If ‘Start Time’ = 00 00 00 and ‘Frequency’ = Daily then equivalent cron expression will be 0 0 * * * (which means cron will be run once a day at midnight)
REF: http://en.wikipedia.org/wiki/Cron
FrequencyFrequency at which cron will run. Options:
Daily
Weekly
Monthly
Error Email RecipientError email will be sent to this address in case of failure
Error Email SenderError Email Sender
Error Email TemplateEmail Template

Notes:
Make sure that cron is enabled for your Magento store.
Refer to the following article for more info:
http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/how_to_setup_a_cron_job

2. Shell/Command line based price importing

There is no any backend settings for Shell based price importing.
You just have to open the terminal and run the following commands.
1. Go to Magento root directory:

cd /path/to/your/magento/root

2. Price Importer help command:

php -f shell/priceimporter.php --help

3. Run price import (without reindexing)

php -f shell/priceimporter.php -- -import "path/to/csv/file.csv"

4. Run price import (with reindexing)

php -f shell/priceimporter.php -- -import "path/to/csv/file.csv" -reindex
Command line price import (with reindexing)

Command line price import (with reindexing)

Command line price import (without reindexing)

Command line price import (without reindexing)

So far in Mass Importer Pro, Prices can be imported via Web Forms, Cron Jobs & Command Line. From wherever you run import, You can view the import history from menu ‘Mass Importer Pro’ > ‘Price Importer’ > ‘Import History’ Tab > Filter the results by ‘Imported Via’.

Price Importer: Import History

Price Importer: Import History

3. Price rounding feature

There are three types of options for price rounding:

Price Importer: Price Rounding

Price Importer: Price Rounding

Rounding TypeDescription
No RoundingNo action with the price
Round NormallyUses php’s round() function. Examples:
9.43 -> 9.00
9.63 -> 10.00
Round to NearestYou have to set Rounding value for this. Examples:
9.43 -> 9.00 + (If Rounding Value = 0.99) = 9.99
9.43 -> 9.00 + (If Rounding Value = 0.50) = 9.50
This option is useful if you want to update your prices ending with .99, .50 etc.

4. Relative(fixed & percentage) based price importing

You can self increment or decrement the prices by fixed value or percentage. Also you can set ‘special_price’, ‘tier_price’ & ‘group_price’ to be certain percentage of ‘price’.
Valid Examples:

ValueDescription
10Fixed Value
+10Increment current value by 10
-10Decrement current value by 10
10%10% of base price (note that there is no any sign in front)
Note: This applies only for price types: special_price, tier_price & group_price
+10%Increment current price by 10%
 -10% Decrement current price by 10%

5. Option to validate your CSV data

You can validate the CSV data before importing any prices.
Go to menu ‘Mass Importer Pro’ > ‘Price Importer’ > Select the import file > Click on the ‘Check Import’ button which will give you the check results.

Price Importer: Check Import Data

Price Importer: Check Import Data

6. Website wise price Import

Before Ver 1.1.0 You had to use combination of sku + store_id as unique identifier to update prices. Since Magento has either global or website level scope for prices, we have replaced ‘store_id’ by ‘website_id’.

In order to find the website_id, go to System > Manage Stores > Click on the related Website > Note the website_id from URL.
By default website_id = 0 (global)

7. Website wise price Export

You can export prices in format compatible with Mass Importer Pro from menu ‘Catalog’ > ‘Manage Products’.
How to export prices is clear from the below screenshot:

Price Importer: Export Prices

Price Importer: Export Prices

Also see: Importing regular, special, tier & group prices using ‘Mass Importer Pro: Price Importer’ Extension

Those were the major updates on newer version. Besides we have fixed some bugs & improved the functionality.
If you have any queries/issues regarding the extension, please do not hesitate to Contact Us.

Importing regular, special, tier & group prices using ‘Mass Importer Pro: Price Importer’ Extension

July 1, 2012  |  4 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

Recently, the MagePsycho team developed Mass Importer Pro: Price Importer Extension, the fastest price import tool for Magento. It provides for importing/updating different prices, such as regular, special, tier, and group prices. This article will serve as documentation for the Mass Importer Pro: Price Importer Extension.

1. INSTALLATION

0. Before installation, make sure that Cache(System > Cache Management) / Compilation(System > Tools > Compilation) is disabled.
1. After the purchase of the extension, you can immediately download the extension from www.magepsycho.com > My Account > My Downloadable Products.
2. Unzipping it, you will get ‘app’ & ‘var’ folders and ReadMe.txt file.
Upload the ‘app’ & ‘var’ folder to the root of your Magento installation.
Note that upload of ‘var’ folder is optional as it contains some sample csv files only.
4. Installation is done!
5. After installation, you can enable the Cache / Compilation if required.

2. CONFIGURATION

After installation, login to the admin. If you get 404 error page in the extension page then try to logout and re-login.
You will now see the top level menu called ‘Mass Importer Pro’ in the admin:

Mass Importer Pro Menu

Extension Activation

0. Go to ‘Mass Importer Pro’ > ‘Price Importer’ menu, you will see the following form (with disabled ‘Run Import’ button) which means you need to enable the extension and activate it with provided license key.

Disabled Run Import Button


1. Go to ‘Mass Importer Pro’ > ‘Manage Settings’ menu, you will see the following section for configuration:

Configuration Section for Mass Importer Pro: Price Importer


And configure as:
[General Settings]
Enabled = Yes
Domain Type = Production / Development
License Key = [will be provided in the email shortly after the purchase of the extension]

[Price Settings]
Tier Price Import Type = Merge / Replace (Group / All)
Group Price Import Type = Merge / Replace (Group / All)
Re-Index Product Price After Import = Yes / No
[This setting will be used as default for importing prices]
2. Go to ‘Mass Importer Pro’ > ‘Price Importer’ menu, You will see the activated ‘Run Import’ button which means you are ready to go with unlimited price imports.

Enabled / Activated Import Form


3. Configuration is done!

3. IMPORTING PRICES

After the extension gets installed and configured properly, you should be able to import prices using csv file. Before importing, you need to know the supported csv fields and valid data format required for the extension.

Preparing CSV Fields
You can refer to the [Sample CSV File] in order to find all the supported fields, which are:
“sku”,”store_id”,”cost”,”price”,”special_price”,”special_from_date”,”special_to_date”,”website_id”,”tier_price:_all_”
,”tier_price:Wholesale”,”tier_price:[Any-Customer-Group]”,”group_price:Wholesale”,”group_price:[Any-Customer-Group]”

FieldDescription
skuSKU of the product
store_idStore Id which can be found in ‘core_store’ table with ‘store_id’ field, If you want to update for all stores then simply use store_id = 0
costCost
priceRegular/Unit Price
special_priceSpecial Price, If ‘special_from_date’ & ‘special_to_date’ fields are left empty then price is special for unlimited time period
special_from_dateSpecial From Date, Date field should be in [YYYY-mm-dd] format. For example: 2012-06-15
special_to_dateSpecial To Date, Date field should be in [YYYY-mm-dd] format. For example: 2012-08-25
website_idWebsite Id, Used for Website wise tier / group pricing import.
tier_price:_all_Tier price for all groups, Values for tier_price should be in tierQty:tierPrice format separated by semi-colon(;). For example: 10:100;20:90;50:70
If you want to delete the tier price then you can mark it with value ‘x’. For example: 10:x;20:x;50:70
tier_price:[CustomerGroupName]If you want tier price for specific groups then you need to add new column in the csv with name = tier_price:[CustomerGroupName]
For example: tier_price:Wholesale if customer group is Wholesale, tier_price:Retailer if customer group is Retailer.
You can find the customer group name from backend: Customers > Customer Groups.
Values should be in same format as above field(tier_price:_all_).
group_price:[CustomerGroupName]Group Price is new feature available in Magento since 1.7.0.0 version. If you want customer group wise price then you need to add new column in the csv with name = group_price:[CustomerGroupName]
For example: group_price:Wholesale if customer group is Wholesale, group_price:Retailer if customer group is Retailer.
Field value should contain the price only.

Among above fields, only sku is the compulsory field.
You can use the other fields depending on your requirement. You can either leave the column value empty or remove the column itself if you don’t want to update that field.

EDIT
Mass Importer Pro: Price Importer (version > 1.1.0) now supports price export feature in CSV format which reduces the complexity of manual process of CSV fields preparation. Now you can easily export the required price types of the selected products using this extension. Refer the following snapshot for more:

Mass Importer Pro: Export Price Feature

Note: Currently Price Importer supports only the CSV file type, using a comma (,) as delimiter and double quotes(“) as enclosure. We recommend you to use OpenOffice tool for formatting CSV file.

When CSV file is ready, go to ‘Mass Importer Pro’ > ‘Price Updater’ menu and go ahead for importing with the following three steps:
1. Selecting CSV File:
Select the required CSV file from ‘Select File To Import’ dropdown. If required csv file is not in the dropdown list then you can either manually upload via FTP to path ./var/massimporterpro/price_updater/ OR via form using ‘Upload File’ button.

Step 1: Select CSV file to import


2. Configuring Settings:
You can configure the Price Settings from ‘Price Settings’ tab. If not configured the settings will be taken from System > Configuration.

Step 2: Configure Import Settings


3. Hit ‘Run Import’ button
After submitting the form, you will see the message mentioning how many rows were imported, how many rows were skipped etc.

Result Message After Import


To view more information about the import you can go to ‘Import History’ tab and can view the log data after clicking on the ‘View Log’ link, which will give you the data used during import operation and result of success, failure or skipped.

Import History Tab

Import Log Data in readable form

That’s all about installation, configuration & importing regarding Mass Importer Pro: Price Importer extension.
Once you have used the import operation you will find it to be one of the fastest of its type (as it imports/updates thousands of product prices within few seconds). This is not just a claim on my part, the plugin has proven itself.

Happy Importing with Mass Importer Pro: Price Importer!

Updating product prices in Magento in an easier & faster way

November 4, 2011  |  66 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

Product prices can be updated via default import profile though but this is very slow and requires lots of csv fields(besides sku & price) for updating.
Today i am going to talk about updating product prices just by using csv with two fields: sku & price(new) which is very fast enough even for thousands of products.

Steps:

1> Prepare CSV file(prices.csv) with two fields: sku & price and upload in the root of Magento installation.
You can see the snapshot how it should look like:

prices.csv

prices.csv


Note: Field/Values should be enclosed within double quotes(“) and separated by a comma(,). I would prefer OpenOffice for csv formatting.

2> Create a file: update_prices.php in the root of magento installation and paste the following code:

<?php
/**
 * @author		MagePsycho <info@magepsycho.com>
 * @website		http://www.magepsycho.com
 * @category	Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
	return Mage::getSingleton('core/resource')->getConnection($type);
}

function _getTableName($tableName){
	return Mage::getSingleton('core/resource')->getTableName($tableName);
}

function _getAttributeId($attribute_code = 'price'){
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
				FROM " . _getTableName('eav_attribute') . "
			WHERE
				entity_type_id = ?
				AND attribute_code = ?";
	$entity_type_id = _getEntityTypeId();
	return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}

function _getEntityTypeId($entity_type_code = 'catalog_product'){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
	return $connection->fetchOne($sql, array($entity_type_code));
}

function _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));

}

function _checkIfSkuExists($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . "	WHERE sku = ?";
	$count		= $connection->fetchOne($sql, array($sku));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _updatePrices($data){
	$connection		= _getConnection('core_write');
	$sku			= $data[0];
	$newPrice		= $data[1];
	$productId		= _getIdFromSku($sku);
	$attributeId	= _getAttributeId();

	$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
				SET  cped.value = ?
			WHERE  cped.attribute_id = ?
			AND cped.entity_id = ?";
	$connection->query($sql, array($newPrice, $attributeId, $productId));
}
/***************** UTILITY FUNCTIONS ********************/

$csv				= new Varien_File_Csv();
$data				= $csv->getData('prices.csv'); //path to csv
array_shift($data);

$message = '';
$count   = 1;
foreach($data as $_data){
	if(_checkIfSkuExists($_data[0])){
		try{
			_updatePrices($_data);
			$message .= $count . '> Success:: While Updating Price (' . $_data[1] . ') of Sku (' . $_data[0] . '). <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: While Upating  Price (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'<br />';
		}
	}else{
		$message .=  $count .'> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.<br />';
	}
	$count++;
}
echo $message;

3> Open your browser and run the following url:
http://your-magento-url/update_prices.php
You will see how fast the prices of corresponding skus are updated.
I have tested with 2000 proudcts and update was accomplished within seconds. Isn’t that fast enough?

EDIT
Recently we have developed an extension for importing all types of prices(regular, special, tier & group prices) via web-interface using the same raw SQL concept. Here goes the link for the extension:
Mass Importer Pro: Price Importer (Regular, Sepcial, Tier & Group Prices)

Thanks for reading. Please share your success story with this code.

Happy Importing!!