I’ve tried the Magento migration to migrate products from a Magento 1 application into a Magento 2 and it didn’t quite worked for me. All I wanted was a CSV file from M1 products to import via the Admin > System > Import > Products in my M2 application.

To solve the problem I wrote a SQL which extracts the data from the M1 database and generates a CSV file that can be imported by M2. See below:

Please don’t feel overwhelmed by the query. It looks big only because its joins and because I’ve covered most of the available fields. You might want to remove the ones you don’t want.

SELECT *
FROM
(
	SELECT
		'sku','store_view_code','attribute_set_code','product_type','categories','product_websites','name',
		'description','short_description','weight',
        'product_online',
        'tax_class_name',
        'visibility',
        'price',
		'special_price','special_price_from_date','special_price_to_date','url_key','meta_title','meta_keywords',
		'meta_description','base_image','base_image_label','small_image','small_image_label','thumbnail_image',
		'thumbnail_image_label','swatch_image','swatch_image_label','created_at','updated_at',
		'new_from_date','new_to_date','display_product_options_in','map_price','msrp_price','map_enabled',
		'gift_message_available','custom_design','custom_design_from','custom_design_to','custom_layout_update',
		'page_layout','product_options_container','msrp_display_actual_price_type','country_of_manufacture',
		'additional_attributes',
        'qty','out_of_stock_qty','use_config_min_qty','is_qty_decimal','allow_backorders',
		'use_config_backorders','min_cart_qty','use_config_min_sale_qty','max_cart_qty','use_config_max_sale_qty',
		'is_in_stock','notify_on_stock_below','use_config_notify_stock_qty','manage_stock','use_config_manage_stock',
		'use_config_qty_increments','qty_increments','use_config_enable_qty_inc','enable_qty_increments',
		'is_decimal_divided','website_id','related_skus','related_position','crosssell_skus','crosssell_position',
		'upsell_skus','upsell_position','additional_images','additional_image_labels','hide_from_product_page',
		'bundle_price_type','bundle_sku_type','bundle_price_view','bundle_weight_type','bundle_values',
		'bundle_shipment_type',
    'configurable_variations', 
    'configurable_variation_labels', 'associated_skus'
	UNION
    SELECT *
    FROM (
		SELECT a.sku as `a1`, '' as `a2`, 'Default' as `a3`, a.type_id, 'Default Category' as `a4`, 'base', b.value as `a5`,
			c.value as `a6`, d.value as `a7`, IFNULL(e.value, 0) as `a8`,
            u.value as `status`,
            'Taxable Goods' as `a10`,
			CASE  WHEN o.value = 1 then 'Not Visible Individually' WHEN o.value = 2 then 'Catalog' WHEN o.value = 3 then 'Search' ELSE 'Catalog, Search' END as `a11`,
			IFNULL(f.value, 0) as `a12`,
			'' as `a13`, '' as `a14`, '' as `a15`, CONCAT(CONCAT(g.value,'-'),a.entity_id) as `a16`, h.value as `a17`, k.value as `a18`,
			l.value as `a19`, REPLACE(IFNULL(m.value, ''), 'no_selection', '') as `a81`, '' as `a82`, REPLACE(IFNULL(m.value, ''), 'no_selection', '') as `a83`, '' as `a84`, REPLACE(IFNULL(m.value, ''), 'no_selection', '') as `a20`,
			'' as `a21`, REPLACE(IFNULL(m.value, ''), 'no_selection', '') as `a22`, '' as `a23`, a.created_at as `a24`, a.updated_at as `a25`,
			'' as `a26`, '' as `a27`, 'Block after Info Column' as `a28`, '' as `a29`, '' as `a30`, '' as `a31`,
			'Use config' as `a32`, '' as `a33`,'' as `a34`,'' as `a35`,'' as `a36`,
			'' as `a37`,'' as `a38`,'' as `a39`, 'Australia' as `a40`,
			CONCAT(CASE WHEN p.value IS NOT NULL THEN 'colour=' ELSE '' END,CASE WHEN p.value = 1 then 'Blue' WHEN p.value = 2 then 'Red' WHEN p.value = 3 then 'Yellow' ELSE '' END) as `a41`,
			n.qty as `a42`, '0' as `a43`, n.use_config_min_qty as `a44`, n.is_qty_decimal as `a45`, '0' as `a46`,
			'1' as `a47`, '1' as `a48`, '1' as `a49`, '1' as `a50`, '1' as `a51`,
			'1' as `a52`, '1' as `a53`, '1' as `a54`, '1' as `a55`, '1' as `a56`,
			'1' as `a57`, '1' as `a58`, '1' as `a59`, '0' as `a60`,
			'0' as `a61`, '0' as `a62`, '' as `a63`, '' as `a64`, '' as `a65`, '' as `a66`,
			'' as `a67`, '' as `a68`, REPLACE(IFNULL(m.value, ''), 'no_selection', '') as `a69`,  '' as `a70`, '' as `a71`,
			'' as `a72`, '' as `a73`, '' as `a74`, '' as `a75`, '' as `a76`,
			'' as `a77`,
			(
			SELECT
				CASE WHEN a.type_id = 'configurable' THEN
					GROUP_CONCAT(
						CONCAT(
							CONCAT(CONCAT('sku=',r.sku), ','),
							CONCAT(CASE WHEN p.value IS NOT NULL THEN 'colour=' ELSE '' END,CASE WHEN p.value = 1 then 'Blue' WHEN p.value = 2 then 'Red' WHEN p.value = 3 then 'Yellow' ELSE '' END)
						)
					SEPARATOR '|')
				ELSE '' END
			FROM catalog_product_relation q
			INNER JOIN catalog_product_entity r ON q.child_id = r.entity_id
			LEFT JOIN catalog_product_entity_int s ON s.entity_id = q.child_id AND s.attribute_id =
				(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'colour')
			WHERE q.parent_id = a.entity_id
			GROUP BY q.parent_id
			) as `a78`,
			'' as `a79`,'' as `a80`
		FROM catalog_product_entity a
		LEFT JOIN catalog_product_entity_varchar b ON b.entity_id = a.entity_id AND b.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'name')
		LEFT JOIN catalog_product_entity_text c ON c.entity_id = a.entity_id AND c.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'description')
		LEFT JOIN catalog_product_entity_text d ON d.entity_id = a.entity_id AND d.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'short_description')
		LEFT JOIN catalog_product_entity_decimal e ON e.entity_id = a.entity_id AND e.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'weight')
		LEFT JOIN catalog_product_entity_decimal f ON f.entity_id = a.entity_id AND f.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'price')
		LEFT JOIN catalog_product_entity_varchar g ON g.entity_id = a.entity_id AND g.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'url_key')
		LEFT JOIN catalog_product_entity_varchar h ON h.entity_id = a.entity_id AND h.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'meta_title')
		LEFT JOIN catalog_product_entity_varchar k ON k.entity_id = a.entity_id AND k.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'meta_keywords')
		LEFT JOIN catalog_product_entity_varchar l ON l.entity_id = a.entity_id AND l.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'meta_description')
		LEFT JOIN catalog_product_entity_varchar m ON m.entity_id = a.entity_id AND m.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'image')
		LEFT JOIN cataloginventory_stock_item n ON n.product_id = a.entity_id
		LEFT JOIN catalog_product_entity_int o ON o.entity_id = a.entity_id AND o.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'visibility')
		LEFT JOIN catalog_product_entity_int p ON p.entity_id = a.entity_id AND p.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'colour')
		LEFT JOIN catalog_product_entity_int u ON u.entity_id = a.entity_id AND u.attribute_id =
			(SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'status')
        #WHERE a.sku like 'SOME_SKU%'
        ORDER BY a.type_id DESC
	) z
) t
INTO OUTFILE '/tmp/products_for_m2_import.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

As you can see, most of the out-of-the-box fields are mapped. Also, the CSV is saved in the /tmp folder of the machine running your database.

This code also covers configurable products. Check the attribute ‘colour’. It’s used to create the associated products. You might want to create a function for the few duplicated SQL snippets.

If any questions, drop a comment below and I will reply ASAP.

Happy Coding!

Migrating Products From Magento 1 to Magento 2https://i2.wp.com/blog.mdnsolutions.com/wp-content/uploads/2016/06/Screen-Shot-2016-06-24-at-10.49.47-AM.png?fit=768%2C391&ssl=1https://i2.wp.com/blog.mdnsolutions.com/wp-content/uploads/2016/06/Screen-Shot-2016-06-24-at-10.49.47-AM.png?resize=150%2C150&ssl=1Renato MedinaMagentoMagento 2Import,Magento 1,Magento 2,Migration,ProductsI've tried the Magento migration to migrate products from a Magento 1 application into a Magento 2 and it didn't quite worked for me. All I wanted was a CSV file from M1 products to import via the Admin > System > Import > Products in my M2 application. To...A Magento Craftsman in Melbourne