Product links on Magento CSV import

product-links-CSV-import

When I started working at Inchoo, one of our clients requested a functionality to be added to Magento CSV product import. Client wanted to know if an attribute value of one of the products that they were importing already had a matching value in the database and they also wanted to be able to jump to that product via link. Find out more about handling this specific situation.

The attribute in question was a custom SKU that they used internally within the company, and as they were importing large number of products they didn’t want to have two products with the same custom SKU.

The biggest problem about this task was the issue of available memory as the number of the products in the database and the size of CSV files were large. In the code below I’ll focus only on the logic that was needed to make this work. For purpose of demonstrating this I’ll be using try() section of the validateAction() in Mage_ImportExport_Adminhtml_ImportController. 

First, we have to make the source file (CSV) available to us by adding another step to the source validation process:

$source = $import->setData($data)->uploadSource();
$validationResult = $import->validateSource($source);

Now there is a check whether the file is valid or not, but in our case the information about the duplicate attribute values should be displayed regardless of the validation result, so we will have to have the same logic in two places.

Second, we have to get the product data we need from Magento database. For this example we will be using ‘name’ attribute as the search value.

//-- set search value flag
$search_value_exists = 0;
 
//-- search_values array for storing search values from all products
$search_values = array();
 
$products_data = Mage::getResourceModel('catalog/product_collection')
		 ->addAttributeToSelect('name')
		 ->addAttributeToFilter('visibility', array('neq' => 1));

We will have to limit number of products that we will be loading while collecting the data from the database to avoid the memory issues. For this example a limit of 100 products per page is used.

//-- set page limit to 100
$products_data->setPageSize(100);
 
//-- get the number of pages
$pages = $products_data->getLastPageNumber();
$currentPage = 1;

And now we will get all the necessary data. The reason for taking SKU values is for uniqueness (other unique values can also be used), as the search values themselves may not be unique (e.g. two products may have the same name). After each page of 100 products is done, we clear the collection and load the next one.

do{
	$products_data->setCurPage($currentPage);
	$products_data->load();
 
	foreach($products_data as $product_data){
		$search_values[$product_data->getId()] = array('sku' => $product_data->getSku(),
							       'name' => $product_data->getName());
	}
 
	$currentPage++;
	$products_data->clear();
} while ($currentPage <= $pages);

For our example we will be displaying the product links with SKUs, so a link between search value and SKU will have to be stored in an array.

//-- array for storing skus belonging to duplicate search values
$sku_search_value = array();

Next, we open the CSV file and get column keys for SKU and search value.

//-- open csv file
$fh = fopen($source, 'r');
 
//-- get key for the search value and sku from the first row of csv
$rowData = fgetcsv($fh, 0, ',', '"');
$search_value_key = array_search('name', $rowData);
$sku_key = array_search('sku', $rowData);

Now comes the part where we will read CSV file row by row and compare the search value with the data that we got from the database (row by row reading of CSV file is used to avoid memory issues). Again, SKU here is used as a unique field to differentiate between those products that have the same search value (e.g. two different products have the same name). If the duplicate is found, then the link is created and stored within an array that will be used later to generate links to products.

//-- for each csv search value (excluding the first row - the column names)
//-- check if that search value exists within the search_values array
while ($rowData = fgetcsv($fh, 0, ',', '"')){
	$rowArray = array('sku' => $rowData[$sku_key],
		          'name' => $rowData[$search_value_key]);
 
	if(in_array($rowArray, $search_values)){
		$link = Mage::helper('adminhtml')
			->getUrl('adminhtml/catalog_product/edit',array('id' => array_search($rowArray, $search_values)));
 
		//-- if there is need for link to open a specific tab then the line below can be uncommented and modified
		//-- the group number can be found in eav_attribute_group table and other tabs will have their specific names
		//-- (like inventory) that can be found by looking in respective layout xml files and searching for <action method="addTab">
		//$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_group_8/'.substr($link, strpos($link, '/key/')+5, strlen($link));
 		//$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_inventory/'.substr($link, strpos($link, '/key/')+5, strlen($link));
 
		$sku_search_value[] = $rowData[$sku_key].' ('.'<a target="_blank" rel="external" href="'.$link.'">'.$rowData[$search_value_key].'</a>'.')';
		$search_value_exists = 1;
	}
}

After that, we close the CSV file and add the links to products with duplicate search values. The import button is disabled by setting the second parameter to false.

//-- close csv file
fclose($fh);  
 
