Magento: Import products through direct sql queries

You can import Products from exported custom CSV Sheet.
Please find the sample code below in custom PHP script:
<?php 
/*******Script used to import Products from exported custom CSV Sheet******/
//qty => 9
//is_in_stock => 10
//websites
require_once('app/Mage.php');
umask(0);

if (!Mage::isInstalled()) {
echo "Application is not installed yet, please complete install wizard first.";
exit;
}

// Only for urls // Don't remove this
$_SERVER['SCRIPT_NAME'] = str_replace(basename(__FILE__), 'index.php', $_SERVER['SCRIPT_NAME']);
$_SERVER['SCRIPT_FILENAME'] = str_replace(basename(__FILE__), 'index.php', $_SERVER['SCRIPT_FILENAME']);

Mage::app('admin')->setUseSessionInUrl(false);
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
error_reporting(E_ALL);

try {
Mage::getConfig()->init();
Mage::app();
} catch (Exception $e) {
Mage::printException($e);
}
ini_set('memory_limit', '1024M');
ini_set('max_execution_time', 0);

$readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');
$writeAdapter = Mage::getSingleton('core/resource')->getConnection('core_write');

$results = $readAdapter->fetchAll("SELECT ea.attribute_id, ea.attribute_code, eet.entity_type_id FROM `eav_entity_type` as eet
inner join `eav_attribute` as ea on ea.entity_type_id = eet.entity_type_id and
eet.entity_type_code like '%catalog_product%'");


$entityTypeId = $results[0]['entity_type_id'];
$attributeIdCodeArray = array();
foreach ($results as $result) {
$attributeIdCodeArray[$result['attribute_code']] = $result['attribute_id'];
}
//print_r($attributeIdCodeArray);
//exit;

// Gets the current store's id
$storeId = Mage::app()->getStore()->getStoreId();

/*===============for simple products=================*/
$fileToImport = "exported_products/exported_products.csv";
$csv = new Varien_File_Csv();
$csvDataToImport = $csv->getData($fileToImport);
$i = 0;
foreach ($csvDataToImport as $dataToImport) {
if (!$i) {
$headerColumns = array_flip($dataToImport);
//print_r($headerColumns);exit;
} else {
$attributeSetName = isset($headerColumns['attribute_set']) ?
$dataToImport[$headerColumns['attribute_set']] : 'Default';
$typeId = isset($headerColumns['type_id']) ?
trim($dataToImport[$headerColumns['type_id']]) : 'simple';
$sku = isset($headerColumns['sku']) ?
trim($dataToImport[$headerColumns['sku']]) : '';
$name = isset($headerColumns['name']) ?
trim($dataToImport[$headerColumns['name']]) : '';
$countryOfManufacture = isset($headerColumns['country_of_manufacture']) ?
trim($dataToImport[$headerColumns['country_of_manufacture']]) : '';
$isReturnable = isset($headerColumns['is_returnable']) ?
trim($dataToImport[$headerColumns['is_returnable']]) : '';
$msrpEnabled = isset($headerColumns['msrp_enabled']) ?
trim($dataToImport[$headerColumns['msrp_enabled']]) : '';
$msrpDisplayActualPriceType = isset($headerColumns['msrp_display_actual_price_type']) ?
trim($dataToImport[$headerColumns['msrp_display_actual_price_type']]) : '';
$metaTitle = isset($headerColumns['meta_title']) ?
trim($dataToImport[$headerColumns['meta_title']]) : '';
$metaDescription = isset($headerColumns['meta_description']) ?
trim($dataToImport[$headerColumns['meta_description']]) : '';
$image = isset($headerColumns['image']) ?
trim($dataToImport[$headerColumns['image']]) : '';
$smallImage = isset($headerColumns['small_image']) ?
trim($dataToImport[$headerColumns['small_image']]) : '';
$thumbnail = isset($headerColumns['thumbnail']) ?
trim($dataToImport[$headerColumns['thumbnail']]) : '';
$customDesign = isset($headerColumns['custom_design']) ?
trim($dataToImport[$headerColumns['custom_design']]) : '';
$pageLayout = isset($headerColumns['page_layout']) ?
trim($dataToImport[$headerColumns['page_layout']]) : '';
$optionsContainer = isset($headerColumns['options_container']) ?
trim($dataToImport[$headerColumns['options_container']]) : '';
$giftMessageAvailable = isset($headerColumns['gift_message_available']) ?
trim($dataToImport[$headerColumns['gift_message_available']]) : '';
$giftWrappingAvailable = isset($headerColumns['gift_wrapping_available']) ?
trim($headerColumns['gift_wrapping_available']) : '';
$imageLabel = isset($headerColumns['image_label']) ?
trim($dataToImport[$headerColumns['image_label']]) : '';
$smallImageLabel = isset($headerColumns['small_image_label']) ?
trim($dataToImport[$headerColumns['small_image_label']]) : '';
$thumbnailLabel = isset($headerColumns['thumbnail_label']) ?
trim($dataToImport[$headerColumns['thumbnail_label']]) : '';

$hasOptions = isset($headerColumns['has_options']) ? $dataToImport[$headerColumns['has_options']] : 0;
$requiredOptions = $hasOptions;
$attributeSet = $readAdapter->fetchAll("SELECT * FROM `eav_attribute_set` WHERE `attribute_set_name`
LIKE '%" . $attributeSetName . "%' and `entity_type_id` = $entityTypeId");
//$attributeSet = $attributeSet[0]['attribute_set_id'];
$attributeSet = trim($dataToImport[$headerColumns['attribute_set_id']]);
echo 'en_id----'.$newEntityId = trim($dataToImport[$headerColumns['entity_id']]);
$insertQuery = '';
// inserts to master table
$insertQuery .= "INSERT INTO `catalog_product_entity`(`entity_id`,`entity_type_id`, `attribute_set_id`, `type_id`, `sku`,
`has_options`, `required_options`, `created_at`, `updated_at`) values ($newEntityId, $entityTypeId,
$attributeSet, '" . $typeId . "', '" . $sku . "', $hasOptions, $requiredOptions, NOW(), NOW()); ";
//$writeAdapter->query($insertQuery);
//$insertQuery = '';
//$newEntityId = $writeAdapter->lastInsertId();

// insert all varchar attributes
$insertQuery .= "INSERT INTO `catalog_product_entity_varchar`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['name'] . ", $storeId, $newEntityId, '" . $name . "'),
($entityTypeId, " . $attributeIdCodeArray['country_of_manufacture'] . ", $storeId, $newEntityId,
'" . $countryOfManufacture . "'),
($entityTypeId, " . $attributeIdCodeArray['is_returnable'] . ", $storeId, $newEntityId, '" . $isReturnable . "'),
($entityTypeId, " . $attributeIdCodeArray['msrp_enabled'] . ", $storeId, $newEntityId, '" . $msrpEnabled . "'),
($entityTypeId, " . $attributeIdCodeArray['msrp_display_actual_price_type'] . ", $storeId, $newEntityId,
'" . $msrpDisplayActualPriceType . "'),
($entityTypeId, " . $attributeIdCodeArray['meta_title'] . ", $storeId, $newEntityId, '" . $metaTitle . "'),
($entityTypeId, " . $attributeIdCodeArray['meta_description'] . ", $storeId, $newEntityId, '" . $metaDescription . "'),
($entityTypeId, " . $attributeIdCodeArray['image'] . ", $storeId, $newEntityId, '" . $image . "'),
($entityTypeId, " . $attributeIdCodeArray['small_image'] . ", $storeId, $newEntityId, '" . $smallImage . "'),
($entityTypeId, " . $attributeIdCodeArray['thumbnail'] . ", $storeId, $newEntityId, '" . $thumbnail . "'),
($entityTypeId, " . $attributeIdCodeArray['custom_design'] . ", $storeId, $newEntityId, '" . $customDesign . "'),
($entityTypeId, " . $attributeIdCodeArray['page_layout'] . ", $storeId, $newEntityId, '" . $pageLayout . "'),
($entityTypeId, " . $attributeIdCodeArray['options_container'] . ", $storeId, $newEntityId, '" . $optionsContainer . "'),
($entityTypeId, " . $attributeIdCodeArray['gift_message_available'] . ", $storeId, $newEntityId,
'" . $giftMessageAvailable . "'),
($entityTypeId, " . $attributeIdCodeArray['gift_wrapping_available'] . ", $storeId, $newEntityId,
'" . $giftWrappingAvailable . "'),
($entityTypeId, " . $attributeIdCodeArray['image_label'] . ", $storeId, $newEntityId, '" . $imageLabel . "'),
($entityTypeId, " . $attributeIdCodeArray['small_image_label'] . ", $storeId, $newEntityId,
'" . $smallImageLabel . "'),
($entityTypeId, " . $attributeIdCodeArray['thumbnail_label'] . ", $storeId, $newEntityId,
'" . $thumbnailLabel . "'); ";

//echo $insertQuery;exit;
//$writeAdapter->query($insertQuery);

// prepare & insert all int attributes
$status = isset($headerColumns['status']) ?
trim($dataToImport[$headerColumns['status']]) : 0;

$color = isset($headerColumns['color']) ?
trim($dataToImport[$headerColumns['color']]) : '';

$size = isset($headerColumns['size']) ?
trim($dataToImport[$headerColumns['size']]) : '';

$visibility = isset($headerColumns['visibility']) ?
trim($dataToImport[$headerColumns['visibility']]) : 1;

$taxClassId = isset($headerColumns['tax_class_id']) ?
trim($dataToImport[$headerColumns['tax_class_id']]) : 0;

$isRecurring = isset($headerColumns['is_recurring']) ?
trim($dataToImport[$headerColumns['is_recurring']]) : 0;

$relatedTgtrPositionLimit = isset($headerColumns['related_tgtr_position_limit']) ?
trim($dataToImport[$headerColumns['related_tgtr_position_limit']]) : NULL;

$relatedTgtrPositionBehaviour = isset($headerColumns['related_tgtr_position_behavior']) ?
trim($dataToImport[$headerColumns['related_tgtr_position_behavior']]) : NULL;


$insertQuery .= "INSERT INTO `catalog_product_entity_int`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['status'] . ", $storeId, $newEntityId, $status),
($entityTypeId, " . $attributeIdCodeArray['color'] . ", $storeId, $newEntityId,
IF ('$color' = '', NULL, '$color')),
($entityTypeId, " . $attributeIdCodeArray['size'] . ", $storeId, $newEntityId,
IF ('$size' = '', NULL, '$size')),
($entityTypeId, " . $attributeIdCodeArray['visibility'] . ", $storeId, $newEntityId, $visibility),
($entityTypeId, " . $attributeIdCodeArray['tax_class_id'] . ", $storeId, $newEntityId,$taxClassId),
($entityTypeId, " . $attributeIdCodeArray['is_recurring'] . ", $storeId, $newEntityId, $isRecurring),
($entityTypeId, " . $attributeIdCodeArray['related_tgtr_position_limit'] . ", $storeId, $newEntityId,
IF ('$relatedTgtrPositionLimit' = '', NULL, '$relatedTgtrPositionLimit')),
($entityTypeId, " . $attributeIdCodeArray['related_tgtr_position_behavior'] . ", $storeId, $newEntityId,
IF ('$relatedTgtrPositionBehaviour' = '', NULL, '$relatedTgtrPositionBehaviour'));
";


//echo $insertQuery;exit;
//$writeAdapter->query($insertQuery);

// prepare & insert all datetime attributes

if (isset($headerColumns['news_from_date'])) {
$newsFromDate = trim($dataToImport[$headerColumns['news_from_date']]);
$newsFromDate = DateTime::createFromFormat('m/d/Y H:i', $newsFromDate);
$newsFromDate = $newsFromDate->format('Y-m-d H:i:s');
} else {
$newsFromDate = NULL;
}

if (isset($headerColumns['news_to_date'])) {
$newsToDate = trim($dataToImport[$headerColumns['news_to_date']]);
$newsToDate = DateTime::createFromFormat('m/d/Y H:i', $newsToDate)->format('Y-m-d H:i:s');
} else {
$newsToDate = NULL;
}


if (isset($headerColumns['special_from_date'])) {
$specialFromDate = trim($dataToImport[$headerColumns['special_from_date']]);
$specialFromDate = DateTime::createFromFormat('m/d/Y H:i', $specialFromDate)->format('Y-m-d H:i:s');
} else {
$specialFromDate = NULL;
}


if (isset($headerColumns['special_to_date'])) {
$specialToDate = trim($dataToImport[$headerColumns['special_to_date']]);
$specialToDate = DateTime::createFromFormat('m/d/Y H:i', $specialToDate)->format('Y-m-d H:i:s');
} else {
$specialToDate = NULL;
}


if (isset($headerColumns['custom_design_from'])) {
$customDesignFrom = trim($dataToImport[$headerColumns['custom_design_from']]);
$customDesignFrom = DateTime::createFromFormat('m/d/Y H:i', $customDesignFrom)->format('Y-m-d H:i:s');
} else {
$customDesignFrom = NULL;
}


if (isset($headerColumns['custom_design_to'])) {
$customDesignTo = trim($dataToImport[$headerColumns['custom_design_to']]);
$customDesignTo = DateTime::createFromFormat('m/d/Y H:i', $customDesignTo)->format('Y-m-d H:i:s');
} else {
$customDesignTo = NULL;
}

$insertQuery .= "INSERT INTO `catalog_product_entity_datetime`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['news_from_date'] . ", $storeId, $newEntityId,
IF ('$newsFromDate' = '', NULL, '" . $newsFromDate . "')),
($entityTypeId, " . $attributeIdCodeArray['news_to_date'] . ", $storeId, $newEntityId,
IF ('$newsToDate' = '', NULL, '" . $newsToDate . "')),
($entityTypeId, " . $attributeIdCodeArray['special_from_date'] . ", $storeId, $newEntityId,
IF ('$specialFromDate' = '', NULL, '" . $specialFromDate . "')),
($entityTypeId, " . $attributeIdCodeArray['special_to_date'] . ", $storeId, $newEntityId,
IF ('$specialToDate' = '', NULL, '" . $specialToDate . "')),
($entityTypeId, " . $attributeIdCodeArray['custom_design_from'] . ", $storeId, $newEntityId,
IF ('$customDesignFrom' = '', NULL, '" . $customDesignFrom . "')),
($entityTypeId, " . $attributeIdCodeArray['custom_design_to'] . ", $storeId, $newEntityId,
IF ('$customDesignTo' = '', NULL, '" . $customDesignTo . "'));
";

