Create CSV list of all products & their categories

Question

can somebody suggest me a way or a script to export a csv file with all product SKUs & the categories those SKUs are associated to?

I've tried several things... but nothing seems to work. Magento's product export isn't working for me.. It gives me errors.

1. I've tried the Aten's script [source code] but it prints the categories in json format..

2. so i've also tried this:

   define('MAGENTO', realpath(dirname(__FILE__)));
   require_once MAGENTO . '/app/Mage.php';
   Mage::app();
   
   $category = Mage::getModel('catalog/category')->load($categoryId);
   
   $ids = $category->getCollection()->getAllIds();
   
   if ($ids) {
       $file = "var/import/catwithid.csv";
       file_put_contents($file,"category_ids, sku" . "\n");
       foreach ( $ids as $id ) {
         $string = $id . ', ' . $category->load($id) . "\n";
           file_put_contents($file, $string, FILE_APPEND);
       }
   }
   

and this

define('MAGENTOROOT', realpath(dirname(__FILE__)));
require_once(MAGENTOROOT.'/app/Mage.php');
umask(0); 
Mage::app('default');

$products = Mage::getModel("catalog/product")->getCollection();
$products->addAttributeToFilter('status', 1);
$products->addAttributeToFilter('visibility', 4);
$products->load();

$fp = fopen('exports.csv', 'w');
$csvHeader = array("sku", "category_ids");
fputcsv( $fp, $csvHeader, $delimiter = ", ");

foreach( $products as $_product ) {
$sku = $_product->getData('sku');
$product = $_product->load($sku);
$product_row = array($sku);
fputcsv( $fp, $product_row, $delimiter = ", ");
}

I've also tried to merge them together but with no luck! :/

please help me!!

Answer 1

This should work:

<?php
error_reporting(E_ALL | E_STRICT);
define('MAGENTO_ROOT', getcwd());
$mageFilename = MAGENTO_ROOT . '/app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
Mage::app();
$products = Mage::getModel("catalog/product")->getCollection();
$products->addAttributeToSelect('category_ids');
$products->addAttributeToFilter('status', 1);//optional for only enabled products
$products->addAttributeToFilter('visibility', 4);//optional for products only visible in catalog and search
$fp = fopen('exports.csv', 'w');
$csvHeader = array("sku", "category_ids");
fputcsv( $fp, $csvHeader,",");
foreach ($products as $product){
    $sku = $product->getSku();
    $categoryIds = implode('|', $product->getCategoryIds());//change the category separator if needed
    fputcsv($fp, array($sku, $categoryIds), ",");
}
fclose($fp);

put the script in a file on the same level as index.php and run it in the browser or command line.