//-- if the search value exists do not show the import button and notify the user
if($search_value_exists){
	$resultBlock->addSuccess(
		$this->__('File is valid, but there are names that already exist in magento products for following SKUs (in CSV): '.'<br>'.implode(', ', $sku_search_value).'<br>'),
		false
	);
} else {
	$resultBlock->addSuccess($this->__('File is valid! To start import process press "Import" button'), true);
}

In the end the whole try() section of the validateAction() should look like this (modified parts are marked by START and END comments):

try {
	/** @var $import Mage_ImportExport_Model_Import */
        $import = Mage::getModel('importexport/import');
	//-- 1st modification START
        //$validationResult = $import->validateSource($import->setData($data)->uploadSource());
	$source = $import->setData($data)->uploadSource();
	$validationResult = $import->validateSource($source);
	//-- 1st modification END
 
	if (!$import->getProcessedRowsCount()) {
		$resultBlock->addError($this->__('File does not contain data. Please upload another one'));
	} else {
        	if (!$validationResult) {
			if ($import->getProcessedRowsCount() == $import->getInvalidRowsCount()) {
				$resultBlock->addNotice(
                                	$this->__('File is totally invalid. Please fix errors and re-upload file')
                            	);
                        } elseif ($import->getErrorsCount() >= $import->getErrorsLimit()) {
                        	$resultBlock->addNotice(
                                	$this->__('Errors limit (%d) reached. Please fix errors and re-upload file', $import->getErrorsLimit())
                            	);
                        } else {
                        	if ($import->isImportAllowed()) {
 
                                //-- 2nd modification START
                                //-- set search value flag
                                $search_value_exists = 0;
 
                                //-- search_values array for storing search values from all products
                                $search_values = array();
 
                                $products_data = Mage::getResourceModel('catalog/product_collection')
                                                ->addAttributeToSelect('name')
                                                ->addAttributeToFilter('visibility', array('neq' => 1));
 
                                //-- set page limit to 100
                                $products_data->setPageSize(100);
 
                                //-- get the number of pages
                                $pages = $products_data->getLastPageNumber();
                                $currentPage = 1;
 
                                do{
                                    $products_data->setCurPage($currentPage);
                                    $products_data->load();
 
                                    foreach($products_data as $product_data){
                                        $search_values[$product_data->getId()] = array('sku' => $product_data->getSku(), 'name' => $product_data->getName());
                                    }
 
                                    $currentPage++;
                                    $products_data->clear();
                                } while ($currentPage <= $pages);
 
                                //-- array for storing skus belonging to duplicate search values
                                $sku_search_value = array();
 
                                //-- open csv file
                                $fh = fopen($source, 'r');
 
                                //-- get key for the barcode and sku from the first row of csv
                                $rowData = fgetcsv($fh, 0, ',', '"');
                                $search_value_key = array_search('name', $rowData);
                                $sku_key = array_search('sku', $rowData);
 
                                //-- for each csv search value (excluding the first row - the column names)
                                //-- check if that search value exists within the search_values array
                                while ($rowData = fgetcsv($fh, 0, ',', '"')){
                                    $rowArray = array('sku' => $rowData[$sku_key],
                                                      'name' => $rowData[$search_value_key]);
 
                                    if(in_array($rowArray, $search_values)){
                                        $link = Mage::helper('adminhtml')
                                                ->getUrl('adminhtml/catalog_product/edit',array('id' => array_search($rowArray, $search_values)));
 
                                        //-- if there is need for link to open a specific tab then the line below can be uncommented and modified
                                        //-- the group number can be found in eav_attribute_group table and other tabs will have their specific names
                                        //-- (like inventory) that can be found by looking in respective layout xml files and searching for <action method="addTab">
                                        //$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_group_8/'.substr($link, strpos($link, '/key/')+5, strlen($link));
                                        //$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_inventory/'.substr($link, strpos($link, '/key/')+5, strlen($link));
 
                                        $sku_search_value[] = $rowData[$sku_key].' ('.'<a target="_blank" rel="external" href="'.$link.'">'.$rowData[$search_value_key].'</a>'.')';
                                        $search_value_exists = 1;
                                    }
                                }
 
                                //-- close csv file
                                fclose($fh);  
 
                                //-- if the search value exists do not show the import button and notify the user
                                if($search_value_exists){
                                    $resultBlock->addNotice(
                                        $this->__('Please fix errors and re-upload file. File also contains names that already exist in magento products for following SKUs (in CSV): '.'<br>'.implode(', ', $sku_search_value).'<br>'),
                                        false
                                    );
                                } else {
                                    $resultBlock->addNotice(
                                        $this->__('Please fix errors and re-upload file or simply press "Import" button to skip rows with errors'),
                                        true
                                    );
                                }
                                //-- 2nd modification END
                            } else {
                                $resultBlock->addNotice(
                                    $this->__('File is partially valid, but import is not possible'), false
                                );
                            }
                        }
                        // errors info
                        foreach ($import->getErrors() as $errorCode => $rows) {
                            $error = $errorCode . ' ' . $this->__('in rows:') . ' ' . implode(', ', $rows);
                            $resultBlock->addError($error);
                        }
                    } else {
                        if ($import->isImportAllowed()) {
                                //-- 3rd modification START
                                //-- set search value flag
                                $search_value_exists = 0;
 
                                //-- search_values array for storing search values from all products
                                $search_values = array();
 
                                $products_data = Mage::getResourceModel('catalog/product_collection')
                                                ->addAttributeToSelect('name')
                                                ->addAttributeToFilter('visibility', array('neq' => 1));
 
                                //-- set page limit to 100
                                $products_data->setPageSize(100);
 
                                //-- get the number of pages
                                $pages = $products_data->getLastPageNumber();
                                $currentPage = 1;
 
                                do{
                                    $products_data->setCurPage($currentPage);
                                    $products_data->load();
 
                                    foreach($products_data as $product_data){
                                        $search_values[$product_data->getId()] = array('sku' => $product_data->getSku(),
                                                                                       'name' => $product_data->getName());
                                    }
 
                                    $currentPage++;
                                    $products_data->clear();
                                } while ($currentPage <= $pages);
 
                                //-- array for storing skus belonging to duplicate search values
                                $sku_search_value = array();
 
                                //-- open csv file
                                $fh = fopen($source, 'r');
 
                                //-- get key for the barcode and sku from the first row of csv
                                $rowData = fgetcsv($fh, 0, ',', '"');
                                $search_value_key = array_search('name', $rowData);
                                $sku_key = array_search('sku', $rowData);
 
                                //-- for each csv barcode (excluding the first row - the column names)
                                //-- check if that barcode exists within the barcodes[] array
                                while ($rowData = fgetcsv($fh, 0, ',', '"')){
                                    $rowArray = array('sku' => $rowData[$sku_key],
                                                      'name' => $rowData[$search_value_key]);
 
                                    if(in_array($rowArray, $search_values)){
                                        $link = Mage::helper('adminhtml')
                                                ->getUrl('adminhtml/catalog_product/edit',array('id' => array_search($rowArray, $search_values)));
 
                                        //-- if there is need for link to open a specific tab then the line below can be uncommented and modified
                                        //-- the group number can be found in eav_attribute_group table and other tabs will have their specific names
                                        //-- (like inventory) that can be found by looking in respective layout xml files and searching for <action method="addTab">
                                        //$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_group_8/'.substr($link, strpos($link, '/key/')+5, strlen($link));
                                        //$link = substr($link, 0, strpos($link, '/key/')).'/tab/product_info_tabs_inventory/'.substr($link, strpos($link, '/key/')+5, strlen($link));
 
                                        $sku_search_value[] = $rowData[$sku_key].' ('.'<a target="_blank" rel="external" href="'.$link.'">'.$rowData[$search_value_key].'</a>'.')';
                                        $search_value_exists = 1;
                                    }
                                }
 
                                //-- close csv file
                                fclose($fh);  
 
                                //-- if the search value exists do not show the import button and notify the user
                                if($search_value_exists){
                                    $resultBlock->addSuccess(
                                        $this->__('File is valid, but there are names that already exist in magento products for following SKUs (in CSV): '.'<br>'.implode(', ', $sku_search_value).'<br>'),
                                        false
                                    );
                                } else {
                                    $resultBlock->addSuccess(
                                        $this->__('File is valid! To start import process press "Import" button'), true
                                    );
                                }
                                //-- 3rd modification END
                        } else {
                            $resultBlock->addError(
                                $this->__('File is valid, but import is not possible'), false
                            );
                        }
                    }
                    $resultBlock->addNotice($import->getNotices());
                    $resultBlock->addNotice($this->__('Checked rows: %d, checked entities: %d, invalid rows: %d, total errors: %d', $import->getProcessedRowsCount(), $import->getProcessedEntitiesCount(), $import->getInvalidRowsCount(), $import->getErrorsCount()));
                }

 The end result for a valid CSV file would look something like this:

import_product_links

Have you ever met with a situation like this? What’s your take on it?


About Zoran Blagojevic

Backend Developer

Zoran is Backend Developer who has skills and experience working with C, OpenMP, OpenCV, Procedural Language/Structured Query Language, MySQL and different Oracle products.

Read more posts by Zoran / Visit Zoran's profile

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.