Magento2: Fastest Way to Import / Update Product Prices in Bulk

3 Shares

Overview

As you know our Magento1 version of the script “Updating product prices in Magento in an easier & faster way” was a massive hit. And we are back with a similar script for Magento 2 which helps you to update the product prices in bulk – probably the easiest and fastest way possible.

Let’s take a look at the standalone PHP script & sample CSV file below:

	<?php
	/**
	*
	* @author Raj KB<magepsycho@gmail.com>
	* @website http://www.magepsycho.com
	* @extension MassImporterPro: Pricing - http://www.magepsycho.com/mass-importer-pro-price-importer-regular-special-tier-group.html
	*/
	ini_set('display_errors', 1);
	ini_set('display_startup_errors', 1);
	error_reporting(E_ALL);

	// Capture warning / notice as exception
	set_error_handler('ctv_exceptions_error_handler');
	function ctv_exceptions_error_handler($severity, $message, $filename, $lineno) {
	if (error_reporting() == 0) {
	return;
	}
	if (error_reporting() & $severity) {
	throw new ErrorException($message, 0, $severity, $filename, $lineno);
	}
	}

	require __DIR__ . '/../app/bootstrap.php';
	$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);

	$obj = $bootstrap->getObjectManager();

	$state = $obj->get('Magento\Framework\App\State');
	$state->setAreaCode('adminhtml');


	/**************************************************************************************************/
	// UTILITY FUNCTIONS - START
	/**************************************************************************************************/
	function _mpLog($data, $includeSep = false)
	{
	$fileName = BP . '/var/log/m2-magepsycho-import-prices.log';
	if ($includeSep) {
	$separator = str_repeat('=', 70);
	file_put_contents($fileName, $separator . '<br />' . PHP_EOL, FILE_APPEND \| LOCK_EX);
	}
	file_put_contents($fileName, $data . '<br />' .PHP_EOL, FILE_APPEND \| LOCK_EX);
	}

	function mpLogAndPrint($message, $separator = false)
	{
	_mpLog($message, $separator);
	if (is_array($message) \|\| is_object($message)) {
	print_r($message);
	} else {
	echo $message . '<br />' . PHP_EOL;
	}

	if ($separator) {
	echo str_repeat('=', 70) . '<br />' . PHP_EOL;
	}
	}

	function getIndex($field)
	{
	global $headers;
	$index = array_search($field, $headers);
	if ( !strlen($index)) {
	$index = -1;
	}
	return $index;
	}

	function readCsvRows($csvFile)
	{
	$rows = [];
	$fileHandle = fopen($csvFile, 'r');
	while(($row = fgetcsv($fileHandle, 0, ',', '"', '"')) !== false) {
	$rows[] = $row;
	}
	fclose($fileHandle);
	return $rows;
	}

	function _getResourceConnection()
	{
	global $obj;
	return $obj->get('Magento\Framework\App\ResourceConnection');
	}

	function _getReadConnection()
	{
	return _getConnection('core_read');
	}

	function _getWriteConnection()
	{
	return _getConnection('core_write');
	}

	function _getConnection($type = 'core_read')
	{
	return _getResourceConnection()->getConnection($type);
	}

	function _getTableName($tableName)
	{
	return _getResourceConnection()->getTableName($tableName);
	}

	function _getAttributeId($attributeCode)
	{
	$connection = _getReadConnection();
	$sql = "SELECT attribute_id FROM " . _getTableName('eav_attribute') . " WHERE entity_type_id = ? AND attribute_code = ?";
	return $connection->fetchOne(
	$sql,
	[
	_getEntityTypeId('catalog_product'),
	$attributeCode
	]
	);
	}

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

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

	}

	function checkIfSkuExists($sku)
	{
	$connection = _getConnection('core_read');
	$sql = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, [$sku]);
	}

	function updatePrices($sku, $price, $storeId = 0)
	{
	$connection = _getWriteConnection();
	$entityId = _getIdFromSku($sku);
	$attributeId = _getAttributeId('price');

	$sql = "INSERT INTO " . _getTableName('catalog_product_entity_decimal') . " (attribute_id, store_id, entity_id, value) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE value=VALUES(value)";
	$connection->query(
	$sql,
	[
	$attributeId,
	$storeId,
	$entityId,
	$price
	]
	);
	}

	/**************************************************************************************************/
	// UTILITY FUNCTIONS - END
	/**************************************************************************************************/

	try {
	$csvFile = 'var/import/prices.csv'; #EDIT - The path to import CSV file (Relative to Magento2 Root)
	$csvData = readCsvRows(BP . '/' . $csvFile);
	$headers = array_shift($csvData);

	$count = 0;
	foreach($csvData as $_data) {
	$count++;
	$sku = $_data[getIndex('sku')];
	$price = $_data[getIndex('price')];

	if ( ! checkIfSkuExists($sku)) {
	$message = $count .'. FAILURE:: Product with SKU (' . $sku . ') doesn\'t exist.';
	mpLogAndPrint($message);
	continue;
	}

	try {
	updatePrices($sku, $price);
	$message = $count . '. SUCCESS:: Updated SKU (' . $sku . ') with price (' . $price . ')';
	mpLogAndPrint($message);
	} catch(Exception $e) {
	$message = $count . '. ERROR:: While updating SKU (' . $sku . ') with Price (' . $price . ') => ' . $e->getMessage();
	mpLogAndPrint($message);
	}
	}
	} catch (Exception $e) {
	mpLogAndPrint(
	'EXCEPTION::' . $e->getTraceAsString()
	);
	}

view raw m2-import-prices-index.php hosted with ❤ by GitHub

	sku	price
	24-MB01	31
	24-MX02	32
	24-UG04	94
	24-UG05	99

view raw prices.csv hosted with ❤ by GitHub

How to run the script?

1. Prepare the Script

Copy/Paste the script from above to the file: [magento2-root]/pub/m2-import-prices-index.php

2. Prepare the CSV Data

Download the sample CSV file as “prices.csv” and update values for SKU & Price columns. Then upload the CSV file to the [magento2-root]/var/import/ directory.

3. Run the Script

You can run script either from browser or from CLI.

3.1 Executing the price bulk update from Browser
Go to the browser and hit the URL:
http://[magento2-base-url]/m2-import-prices-index.php

Which will out as below on successful operation:

Note: If you are using Nginx web server and want to change the script filename other than *-index.php, you may need to edit the nginx.conf.sample file of Magento2 as


# PHP entry point for main application
location ~ (index|get|static|report|404|503|AddYourNewFileNameHere)\.php$ {

And of course need to reload the nginx configuration thereafter:


# Ubuntu
sudo service nginx reload

3.2 Executing the price bulk update script from CLI
Simply run the following command(s) from the Console of your server:


cd /path/to/magento2-root
php -f pub/m2-import-prices-index.php

Which will output as:

Note: This is recommended approach if you want to bulk update CSV data is big.

Hope Magento2 Developers & Store Owners will find this script useful.
Please do show your love towards the script by sharing & commenting below.

ANNOUNCEMENT

We have released the fastest price importer extension for Magento 2 for updating any kinds of prices (regular, cost, MSRP/MAP, special, tier & customer group price) via different methods (web, CLI & cron jobs)

Go to M2 Extension Page

Share3