//echo $insertQuery;exit;
//$writeAdapter->query($insertQuery);

// prepare & insert all text attributes

$description = isset($headerColumns['description']) ?
trim($dataToImport[$headerColumns['description']]) : NULL;
$shortDescription = isset($headerColumns['short_description']) ?
trim($dataToImport[$headerColumns['short_description']]) : NULL;
$metaKeyword = isset($headerColumns['meta_keyword']) ?
trim($dataToImport[$headerColumns['meta_keyword']]) : NULL;
$customLayoutUpdate = isset($headerColumns['custom_layout_update']) ?
trim($dataToImport[$headerColumns['custom_layout_update']]) : NULL;


$insertQuery .= "INSERT INTO `catalog_product_entity_text`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['description'] . ", $storeId, $newEntityId,
IF ('$description' = '', NULL, '" . $description . "')),
($entityTypeId, " . $attributeIdCodeArray['short_description'] . ", $storeId, $newEntityId,
IF ('$shortDescription' = '', NULL, '" . $shortDescription . "')),
($entityTypeId, " . $attributeIdCodeArray['meta_keyword'] . ", $storeId, $newEntityId,
IF ('$metaKeyword' = '', NULL, '" . $metaKeyword . "')),
($entityTypeId, " . $attributeIdCodeArray['custom_layout_update'] . ", $storeId, $newEntityId,
IF ('$customLayoutUpdate' = '', NULL, '" . $customLayoutUpdate . "'));
";
//echo $insertQuery;exit;


// prepare & insert all decimal attributes

$weight = isset($headerColumns['weight']) ? trim($dataToImport[$headerColumns['weight']]) : NULL;
$price = isset($headerColumns['price']) ? trim($dataToImport[$headerColumns['price']]) : NULL;
$specialPrice = isset($headerColumns['special_price']) ? trim($dataToImport[$headerColumns['special_price']]) : NULL;
$msrp = isset($headerColumns['msrp']) ? trim($dataToImport[$headerColumns['msrp']]) : NULL;
$cost = isset($headerColumns['cost']) ? trim($dataToImport[$headerColumns['cost']]) : NULL;
$giftWrappingPrice = isset($headerColumns['gift_wrapping_price']) ?
trim($dataToImport[$headerColumns['gift_wrapping_price']]) : NULL;


$insertQuery .= " INSERT INTO `catalog_product_entity_decimal`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['weight'] . ", $storeId, $newEntityId,
IF ('$weight' = '', NULL, '$weight')),
($entityTypeId, " . $attributeIdCodeArray['price'] . ", $storeId, $newEntityId,
IF ('$price' = '', NULL, '$price')),
($entityTypeId, " . $attributeIdCodeArray['special_price'] . ", $storeId, $newEntityId,
IF ('$specialPrice' = '', NULL, '$specialPrice')),
($entityTypeId, " . $attributeIdCodeArray['msrp'] . ", $storeId, $newEntityId,
IF ('$msrp' = '', NULL, '$msrp')),
($entityTypeId, " . $attributeIdCodeArray['cost'] . ", $storeId, $newEntityId,
IF ('$cost' = '', NULL, '$cost')),
($entityTypeId, " . $attributeIdCodeArray['gift_wrapping_price'] . ", $storeId, $newEntityId,
IF ('$giftWrappingPrice' = '', NULL, '$giftWrappingPrice'));
";
//echo $insertQuery;exit;

