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,create d_at,custom_design,custom_design_from,custom_desig n_to,custom_layout_update,description,enable_googl echeckout,gallery,gift_message_available,has_optio ns,image,image_label,is_imported,manufacturer,medi a_gallery,meta_description,meta_keyword,meta_title ,minimal_price,msrp,msrp_display_actual_price_type ,msrp_enabled,name,news_from_date,news_to_date,opt ions_container,page_layout,price,required_options, short_description,small_image,small_image_label,sp ecial_from_date,special_price,special_to_date,stat us,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_c onfig_backorders,min_sale_qty,use_config_min_sale_ qty,max_sale_qty,use_config_max_sale_qty,is_in_sto ck,notify_stock_qty,use_config_notify_stock_qty,ma nage_stock,use_config_manage_stock,stock_status_ch anged_auto,use_config_qty_increments,qty_increment s,use_config_enable_qty_inc,enable_qty_increments, _links_related_sku,_links_related_position,_links_ crosssell_sku,_links_crosssell_position,_links_ups ell_sku,_links_upsell_position,_associated_sku,_as sociated_default_qty,_associated_position,_tier_pr ice_website,_tier_price_customer_group,_tier_price _qty,_tier_price_price,_media_attribute_id,_media_ image,_media_lable,_media_position,_media_is_disab led

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,_produc t_websites,color,cost,country_of_manufacture,creat ed_at,custom_design,custom_design_from,custom_desi gn_to,custom_layout_update,description,enable_goog lecheckout,gallery,gift_message_available,has_opti ons,image,image_label,is_imported,manufacturer,med ia_gallery,meta_description,meta_keyword,meta_titl e,minimal_price,msrp,msrp_display_actual_price_typ e,msrp_enabled,name,news_from_date,news_to_date,op tions_container,page_layout,price,required_options ,short_description,small_image,small_image_label,s pecial_from_date,special_price,special_to_date,sta tus,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_st ock,notify_stock_qty,use_config_notify_stock_qty,m anage_stock,use_config_manage_stock,stock_status_c hanged_auto,use_config_qty_increments,qty_incremen ts,use_config_enable_qty_inc,enable_qty_increments ,_links_related_sku,_links_related_position,_links _crosssell_sku,_links_crosssell_position,_links_up sell_sku,_links_upsell_position,_associated_sku,_a ssociated_default_qty,_associated_position,_tier_p rice_website,_tier_price_customer_group,_tier_pric e_qty,_tier_price_price,_media_attribute_id,_media _image,_media_lable,_media_position,_media_is_disa bled';

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.

View more threads in the same category: