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)."\r\n";
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('"',"\r\n","\n","\r","\t");
$good=array(""," "," "," ","");
$product_data[$k] = '"'.str_replace($bad,$good,$val).'"';
}

$feed_line = implode("\t", $product_data)."\r\n";
fwrite($handle, $feed_line);
fflush($handle);

}

fclose($handle);

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

Via Janelle

Tags: , , , , , , , ,

Post simili:

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

10 Responses to “Magento: how to create a super fast catalog export for Google Merchant”
  1. Matt Rhys-Davies
    13:41 on January 14th, 2013

    Great stuff – thanks for the tutorial Michele, most helpful.

  2. Dream-protect.fr
    13:35 on January 12th, 2013

    Are you still around ? i need some help with the plugin :(

  3. ItalyMall
    10:09 on November 22nd, 2012

    Also for me.

  4. Business broker
    13:14 on November 6th, 2012

    thank to share this comment i will use this code may be it will difficult for me

  5. Des Moine's Rock In Prevention Site
    21:32 on September 29th, 2012

    I enjoy, cause I discovered exactly what I used to be looking for. You have ended my four day lengthy hunt! God Bless you man. Have a great day. Bye

  6. canton fair 2012
    10:22 on September 18th, 2012

    So much code, I must watch it slowly to understand.

  7. Tim
    12:39 on August 21st, 2012

    I have tried Magento, but for me it’s to complicated.

  8. Catering Bucuresti
    17:08 on August 15th, 2012

    Using Magento is difficult for all of us, but using your file is more easy to export those files, thanks

  9. Andy
    17:09 on August 4th, 2012

    Thanks a lot for sharing this code, magento seems so complex to me, i always have a hard time adding a functionality to it :(

  10. Magento webshop
    15:46 on July 3rd, 2012

    Hi Michele,

    Thanks for this useful info!

    Regards from Holland!

Leave a comment

(required)

(required)


*

Categories