// prepare & insert url_key attributes

/*$urlKey = isset($headerColumns['url_key']) ? trim($dataToImport[$headerColumns['url_key']]) : NULL;
$insertQuery .= " INSERT INTO `catalog_product_entity_url_key`(`entity_type_id`, `attribute_id`, `store_id`,
`entity_id`, `value`) values
($entityTypeId, " . $attributeIdCodeArray['url_key'] . ", $storeId, $newEntityId,
IF ('$urlKey' = '', NULL, '$urlKey'));
";*/
//echo $insertQuery;exit;

// prepare & insert category_ids attributes

$categoryIds = isset($headerColumns['category_ids']) ? trim($dataToImport[$headerColumns['category_ids']]) : NULL;
$categoryIds = explode(',', $categoryIds);
if (!empty($categoryIds)) {
foreach ($categoryIds as $categoryId) {
$insertQuery .= " INSERT INTO `catalog_category_product`(`category_id`, `product_id`, `position`) values
($categoryId, $newEntityId, 1); ";
}
}

// prepare & insert product websites attributes

$websiteIds = isset($headerColumns['websites']) ? trim($dataToImport[$headerColumns['websites']]) : NULL;
$websiteIds = explode(',', $websiteIds);
if (!empty($websiteIds)) {
foreach ($websiteIds as $websiteId) {
$insertQuery .= " INSERT INTO `catalog_product_website`(`product_id`, `website_id`) values
($newEntityId, $websiteId); ";
}
}

