Multilingual Export/Import for OpenCart 2.x / 3.x / 4.x

please check you private messages.

Ystei wrote:Hi!

I just updated to the 2-23 version on my OC 2.0.1.1 and when then tried to import. i get a "Success" message in the backadmin but when i look at the products in the pront end 12 of 200 products are not showing any picture or information and when i go to the product page i get only "Product not found!".

Not sure if this is caused by the update or not.. i just discovered it now.. I have two languages..

The address is like this

Code: Select all

/index.php?route=product/product&path=62_200&product_id=

The address for the products that shows up normal looks like this

Code: Select all

/index.php?route=product/product&path=64_223&product_id=8078

Very thankful for all clues!


Error log shows this when importing categories:

Code: Select all

2015-03-18 23:15:32 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:15:32 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8161' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:15:32 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10


2015-03-18 23:15:35 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:15:35 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8018' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:15:35 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10

And this when importing products:

Code: Select all

2015-03-18 23:16:40 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:16:40 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8006' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40
2015-03-18 23:16:40 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10
2015-03-18 23:17:08 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18
2015-03-18 23:17:08 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8006' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:17:08 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10


2015-03-18 23:19:19 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:19:19 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8015' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:19:19 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10


2015-03-18 23:19:41 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:19:41 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8168' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:19:41 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10


2015-03-18 23:20:26 - PHP Warning:  mysqli::query(): (21000/1242): Subquery returns more than 1 row in install_folder/system/library/db/mysqli.php on line 18


2015-03-18 23:20:26 - PHP Notice:  Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '8316' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in install_folder/system/library/db/mysqli.php on line 40


2015-03-18 23:20:26 - PHP Notice:  Trying to get property of non-object in install_folder/catalog/model/catalog/product.php on line 10

As the error message suggests, for product 8316 you either have:
- multiple discounts for same customer_group
- multiple specials for same customer_group
- multiple rewards for same customer_group
- multiple stock_status with same name
- multiple weight_class with same name
- multiple length_class with same name

Please check your data for above mentioned possibilties.

Goinea92 wrote:Hello again!

Everything works fine with Export / Import, but one thing: After I import products they appear on the store (Frontend) but there's no entry in the Backend (Catalog > Products). How can I fix that?

It wouldn't have been a problem, but I wanted to add Filters on the products, and that was the only way I could add them.

Thanks,
Andy

See https://github.com/opencart/opencart/issues/2085 on this.

Hi

For Export import of the products, can we import the images also in particular folder we make for the products.

If yes please tell us how.

You can choose what ever folder you wish to store the images in. say...
image/catalog/mydata/categoryName/productName.jpg (OC 2.x)
for example should you have 1000's of products.

When you export the data there is a field in the products excel file for the location of the main image for the product.
image_name is the fields name.

Is it possible to Upload product images from Admin Sections from Export Import in Bulk

mtjhost wrote:Is it possible to Upload product images from Admin Sections from Export Import in Bulk

Just FTP them to the directory you wish to have them in. It is the easiest method.

Mike

I too am receiving a Success message with no errors at all, but nothing is imported. DB tables are empty.

I'm trying to import around 500 categories using the exact template given from the Export.

(OC ver 2.0.1.1)

Anyone sussed this yet?

Hi,

Are you using more than one language ?

When you import the data do you get any errors show up in your error log ?

Hii , I'm Getting Error on VPS Linux Server Saying ZipArchieve Not Found

Hi,

Is this compatible with any of the extensions that allow sku's for product options?

Thanks in advance

I downloaded and installed v 2.24, but it still shows that I'm using an old version.

Nope just the one language, and no error at all, it tells me I have successfully imported products, yet nothing is imported.. (it also does not take more than a second to execute)...

do you have access to your phpmyadmin ?
if so please check the oc_products and oc_categories databases and see if you have anything showing.

Chuckun wrote:Nope just the one language, and no error at all, it tells me I have successfully imported products, yet nothing is imported.. (it also does not take more than a second to execute)...

Use LibreOffice, or OpenOffice when editing your spreadsheet. Make sure you have the store_id column filled in with the correct store_id (usually 0 if only one store). Also make sure you use unique numeric category_ids.

When trying to Import a Product file, with over 2,000 products, I encounter the error: Maximum execution time of 30 seconds exceeded

As I was unsure which specific php.ini file, or files, relates to this issue, I have altered the section: max_execution_time = 36000 to max_execution_time = 90000 within the php.ini files located in:

[home]
[home]/admin
[home]/azonwebstoresync/api
[home]/azonwebstoresync/opencart/amazon
[home]/installbak

The error still happens after uploading the altered php.ini files to the folders above. Is there another section I should have changed within one or more of these files, or are there other files I have missed, which are responsible for extending the 30 second limit?

[OpenCart version 2.0.1.1]

I have a missing then installing this module for 2.0.1.1
missing is here : And Import/Export menu have not seem on admin panel.

How can I fix it?
Please can you help me?

how many products and categories do you have ?

I've tryed to install in 2.0.1.1 using the admin xml-istaller, but after half install I get a popup saying "Bad Gateway".
And yes, my ftp-settings are correct. I have tryed 5 times with the same result every time.

to see if it is an issue with your setup or with the amount of data you are trying to upload. Create a test export. Add a few items to your cart and export the products and then try to import the items again, 'overwriting' the exiting cart. If this works then it is due to having a large amount of data to upload.

If this shows that you do have too many to upload, try to split the products spreadsheet up in to 1000 products per file, and import them one by one, keeping the existing data on the site and not 'overwriting' it. that way you should be able to get all of them uploaded.

I have over 6000 items and I have 6 files to add, and all of them go in better this way.