Resolved error Loading ICEcat indexes

Hello,
Please follow the link Import ICEcat products , The solution for the above issue is explained in the link.


Thank you.

I've done with this operation.
Please advice what's the next step in importing products in virtuemart

Which next step? Have you followed the tutorial Tharuna linked?

I've made the settings from tutorial. And I run the Loading ICEcat indexes ... at the end of processing all entries step by step 1000 lines each ,... I've got in red, this error :

Error

Column count doesn't match value count at row 123 SQL=INSERT INTO `eltwc_csvi_icecat_index` (`path`,`product_id`,`updated`,`quality`,`supplier_id`,`prod_id`,`catid`,`m_prod_id`,`ean_upc`,`on_market`,`country_market`,`model_name`,`product_view`,`high_pic`,`high_pic_size`,`high_pic_width`,`high_pic_height`,`m_supplier_id`,`m_supplier_name`) VALUES ('export/freexml.int/INT/9465268.xml','9465268','20150521095801','ICECAT','895','F55000VI0P','1324','F-55000-Vi0P','7332543106882','1','NL;RU','F55000VI0P','3617','http://images.icecat.biz/img/norm/high/9465268-2657.jpg','53610','335','335','895','AEG'),('export/http://freexml.int/INT/9465268.xml','9465268','20150521095801','ICECAT','895','F55000VI0P','1324','911 436 007','7332543106882','1','NL;RU','F55000VI0P','3617','http://images.icecat.biz/img/norm/high/9465268-2657.jpg','53610','335','335','895','AEG'), and so on .... a long list ...

please advice

Hello,

I wonder if something else is wrong in the query because what you posted, has enough columns. Can you set the import to 100 lines each and see if that works? The query of 1000 may be too large. If the same issue happens set it to 10 lines and post the full 10 lines when an issue shows up.

I'll test it ... and report ! see u around !

I have the same issue, even if process just 1 line (single) - See attached file

If process only suppliers it works in a few seconds - See attached file

Maybe i am wrong but it seems that change to for e.g. 10 not results in processing 10, but still 1000 - See attached file

My Joomla version is 3.4.5
Maybe helpfull i checked my table, its filled partially - see screenshot last rows
Filled with 161010 records - MyISAM - utf8_general_ci 35,8 MiB

Yes, it is the same issue , identical !

Hoi Roland,
Ik neem aan dat je dit momenteel aan het uitzoeken bent. Jammer dat het niet direct vanuit de box werkt, want ik had vandaag verder willen testen. Mijn hoster wil geen aanpassing op server maken ( local-infile=1 ) omdat hierbij wat veiligheids-zaken kunnen gaan spelen bij het inschakelen van deze functie. Zij adviseren om het via PHPMyAdmin te doen. :huh:

Ik had eigenlijk gepland eind komende week online te gaan. Hoop dat het nog gaat lukken voor die tijd.
Groeten Stan

Hello,
Please load the patch file attached and run the process again.

Thank you.
patch_201115.zip

After patch ...

Error

The used command is not allowed with this MySQL version SQL=LOAD DATA LOCAL INFILE '/home/___/public_html/tmp/com_csvi/icecat_index' INTO TABLE `eltwc_csvi_icecat_index` FIELDS TERMINATED BY ' ' ENCLOSED BY '"' IGNORE 1 LINES

I'll run in steps of 1000 each now ... I'll be back

Hello Stan,

That it doesn't work out of the box is a pity for sure. The single load worked fine for me, so I wasn't thinking it would be a problem for the stepped import. The issue has been identified as an incorrect CSV file, please try the patch and let me know.

@buiak, you should set the import to the stepped import as your host doesn't allow the LOCAL INFILE command.

It works well now ... Please advice what's the next step in importing products from icecat to virtuemart . Thank you in advance

Ronald, the maintenance is now working, but i am struggling again, now with import. The import works but nothing is added to products, no description, no image

See attachments CSV file and logfile

Hello Stan,

Let's start with the issue between your template configuration and your file. Your template configuration doesn't have the same amount of fields as the import file nor are they in the same order. In your file you are missing entries for the product_desc and product_s_desc. So they will need to be added to the file. The order of the fields in the file needs to be the same as the order of the fields in your template. So you will need to reorder either the template fields or the file fields.

The other thing I see is that none of your SKUs is matching those of ICEcat. Looking at the first product, I see this