// prepare & insert product stock item details

$qty = isset($headerColumns['qty']) ? trim($dataToImport[$headerColumns['qty']]) : 0;
$insertQuery .= " INSERT INTO `cataloginventory_stock_item` (`product_id`, `stock_id`, `qty`, `min_qty`,
`use_config_min_qty`, `is_qty_decimal`, `backorders`, `use_config_backorders`, `min_sale_qty`,
`use_config_min_sale_qty`, `max_sale_qty`, `use_config_max_sale_qty`, `is_in_stock`, `low_stock_date`,
`notify_stock_qty`, `use_config_notify_stock_qty`, `manage_stock`, `use_config_manage_stock`,
`stock_status_changed_auto`, `use_config_qty_increments`, `qty_increments`, `use_config_enable_qty_inc`,
`enable_qty_increments`, `is_decimal_divided`) VALUES
('" . $newEntityId . "', 1, '" . $qty . "', '0.0000', 1, 0, 0, 1, '1.0000', 1, '0.0000', 1, 1, NULL, NULL, 1, 0, 1, 0, 0,
'0.0000', 1, 0, 0); ";

// prepare & insert product stock status details
$isInStock = isset($headerColumns['is_in_stock']) ? trim($dataToImport[$headerColumns['is_in_stock']]) : 0;
if (!empty($websiteIds)) {
foreach ($websiteIds as $websiteId) {
$insertQuery .= " INSERT INTO `cataloginventory_stock_status` (`product_id`, `website_id`, `stock_id`, `qty`,
`stock_status`) VALUES ($newEntityId, IF('$websiteId' = '',1,'$websiteId'), 1, '" . $qty . "', $isInStock); ";
}
}

