As per this thread on reddit, here’s my script for importing products into magento via an excel file. Some of it, specifically the attributes and attribute sets are hardcoded in places, but other than that it should be generally applicable to most stores. It makes use of the PHPExcel library for loading the data from an xls or xlsx file.
<?php
require_once
require_once realpath(dirname(__FILE__)).'/../../include/PHPExcel.php';
require_once realpath(dirname(__FILE__)).'/../../include/price_rules.php';
function set_price($id){
return;
$multipliers = getPriceRules();
//load product
$product = Mage::getModel('catalog/product')
->setStoreId(1)
->load($id);
//$product = $products->getFirstItem();
echo "got product ".$product->getSku()."\n";
$original_price = $product->getPrice();
echo "price is ".$original_price."\n";
$original_special_price = $product->getSpecialPrice();
echo "special price is ".$original_special_price."\n";
foreach($multipliers as $store_id=>$multiplier){
$new_price = round($original_price*$multiplier,0);
$new_special_price = round($original_special_price*$multiplier,0);
$product->setStoreId($store_id);
echo "store id set to ".$store_id."\n";
$product->setPrice($new_price);
echo "Price set to ".$new_price."\n";
if ($new_special_price > 0){
$product->setSpecialPrice($new_special_price);
echo "Special Price set to ".$new_special_price."\n";
} else {
$product->setSpecialPrice(null);
}
$product->save();
echo "product saved\n";
}
}
function getAttributeOptionValue($arg_attribute, $arg_value) {
echo "here\n";
$attribute_model = Mage::getModel('eav/entity_attribute');
$attribute_options_model= Mage::getModel('eav/entity_attribute_source_table') ;
$attribute_code = $attribute_model->getIdByCode('catalog_product', $arg_attribute);
echo "[".$attribute_code."]\n";
//die();
$attribute = $attribute_model->load($attribute_code);
$attribute_table = $attribute_options_model->setAttribute($attribute);
$options = $attribute_options_model->getAllOptions(false);
foreach($options as $option) {
echo $option['label']."\n";
if ($option['label'] == $arg_value) {
return $option['value']; }
}
return false;
}
function getColourID($colour){
$_product = Mage::getModel('catalog/product');
$attr = $_product->getResource()->getAttribute("colour");
if ($attr->usesSource()) {
return $attr->getSource()->getOptionId($colour);
}
return 0;
}
function createSimple($data,$visible = false){
echo "creating simple\n";
print_r($data);
echo "\n";
if ($visible == false)
$visibility = Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE;
else
$visibility = Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH;
echo "[".strtoupper($data['productType'])."]";
switch (strtoupper($data['productType'])){ /*this bit is probably specific to our store */
case "CLOTHING":
$configurable_attribute = "clothes_size";
$attribute_set = 12;
$attr_id = 135; //clothes_size
break;
case "SHOES":
$configurable_attribute = "shoe_size";
$attribute_set = 9;
$attr_id = 128;
break;
case "ACCESSORIES":
case "ACCESSORY":
$configurable_attribute = NULL;
$attribute_set = 4;
break;
}
echo $configurable_attribute;
if (strtoupper($data['size']) == "OS") /* on the fly data sanitisation ftw */
$data['size'] = "One Size";
if ($configurable_attribute !== NULL)
$configurableAttributeOptionId = getAttributeOptionValue($configurable_attribute, $data['size']);
echo "[".$configurableAttributeOptionId."]";
$sProduct = Mage::getModel('catalog/product');
//echo "[".getColourID($data['colour'])."]";
$sProduct ->setTypeId(Mage_Catalog_Model_Product_Type::TYPE_SIMPLE)
->setWebsiteIds(array(1))
->setStatus(Mage_Catalog_Model_Product_Status::STATUS_DISABLED)
->setVisibility($visibility)
->setTaxClassId(5)
->setAttributeSetId($attribute_set)
//->setCategoryIds($magento_categories) // Populated further up the script
->setSku($data['sku']) // $main_product_data is an array created as part of a wider foreach loop, which this code is inside of
->setName($data['sku'])
->setShortDescription($data['description'])
->setDescription($data['description'])
//->setDescription($main_product_data['long_description'])
->setCost(sprintf("%0.2f", $data['cost']))
->setColour(getColourID(ucwords($data['colour']))) /* some of these attributes are site specific */
->setSupplier($data['supplier'])
->setSupplyCode($data['supplyCode'])
->setPrice(sprintf("%0.2f", $data['price']))
->setMsrp(sprintf("%0.2f", $data['price']))
;
if ($configurable_attribute !== NULL)
$sProduct->setData($configurable_attribute, $configurableAttributeOptionId);
$sProduct->setStockData(
array(
'is_in_stock' => 0,
'qty' => 0 )
);
$sProduct->save();
//die();
return array(
"id" => $sProduct->getId(),
"price" => $sProduct->getPrice(),
"attr_code" => $configurable_attribute,
"attr_id" => $attr_id,
"value" => $configurableAttributeOptionId,
"label" => $attr_value
);
}
function createProduct($data){
//echo "[".$data['price']."]";
if (!stristr($data['size'],'-'))
return createSimple($data,true);
$sizes = explode("-",$data['size']);
$simpleProducts = array();
foreach($sizes as $size){
$simpleArray = $data;
if (strtoupper($size) == "OS")
$size = "One Size";
$simpleArray['size'] = $size;
$simpleArray['sku'] = $simpleArray['sku'] ."-".str_replace("/","",$size);
$simpleProducts[] = createSimple($simpleArray);
}
switch (strtoupper($data['productType'])){ /* this determines how simples are associated to configs */
case "CLOTHING":
$configurable_attribute = "clothes_size";
$attribute_set = 12;
$attr_id = 135; //clothes_size
break;
case "SHOES":
$configurable_attribute = "shoe_size";
$attribute_set = 9;
$attr_id = 128;
break;
}
echo "Configurable attribute: ".$configurable_attribute."\n";
$cProduct = Mage::getModel('catalog/product');
$cProduct ->setTypeId(Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE)
->setTaxClassId(5)
->setVisibility(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
->setStatus(Mage_Catalog_Model_Product_Status::STATUS_DISABLED)
->setWebsiteIds(array(1))
//->setCategoryIds($magento_categories)
->setAttributeSetId($attribute_set) // You can determine this another way if you need to.
->setSku($data['sku'])
->setName($data['sku'])
->setShortDescription($data['description'])
->setDescription($data['description'])
->setCost(sprintf("%0.2f", $data['cost']))
->setColour(getColourID(ucwords($data['colour'])))
->setSupplier($data['supplier'])
->setSupplyCode($data['supplyCode'])
->setPrice(sprintf("%0.2f", $data['price']))
->setMsrp(sprintf("%0.2f", $data['price']))
->setReportingCategory($data['category'])
;
print_r($data);
echo "\n";
$cProduct->setCanSaveConfigurableAttributes(true);
$cProduct->setCanSaveCustomOptions(true);
$cProductTypeInstance = $cProduct->getTypeInstance();
// This array is is an array of attribute ID's which the configurable product swings around (i.e; where you say when you
// create a configurable product in the admin area what attributes to use as options)
// $_attributeIds is an array which maps the attribute(s) used for configuration so their numerical counterparts.
// (there's probably a better way of doing this, but i was lazy, and it saved extra db calls);
// $_attributeIds = array("size" => 999, "color", => 1000, "material" => 1001); // etc..
$cProductTypeInstance->setUsedProductAttributeIds(array($attr_id));
$attributes_array = $cProductTypeInstance->getConfigurableAttributesAsArray();
foreach($attributes_array as $key => $attribute_array) {
$attributes_array[$key]['use_default'] = 1;
$attributes_array[$key]['position'] = 0;
if (isset($attribute_array['frontend_label'])) {
$attributes_array[$key]['label'] = $attribute_array['frontend_label'];
} else {
$attributes_array[$key]['label'] = $attribute_array['attribute_code'];
}
}
// Add it back to the configurable product..
$cProduct->setConfigurableAttributesData($attributes_array);
print_r($attributes_array);
echo "\n";
// Remember that $simpleProducts array we created earlier? Now we need that data..
$dataArray = array();
foreach ($simpleProducts as $simpleArray) {
$dataArray[$simpleArray['id']] = array();
foreach ($attributes_array as $attrArray) {
array_push( $dataArray[$simpleArray['id']],
array( "attribute_id" => $simpleArray['attr_id'],
"label" => $simpleArray['label'],
"is_percent" => false,
"pricing_value" => $simpleArray['price']
)
);
}
}
// This tells Magento to associate the given simple products to this configurable product..
$cProduct->setConfigurableProductsData($dataArray);
$cProduct->setStockData(
array( 'use_config_manage_stock' => 1,
'is_in_stock' => 1,
'is_salable' => 1
)
);
$cProduct->save();
return array(
"id" => $cProduct->getId(),
"price" => $cProduct->getPrice(),
);
}
$uploadfile = $argv[1];
$recipient = $argv[2];
$output = "";
$objPHPExcel = PHPExcel_IOFactory::load($uploadfile);
$objPHPExcel->setActiveSheetIndex(0);
$data = $objPHPExcel->getActiveSheet()->toArray();
/*
* [0] => SKU //columns in spreadsheet
[1] => Date
[2] => New/Rebuy
[3] => PREFIXES
[4] => Product Type
[5] => Colour
[6] => Supplier
[7] => Supplier Code
[8] => SKU
[9] => Description
[10] => Sizes
[11] => Magento
[12] => Qty
[13] => Unit Price
*
*
*
*/
$product_type_col = 4;
$sku_col = 8;
$description_col = 9;
$cost_col = 13;
$colour_col = 5;
$size_col = 10;
$supplier_col = 6;
$supply_code_col = 7;
$price_col = 15;
$category_col = 1;
$i = 0;
$out = "";
$created_products = array();
foreach($data as $line){
//skip headings
$i++;
if ($i == 1)
continue;
if ($line[$sku_col] == "")
continue;
$line[$cost_col] = str_replace("£","",$line[$cost_col]);
$line[$price_col] = str_replace("£","",$line[$price_col]);
$line_data = array(
'productType' => $line[$product_type_col],
'sku' => $line[$sku_col],
'description' => $line[$description_col],
'cost' => $line[$cost_col],
'colour' => $line[$colour_col],
'size' => $line[$size_col],
'supplier' => $line[$supplier_col],
'supplyCode' => $line[$supply_code_col],
'price' => $line[$price_col],
'category' => $line[$category_col],
);
$prod = Mage::getModel('catalog/product')->loadByAttribute('sku',$line_data['sku']);
//var_dump($prod);
if (!$prod){
echo "creating ".$line_data['sku']." \n";
$out .= "creating ".$line_data['sku']." \n";
$created = createProduct($line_data);
$created_products[] = $created['id'];
}
}
$out .= "Done";