Our task:To make the import of the products data from the Excel file quick and easy without the excellent knowledge.
Solution using the practice sample:We should learn the Excel file with the data carefully. The file contains the information about the product name and the price. The file is formatted by categories. The number of the products is about 4 000.
You can write an algorithm using some library with the open code for the excel files parsing in order to parse the source and take the data, but taking into account that the task is disposable, the most optimistic solution will be to copy the cells data in the notepad (ctrl+c, ctrl+v). As goods are divided into categories, we can’t copy them with one block, that’s why we have to copy them by categories. It will take us 10 seconds, not 5 minutes.
Please, investigate what we have got. We have got the file with the tabs, blank space symbols and other useless things copied from the excel file.
We should use Notepad++ search/replacement with use of the regular expressions for clearing the data in the text file. We should clear the redundant blank spaces and tabs and separate the text data with the delimiter “|”. The data is ready for using.
Somehow, we need to create the file from the ready data in the correct format for the import. We should learn the format.
Then we should log into Magento admin panel and do the export of one product using the standard interface. We need to check what we have got. We have got the file with the following content:
sku,_store,_attribute_set,_type,_category,_product_websites,color,cost,country_of_manufacture,created_at,custom_design,custom_design_from,custom_design_to,custom_layout_update,description,enable_googlecheckout,gallery,gift_message_available,has_options,image,image_label,is_imported,manufacturer,media_gallery,meta_description,meta_keyword,meta_title,minimal_price,msrp,msrp_display_actual_price_type,msrp_enabled,name,news_from_date,news_to_date,options_container,page_layout,price,required_options,short_description,small_image,small_image_label,special_from_date,special_price,special_to_date,status,tax_class_id,thumbnail,thumbnail_label,updated_at,url_key,url_path,visibility,weight,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,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,_links_related_sku,_links_related_position,_links_crosssell_sku,_links_crosssell_position,_links_upsell_sku,_links_upsell_position,_associated_sku,_associated_default_qty,_associated_position,_tier_price_website,_tier_price_customer_group,_tier_price_qty,_tier_price_price,_media_attribute_id,_media_image,_media_lable,_media_position,_media_is_disabled
We can see the header of the products parameters at the beginning separated by comma. The products data follows them. The absent parameters are left blank (the next comma is written down there). If a field contains several words with the blank space, the data is quoted.
The headers line and the data line are divided with the symbol of newline and the carriage return. It can be concluded that the data about every new product begins with a new line.
You should take the cleared data about the products and create the same file for import.
The server with the system can accept files for uploading not more 2Mb. We have a big number of data, so we need to divide them into the several parts, for example, by 500 products in the file. We will use the PHP interpreter for execution of the next code:
<?php
$i = 0;
$j = 0;
$products = fopen('products.txt', 'r');
$importHeaders = 'sku,_store,_attribute_set,_type,_category,_product_websites,color,cost,country_of_manufacture,created_at,custom_design,custom_design_from,custom_design_to,custom_layout_update,description,enable_googlecheckout,gallery,gift_message_available,has_options,image,image_label,is_imported,manufacturer,media_gallery,meta_description,meta_keyword,meta_title,minimal_price,msrp,msrp_display_actual_price_type,msrp_enabled,name,news_from_date,news_to_date,options_container,page_layout,price,required_options,short_description,small_image,small_image_label,special_from_date,special_price,special_to_date,status,tax_class_id,thumbnail,thumbnail_label,updated_at,url_key,url_path,visibility,weight,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,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,_links_related_sku,_links_related_position,_links_crosssell_sku,_links_crosssell_position,_links_upsell_sku,_links_upsell_position,_associated_sku,_associated_default_qty,_associated_position,_tier_price_website,_tier_price_customer_group,_tier_price_qty,_tier_price_price,_media_attribute_id,_media_image,_media_lable,_media_position,_media_is_disabled';
while (!feof($products)) {
if($j == 0) {
$csv = fopen('import-files/import' . $i . '.csv', 'a');
fwrite($csv, $importHeaders . "\r\n");
}
$buffer = fgets($products, 4096);
$prodBuf = explode('|', $buffer);
$sku = 'sku-' . $i;
$date = date("Y-m-d H:i:s");
$product = $prodBuf[0];
$price = str_replace(',', '.', str_replace("\r\n", '', $prodBuf[1]));
fwrite($csv, $sku . ',,Default,simple,,base,,,,"' . $date . '",,,,,"' . $product . '",1,,,0,,,No,,,,,,,,"Use configuration settings","Use configuration settings"' .
',"' . $product . '","' . $date . '",," Block after the information column",,' . $price . '00,0,"' . $product . '",,,,,,1,2,,,"' . $date . '",,,' .
'4,0.0000,100.0000,0.0000,1,0,0,1,1.0000,1,0.0000,1,1,,1,0,1,0,1,0.0000,1,0,,,,,,,,,,,,,,80,,,1,0' . "\r\n");
$i++;
$j++;
if($j == 500) {
fclose($csv);
$j = 0;
}
}
fclose($products);
fclose($csv);
The text file data is read out, formatted as needed and the csv files are created. Then we should use the standard mechanism of the Magento import uploading the resulting files.
We can see that we can improve Magento functional with our own things and customizations. One of such tips for export the feed files from Magento to shopping engines is Magento Feed Manager. We are talking about GoMage Feed Pro extension which is one of the best in this sphere of the datafeed creation and uploading of the feed files into the shopping engines such as Google, Amazon, eBay, etc:
https://www.gomage.com/extensions/gomage-feed-pro.html