$writeAdapter->query($insertQuery);

if ($typeId == 'configurable') {
// prepare & insert configurable product relation with simple
$childIds = isset($headerColumns['child_ids']) ? trim($dataToImport[$headerColumns['child_ids']]) : NULL;
$childIds = explode(',', $childIds);
if (!empty($childIds)) {
$insertQueryRelation = '';
foreach ($childIds as $childId) {
$insertQueryRelation .= " INSERT INTO `catalog_product_relation` (`parent_id`, `child_id`)
VALUES ($newEntityId, $childId);
INSERT INTO `catalog_product_super_link` (`product_id`, `parent_id`) VALUES
($childId, $newEntityId); ";
}
}
//echo $insertQueryRelation;exit;
$writeAdapter->query($insertQueryRelation);

// prepare & insert configurable product attribute details
$configurableData = json_decode(isset($headerColumns['configurable_data']) ? trim($dataToImport[$headerColumns['configurable_data']]) : 0);
$insertQuery = '';
$lastInsertId = 0;
if (!empty($configurableData)) {
foreach ($configurableData as $confData) {
$insertQueryOuter = '';
$insertQueryOuter .= " INSERT INTO `catalog_product_super_attribute` (`product_id`,
`attribute_id`, `position`) VALUES
($newEntityId, " . $confData->attribute_id . ", " . $confData->position . "); ";
$writeAdapter->query($insertQueryOuter);
$lastInsertId = $writeAdapter->lastInsertId();

$insertQueryOuter = '';
$insertQueryOuter .= " INSERT INTO `catalog_product_super_attribute_label` (`product_super_attribute_id`,
`store_id`, `use_default`, `value`) VALUES
($lastInsertId, 0, " . $confData->use_default . ", '" . $confData->label . "'); ";
$writeAdapter->query($insertQueryOuter);
$valueIndexes = $confData->values;

foreach ($valueIndexes as $key => $valueIndex) {
$insertQueryInner= '';
$resultValue = 0;
$backendType = $readAdapter->fetchOne("select `backend_type` from `eav_attribute` where `attribute_id`=" . $confData->attribute_id . " ");
$resultValue = $readAdapter->fetchOne("select `value` from catalog_product_entity_$backendType where `attribute_id`=" . $confData->attribute_id . "
and `entity_id`='" . $childIds[$key] . "' ");
if($resultValue) {
$insertQueryInner .= " INSERT INTO `catalog_product_super_attribute_pricing` (`product_super_attribute_id`,
`value_index`, `is_percent`, `pricing_value`, `website_id`) VALUES
($lastInsertId, $resultValue, " . $valueIndex->is_percent . ", " . $valueIndex->pricing_value . ", 0); ";
}
$writeAdapter->query($insertQueryInner);
}
}
}
}
}
$i++;
}

echo ($i-1)." products imported successfully";
?>


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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