How do I have to import products in Magento using a direct connection to the database

Question

Hi guys,

I have about 50.000 of records to import in a Magento store. What I have already tested: The file is about 50 MB.

• Splitted files
• API
• Magento Classes

Splitting the file doesn't improve the speed of the importing of the products. Api are very slow. Magento Classes are slow.

This is a snipped of code using the Magento Classes:

// Build the product
$product->setIsMassupdate(true)
        ->setExcludeUrlRewrite(true)
        ->setManufacturer($this->addManufacturers(utf8_encode($record[4])))
        ->setSku($record[3])
        ->setAttributeSetId($this->attribute_set)# 9 is for default
        ->setTypeId(Mage_Catalog_Model_Product_Type::TYPE_SIMPLE)
        ->setName(utf8_encode($record[5]))
        ->setCategoryIds($this->getCategories(array($record[0], $record[1], $record[2]))) # some cat id's,
        ->setWebsiteIDs(array(1)) # Website id, 1 is default
        ->setDescription(utf8_encode($record[6]))
        ->setShortDescription($this->shortText(utf8_encode($record[6]), 150))
        ->setPrice($price) # Set some price
        ->setSpecialPrice($special_price)
        ->setWeight($record[12])
        ->setStatus( Mage_Catalog_Model_Product_Status::STATUS_ENABLED )
        ->setVisibility(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
        ->setTaxClassId(2)     // default tax class
        ->setPixmaniaimg($record[10])
        ->setStockData(array('is_in_stock' => $inStock, 'qty' => $qty))
        ->setCreatedAt(strtotime('now'));

$product->save();     
$ID = is_numeric($productID) ? $productID : $product->getId(); 

So the above method is correct but it spends about 5 hours in order to insert only 2300 records!!

Which are the simple SQL inserts that I have to execute in the Magento DB in order to add a new product?

Answer 1

It's very hard to create products using raw SQL queries, because Magento uses EAV pattern for storing products.

Answer 2

I strongly recommend that you avoid writing raw SQL at all costs, you will almost certainly spend days and days writing to map the attribute IDs and probably get it wrong. It will also bypass all the important indexing and other system updates that Magento relies on.

If speed is your issue, I suggest that you consider uRapidFlow from Unirgy. Usual disclaimers apply, I have no affiliation with Unirgy, but my observations has been that the quality of this work is excellent.

HTH, JD

Answer 3

Occasionally I've noticed bulk inserts that work by first creating a template model...

$blankProduct = Mage::getModel('catalog/product');

...then avoid the creation of the model for each record...

$newProduct = clone $blankProduct;
$newProduct->setIsMassupdate(true)
    ...
$newProduct->save();

It's slightly more efficient but probably not enough to get that massive import to a reasonable time.