2015-11-20 16:55:03 1 [DEBUG] Found ICEcat mpn reference: NX.MLFEH.031
2015-11-20 16:55:03 1 [DEBUG] Found ICEcat manufacturer name: Acer
2015-11-20 16:55:03 1 [DEBUG] Find the ICEcat ID for manufacturer Acer and part number NX.MLFEH.031
2015-11-20 16:55:03 1 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`prod_id` = 'NX.MLFEH.031' AND `s`.`supplier_name` = 'Acer'
2015-11-20 16:55:03 1 [DEBUG] Find the ICEcat ID by similar SKU for manufacturer Acer and part number NX.MLFEH.031
2015-11-20 16:55:03 1 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`prod_id` LIKE 'NX.MLFEH.031%' AND `s`.`supplier_name` = 'Acer'
2015-11-20 16:55:03 1 [DEBUG] Find the ICEcat ID as alternative ID for manufacturer Acer and part number NX.MLFEH.031
2015-11-20 16:55:03 1 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`m_prod_id` = 'NX.MLFEH.031' AND `s`.`supplier_name` = 'Acer'

The combination of NX.MLFEH.031 and Acer doesn't find any ICEcat link.

Looking further there is another product

2015-11-20 16:55:06 134 [DEBUG] Found ICEcat mpn reference: PSSG0E-00600QDU
2015-11-20 16:55:06 134 [DEBUG] Found ICEcat manufacturer name: Toshiba
2015-11-20 16:55:06 134 [DEBUG] Find the ICEcat ID for manufacturer Toshiba and part number PSSG0E-00600QDU
2015-11-20 16:55:06 134 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`prod_id` = 'PSSG0E-00600QDU' AND `s`.`supplier_name` = 'Toshiba'
2015-11-20 16:55:06 134 [DEBUG] Find the ICEcat ID by similar SKU for manufacturer Toshiba and part number PSSG0E-00600QDU
2015-11-20 16:55:06 134 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`prod_id` LIKE 'PSSG0E-00600QDU%' AND `s`.`supplier_name` = 'Toshiba'
2015-11-20 16:55:06 134 [DEBUG] Find the ICEcat ID as alternative ID for manufacturer Toshiba and part number PSSG0E-00600QDU
2015-11-20 16:55:06 134 [QUERY] SELECT product_id FROM `oks_csvi_icecat_index` AS `i` LEFT JOIN `oks_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`m_prod_id` = 'PSSG0E-00600QDU' AND `s`.`supplier_name` = 'Toshiba'

This actually does find a product in my system but not in yours.

This is the output on my system as I would expect to see it on yours

2015-11-20 19:01:02 1 [DEBUG] Found ICEcat mpn reference: PSSG0E-00600QDU
2015-11-20 19:01:02 1 [DEBUG] Found ICEcat manufacturer name: Toshiba
2015-11-20 19:01:02 1 [DEBUG] Find the ICEcat ID for manufacturer Toshiba and part number PSSG0E-00600QDU
2015-11-20 19:01:02 1 [QUERY] SELECT product_id FROM `jos_csvi_icecat_index` AS `i` LEFT JOIN `jos_csvi_icecat_suppliers` AS `s` ON `s`.`supplier_id` = `i`.`supplier_id` WHERE `i`.`prod_id` = 'PSSG0E-00600QDU' AND `s`.`supplier_name` = 'Toshiba'
2015-11-20 19:01:02 1 [DEBUG] Calling ICEcat URL: http://data.icecat.biz/export/freexml.int/INT/24437769.xml

Notice the last line saying Calling ICEcat URL.

So, when you go to Settings -> ICEcat how many Files does it say?

Hi Roland,
Files = 161122
Supplier = 2827

I changed all you asked and did import again but still no results, i double checked everything: I can't find some misconfiguration but maybe i am wrong

I did again maintenance but still 161122 files after that

I checked the icecat_index file in /tmp/com_csvi and that seems correct.
Last line number = 495779
containing: export/http://freexml.int/INT/30347996.xml

Hello guys,

I have a new patch for you to use. This solves the issue of having too few entries loaded. The main problem is that in the current ICEcat files some lines have become very long. This patch deals with that.

Hi Roland, The patch solved the problem. Thanks for your excellent support.

I have a question about import based on gtin, because i want my own sku's (not sku from manufacturer).
I saw in another topic it can be done with:
Can I use this field now to import through csvi EAN Code?
Yes you can, on the Options tab set the Import based on to Cystom SKU and in the field that appears put the name of the field product_gtin.

In the options tab after set to Custom SKU (i think Cystom was misspelled) i see no extra field for put product_gtin in it.
What fields are required in the csv importfile? Just product_sku and product_gtin?

Hello,

The main field that is required is the product_sku or any other field stored in the virtuemart_products table. This can be set in the Import based on field on the Options tab of the product import template.

The product_gtin field is such field that is available in the virtuemart_products table.

because i want my own sku's (not sku from manufacturer).

The only issue I see here is that you can't import the ICEcat data then. CSVI uses the same field for retrieving the ICEcat data. So you would need to use the manufacturer SKU as main field and have your own SKU in the product_sku field for example.

To import ICEcat data, you need to include the fields of the data that you want to retrieve from ICEcat.