I am facing an error while updating product price because of extra white space around sku code. Any one know how can I remove that space from all product sku code.
-
Which Magento version are you using?– Jaimin SutariyaCommented Feb 27, 2017 at 1:58
-
Its magento CE version 1.9.2x– AkashCommented Feb 27, 2017 at 3:52
-
Did you get solution for this issue?– Jaimin SutariyaCommented Mar 10, 2017 at 9:45
-
No still not when i am trying to run that file on browser it's only loading and loading no result can you help me in this ?– AkashCommented Mar 10, 2017 at 9:47
-
Can you let me know how much number of products you have in your setup?– Jaimin SutariyaCommented Mar 10, 2017 at 9:49
|
Show 2 more comments
2 Answers
Here is how you can remove whitespace from SKU values. Create a new php file e.g. updateSku.php in your Magento root folder and put this code in it. Then run file with URL : YOUR_MAGENTO_HOST/updateSku.php
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<?php
require_once 'app/Mage.php';
umask(0);
Mage::app('default');
?>
<html><head><title>Trim Product SKU</title><style type="text/css">h1,thead{text-align:center}.result-table td{padding:3px 0 3px 15px}body{margin:0 auto;width:65%}.main-div,.result-table{width:100%;box-shadow:0 1px 11px #000;float:left}h1{margin-top:10px;text-decoration:underline}.main-div{margin:0 auto;border:1px outset #fff}.result-table{margin:5px auto 0;font-size:13px}.suggestion-from{clear:both;float:left;font-size:11px;line-height:26px;margin-left:5px;position:absolute}form{padding:30px 30px 0}.search-button{float:right;margin-top:25px}thead{font-size:20px}</style></head><body><h1>Trim Product SKU</h1><div class="main-div"><form action="" method="post"><table><tr><td><label><?php echo "Product from";?></label><td><input type="text" name="from" id="from" value="<?php echo (isset($_POST['from']))?$_POST['from']:"" ?>"/><label class="suggestion-from">Product From</label></td></tr><tr><td><label><?php echo "Product to";?></label><td><input type="text" name="to" id="to" value="<?php echo (isset($_POST['to']))?$_POST['to']:"" ?>"/><label class="suggestion-from">Product To</label></td></tr><tr></tr><tr><td colspan="2"><input class="search-button" type="submit" title="Trim" value="Trim"/></td></tr></table></form></div></body></html>
<?php
if($_POST){
$from = $_POST['from'];
$to = $_POST['to'];
echo "<table border='1' class='result-table'><thead><tr><td colspan='3'>Import Data</td></tr></thead><tbody><tr><td>Product Id</td><td>Trimmed SKU</td></tr>";
trimSKU($from, $to);
echo "</tbody></table>";
}
$i = 0;
function trimSKU($from, $to){
$collection = Mage::getModel('catalog/product')->getCollection();
foreach($collection as $product){
$i++;
if($i > $to || $i < $from) { continue; };
$sku = trim($product->getSku());
if($product->getId()){
$product->setSku($sku)->save();
echo "<tr><td>". $product->getId()."</td><td>".$product->getSKU()."</td></tr>";
}
}
}
?>
When you run the file, it will give you a form like below.
Add Product from as the starting point like 1
Add Product to as Ending point like 100
Click on Trim button. It will trim 1 to 100 products. Once these products gets trimmed, start with values 101 to 200.
It may happen that due to server limitations, the script may not run well. With this file you can update product SKUs in chunks. I will suggest you to keep chunks of 100 products at one time.
UPDATE
I have checked the script execution time and found that if you change your indexes mode to Manual Update the script will run much faster.
Execution time with indexes "Update on Save" : 208.79583716393
Execution time with indexes "Manual Update" : 152.2172369957
answered Feb 27, 2017 at 4:25
Via direct SQL with simple output messages. Extremely quick. Set $path to local.xml correctly. Remember to re-index after running.
<?php
global $conn;
global $db;
// Set path to local.xml correctly
$path = "../app/etc/local.xml";
$count = 0;
$xml = simplexml_load_file($path, NULL, LIBXML_NOCDATA);
if(is_object($xml)):
createDatabaseConnection($xml);//Create Connection
$data = _getSKUs();
foreach ($data as $productId=>$sku):
$skuTrim = trim($sku);
if ($sku == $skuTrim):
continue;
endif;
$count ++;
if (_updateSku($productId,$skuTrim)):
echo "<p>ID=>". $productId." SKU=> ".$sku." White Space removed</p>";
else:
echo "<p>ERROR ID=>". $productId." SKU=> ".$sku." NOT UPDATED</p>";
endif;
endforeach;
if ($count == 0):
echo "<p>NO SKUs to TRIM</p>";
endif;
endif;
$conn->close(); // Close database connection
/***************** UTILITY FUNCTIONS ********************/
function createDatabaseConnection($xml) {
global $conn;
global $db;
$db['host'] = $xml->global->resources->default_setup->connection->host;
$db['name'] = $xml->global->resources->default_setup->connection->dbname;
$db['user'] = $xml->global->resources->default_setup->connection->username;
$db['pass'] = $xml->global->resources->default_setup->connection->password;
$db['pref'] = $xml->global->resources->db->table_prefix;
$conn = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);
// check connection
if ($conn->connect_error):
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
endif;
}
function _getSKUs(){ //Get ALL product skus
global $conn;
global $db;
$sql = "SELECT sku,entity_id FROM ".$db['pref']."catalog_product_entity";
if ($result = $conn->query($sql)):
if ($result->num_rows):
while ($row = $result->fetch_assoc()):
$data[$row['entity_id']]= $row['sku']; // Load data into array
endwhile;
endif;
endif;
return $data;
}
function _updateSku($productId,$sku){
global $conn;
global $db;
$sql = 'UPDATE '.$db['pref'].'catalog_product_entity SET sku = "'.$sku.'" WHERE entity_id = '.$productId;
if ($result = $conn->query($sql)):
return true;
endif;
return false;
}
?>
