Programatically Import Downloadable Product from Excel Spreadsheet

A recent assignment, required to migrate current data, to magento store.

All Categories, Products, Users, Purchases, etc. needed to be moved with a single script, by reading data from a Excel workbook(admin used to manage data with excel sheets).

the store dealt with products of “downloadable” kind.

I used the following Excel Parser, to read the data from the spreadsheet.

the sheet containing products has the following columns and in the order as specified:

  • category id
  • name
  • description
  • sku
  • price
  • FileName
  • MetaTitle
  • MetaDescription
  • MetaKeywords

Use the code below, to iterate through the rows of product and insert them to magento store

<?php
set_time_limit(0); //not to let the script timeout, due to long list of products
define('MAGENTO', "path/to/your/magento/root");
require_once MAGENTO . '/app/Mage.php';
//optional, to check errors on script
error_reporting(E_ALL);
ini_set('display_errors', '1');
umask(0);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); //set current store to admin panel/backend
//find the store id, for the store, the product belongs to
$storeId=Mage::app()->getStore('default')->getId(); //check the store id, in my case, it is 'default'
require_once 'reader.php'; //path/to/excel_reader_file/from/above/mentioned/library
$filename="data.xls"; //replace it with the full path to the excel(.xls) file, which would be read for information
//initialize the class
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($filename);
//assuming the first sheet of the file contains the info for products
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
try {
$product = Mage::getModel('catalog/product');
$product->setStoreId($storeId);
$product->setWebsiteIds(array(
Mage::app()->getStore($storeId)->getWebsiteId()
));
$product->setAttributeSetId($product->getDefaultAttributeSetId());
$product->setHasOptions(4);
$product->setTypeId('downloadable');
$product->setSku($data->sheets[0]['cells'][$i][4]);
$product->setPrice((float)$data->sheets[0]['cells'][$i][5]);
$product->setStatus(1);
$product->setVisibility(4);
$product->setTaxClassId(0);
$product->setLinksPurchasedSeparately(0);
$product->setEnableGooglecheckout(0);
$product->setIsImported(0);
$product->setLinksExist(true);
$product->setDescription($data->sheets[0]['cells'][$i][3]);
$product->setShortDescription($data->sheets[0]['cells'][$i][8]); //added, meta description to 'short description' field, you can change this value
$product->setMetaKeyword($data->sheets[0]['cells'][$i][9]);
$product->setCustomLayoutUpdate(NULL);
$product->setName($data->sheets[0]['cells'][$i][2]);
$product->setMetaTitle($data->sheets[0]['cells'][$i][7]);
$product->setMetaDescription($data->sheets[0]['cells'][$i][8]);
$product->setLinksTitle("Download");
$downloadData=array();
$downloadData['link'][0]=array(
'is_delete' => '',
'link_id' => '0',
'title' => 'Download',
'price' => '',
'number_of_downloads' => '0',
'is_shareable' => '2',
'sample' => array(
'file' => '[]',
'type' => '',
'url' => ''
),
'file' => '[]',
'type' => 'url',
'link_url' => $data->sheets[0]['cells'][$i][6],
'sort_order' => ''
);
$product->setDownloadableData($downloadData);
$product->setStockData(array(
'use_config_manage_stock' => 1,
'use_config_min_sale_qty' => 1,
'use_config_max_sale_qty' => 1
));
$product->setCategoryIds(
array(
$data->sheets[0]['cells'][$i][1] //first column, has the category id
)
);
$product->save();
echo $product->getId();
} catch (Exception $e) {
echo "Exception : $e->getMessage()";
}
}
?>

I have specified minimum attributes, which are required for successful insertion.

 

Advertisements

2 thoughts on “Programatically Import Downloadable Product from Excel Spreadsheet

  1. Thanks a lot, this is a very nice post, I will try it one day. For now the lack of time is forcing me to stick to the xml import/export downloadable product option. However I have not encountered any information about importing/exporting the download links through xml or csv. Here your download information is stored inside the “$downloadData” array, but how could this be rendered in a xml import file?

    • I guess you currently use SimpleXML or other similar library to parse the data from XML file. you can add multiple tags to define the download data.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s