Magento: how to create a super fast catalog export for Google Merchant

So you need a script to export your catalog products into a CSV file to import it on Google Merchant. There is a lot of scripts on the net to do this, but the problem with these scripts is, as usual, speed performance.

If you have a big/huge catalog you will experience issue on speed, I had about 30k catalog products with a lot of categories and the old script based upon Magento collections took about 20 minutes to finish and a lot of system resources.

Now with this new script based upon direct SQL queries I can export all the catalog in about 2 minutes!

What you need to start? Basically you need to have flat catalog enabled because SQL select  is on that table (catalog_product_flat_1), so don’t try it if you hadn’t flat catalog enabled, it won’t work.

Then, you need to customize it to get the right things, it’s a very simple script and it doesn’t relay on stock items, I presume you have every products in stock.

Also the prices must have same tax, so if you have different taxes you need to add function to get right prices.

Hope this can help you:

define('SAVE_FEED_LOCATION','google_feed.csv');
set_time_limit(0);
require_once 'app/Mage.php';
umask( 0 );
Mage::app( "default" );

Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

try {
$handle = fopen(SAVE_FEED_LOCATION, 'w');
$heading = array('id','mpn','ean','title','google_product_category','description','link','image_link','price','brand','product_type','condition', 'availability');
$feed_line= implode("t", $heading)."rn";
fwrite($handle, $feed_line);

$resource = Mage::getSingleton('core/resource');

$readConnection = $resource->getConnection('core_read');

$query = "SELECT * FROM catalog_product_flat_1 WHERE visibility=4 AND type_id='{$type}'";

$tax = "1.21"; // Insert your tax product
$currency = "EUR";

$products = $readConnection->fetchAll($query);

foreach($products as $product) {
$product_data = array();
$product_data['sku'] = $product['sku'];
$product_data['mpn'] = $product['sku'];
$product_data['ean'] = $product['sku'];
$product_data['title'] = ucfirst(strtolower($product['name']));
$product_data['google_product_category'] = 'INSERT YOUR DEFAULT CATEGORY';

if ($product['description'] != "")
$product_data['description'] = Mage::helper('core/string')->truncate(str_replace('“', '"', str_replace('”', '"', iconv("UTF-8","UTF-8//IGNORE", strip_tags($product['description'])))), 8000); // removes most the utf8 errors (doesnt like row if empty too!)
else
$product_data['description'] = "Description not available";

$product_data['deeplink'] = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA).$product['url_path']; // thanks to hamish for this
$product_data['image_link'] = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA).'catalog/product'.$product['small_image'];

if($product['special_price'] != NULL){ // update to check for special price or discount
$product_data['price'] = number_format(($product['special_price']*$tax), 2, ",", "") . " $currency";
} else {
$product_data['price'] = number_format(($product['price']*$tax), 2, ",", "") . " $currency";
}

$product_data['brand'] = "INSERT YOUR BRAND";
$product_data['product_type'] = "";
$product_data['condition'] = "new";
$product_data['availability'] = "in stock";

$i = 1;

$queryCats = "SELECT name FROM catalog_category_product JOIN catalog_category_flat_store_1 ON catalog_category_product.category_id = catalog_category_flat_store_1.entity_id WHERE product_id={$product['entity_id']}";

$cats = $readConnection->fetchAll($queryCats);
$numCat = count($cats);
foreach($cats as $_category){
if ($i == $numCat)
$product_data['product_type'] .= $_category['name'];
else
$product_data['product_type'] .= $_category['name'] . " > ";
$i++;
}

foreach($product_data as $k=>$val){
$bad=array('"',"rn","n","r","t");
$good=array(""," "," "," ","");
$product_data[$k] = '"'.str_replace($bad,$good,$val).'"';
}

$feed_line = implode("t", $product_data)."rn";
fwrite($handle, $feed_line);
fflush($handle);

}

fclose($handle);

}
catch(Exception $e)
{
die($e->getMessage());
}

Via Janelle



Leave a Comment