Magento export categories -> import with magmi

Question

we need to change the categories of some products and it is easier for us to do this with a csv file. Is there a way to export the categories in magmi format?

sku,categories
"abc","cat1/cat2;;cat5/cat6/cat1"
"bgb","cat1/cat2"

or is there maybe a small tool to manage the products in categories?

Edit: this is the current code which is displaying the category names. But I am trying to display the category path like this:

FirstCat/Tools/Screwdriver

But it is displaying like this:

FirstCat/Screwdriver/Tools

so the categoryid is not sorted.

<?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->addAttributeToSelect('sku');
$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();
  $i = 2;
  $len = count($product->getCategoryIds());
  $str = "";
  foreach ($product->getCategoryIds() as $id){
    $category = Mage::getModel('catalog/category')->load($id);
    $name = $category->getName();
    $str .= $name;
    if($i <= $len) {
      $str = $str .=  "/";
    }
    $i++;
  }

  fputcsv($fp, array($sku, $str), ",");

}
fclose($fp);

imso077 imso077 · Accepted Answer · 2015-09-02T14:56:24

I use this code to export out categories. You can try to run it, don't forget to modify your path to your mage/app.php:

 <?php

require_once('../yourpath/app/Mage.php');
Mage::app('admin');

Mage::app()->getStore()->setId(Mage_Core_Model_App::ADMIN_STORE_ID);
Mage::register("isSecureArea", true);

function saveData($file, $data) {
    $fh = fopen($file, 'w');
    foreach ($data as $dataRow) {
        fputcsva($fh, $dataRow);
    }
    fclose($fh);
    return $this;
}

function fputcsva(&$handle, $fields = array(), $delimiter = ',', $enclosure = '"') {
    $str = '';
    $escape_char = '\\';
    foreach ($fields as $value) {
        if (strpos($value, $delimiter) !== false ||
            strpos($value, $enclosure) !== false ||
            strpos($value, "\n") !== false ||
            strpos($value, "\r") !== false ||
            strpos($value, "\t") !== false ||
            strpos($value, ' ') !== false) {
            $str2 = $enclosure;
            $escaped = 0;
            $len = strlen($value);
            for ($i = 0; $i < $len; $i++) {
                if ($value[$i] == $escape_char) {
                    $escaped = 1;
                } else if (!$escaped && $value[$i] == $enclosure) {
                    $str2 .= $enclosure;
                } else {
                    $escaped = 0;
                }
                $str2 .= $value[$i];
            }
            $str2 .= $enclosure;
            $str .= $str2 . $delimiter;
        } else {
            if (strlen($value)):
                $str .= $enclosure . $value . $enclosure . $delimiter;
            else:
                $str .= $value . $delimiter;
            endif;
        }
    }
    $str = substr($str, 0, -1);
    $str .= "\n";
    return fwrite($handle, $str);
}

function geturl($connect, $value){
    $id=  $value;
    if ($id) {
        $sql = "SELECT value FROM magento.catalog_category_entity_url_key where entity_id=$id;";
        $result = $connect->query($sql)->fetchObject();
    } else {
        return "";
    }
    return $result->value;
}

$modifiedarray = array();
$configpricearray=array();
$coreResource = Mage::getSingleton('core/resource');
$connect = $coreResource->getConnection('core_write');


$sql = "SELECT entity_id FROM catalog_category_entity;";

$category_row = $connect->query($sql);

$i=1;
$problematiccats=array(394,395,397,398);
$problematiccolumn=array('path','parent_id','level', 'position');
while ($row = $category_row->fetch()) {
    $catid=$row['entity_id'];
    echo "Category id: ".$catid.PHP_EOL;
    if (in_array($catid,$problematiccats)): continue; endif;
    $catsql="SELECT
        ce.entity_id,
        ce.parent_id,
        ce.path,
        ce.level,
        ce.position,
        ce.children_count,
        ea.attribute_id,
        ea.attribute_code,
        CASE ea.backend_type
           WHEN 'varchar' THEN ce_varchar.value
           WHEN 'int' THEN IFNULL (eav_option.value,ce_int.value)
           WHEN 'text' THEN ce_text.value
           WHEN 'decimal' THEN ce_decimal.value
           WHEN 'datetime' THEN ce_datetime.value
           ELSE ea.backend_type
        END AS value,
        ea.is_required AS required
    FROM catalog_category_entity AS ce
    LEFT JOIN eav_attribute AS ea
        ON ce.entity_type_id = ea.entity_type_id
    LEFT JOIN catalog_category_entity_varchar AS ce_varchar
        ON ce.entity_id = ce_varchar.entity_id
        AND ea.attribute_id = ce_varchar.attribute_id
        AND ea.backend_type = 'varchar'
    AND ce_varchar.store_id = 0
    LEFT JOIN catalog_category_entity_int AS ce_int
        ON ce.entity_id = ce_int.entity_id
        AND ea.attribute_id = ce_int.attribute_id
        AND ea.backend_type = 'int'
    AND ce_int.store_id = 0
    LEFT JOIN catalog_category_entity_text AS ce_text
        ON ce.entity_id = ce_text.entity_id
        AND ea.attribute_id = ce_text.attribute_id
        AND ea.backend_type = 'text'
    AND ce_text.store_id = 0
    LEFT JOIN catalog_category_entity_decimal AS ce_decimal
        ON ce.entity_id = ce_decimal.entity_id
        AND ea.attribute_id = ce_decimal.attribute_id
        AND ea.backend_type = 'decimal'
    AND ce_decimal.store_id = 0
    LEFT JOIN catalog_category_entity_datetime AS ce_datetime
        ON ce.entity_id = ce_datetime.entity_id
        AND ea.attribute_id = ce_datetime.attribute_id
        AND ea.backend_type = 'datetime'
    LEFT JOIN eav_attribute_option_value as eav_option
        ON eav_option.option_id=ce_int.value
        AND eav_option.store_id=0
    WHERE ce.entity_id = '$catid';";

    $category_info = $connect->query($catsql);
    $csvrow=array();
    if ($i==1): $csvrow['entity']='entity_id';$csvrow['parent']='parent_id'; $csvrow['path']='path';
        $csvrow['level']='level';
        $csvrow['position']='position';
        $csvrow['children_count']='children_count';endif;
    while ($catrow = $category_info->fetch()) {


        if (in_array($catrow['attribute_code'],$problematiccolumn)): continue; endif;
        if ($i==1):
            $csvrow[]=$catrow['attribute_code'];
        else:
            $csvrow['entity']=$catrow['entity_id'];
            $csvrow['parent']=$catrow['parent_id'];
            $csvrow['path']=$catrow['path'];
            $csvrow['level']=$catrow['level'];
            $csvrow['position']=$catrow['position'];
            $csvrow['children_count']=$catrow['children_count'];
            $csvrow[$catrow['attribute_code']]=($catrow['value']?$catrow['value']:"");
        if ($catrow['attribute_code']=="url_key"):
            if (strlen($catrow['url_key'])<3):
                $csvrow['url_key']=geturl($connect,$catid);
            endif;
          endif;
        endif;

    }

    $csv[]=$csvrow;
    $i++;
}

$file_path = 'categoryexport.csv';

try {
    saveData($file_path, $csv);
} catch (Exception $e) {
    echo "[ERROR] Creating sale report file: " . $e->getMessage() . PHP_EOL;
}

In this solution the db was'n in very well state, so the url needs to grabbed separately, you can remove that part if not necessary for you. I used this on EE 1.13.0.2 You can run it from the shell.

Magento export categories -> import with magmi

2 Answers