[UPDATED] Export/Import tool for OpenCart 1.4.9

Ack, think I just posted my issue in the wrong forum, apologies mods

http://forum.opencart.com/viewtopic.php?f=112&t=70406


Basically, post a server move (terrible caching issues) the Import/Export tool is outputting corrupt files. The excel file should be around 800k. I'm getting a tiny 4k file outputted with the info inside reading:

----------------------------
Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay<br />Error No: 1104<br />SELECT p.product_id, pd.name, GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR "," ) AS categories, p.sku, p.location, p.quantity, p.model, m.name AS manufacturer, p.image AS image_name, p.shipping, p.price, p.date_added, p.date_modified, p.date_available, p.weight, wc.unit, p.length, p.width, p.height, p.status, p.tax_class_id, p.viewed, p.sort_order, pd.language_id, ua.keyword, pd.description, pd.meta_description, pd.meta_keywords, p.stock_status_id, mc.unit AS length_unit, p.subtract, p.minimum, p.cost, GROUP_CONCAT( DISTINCT CAST(pr.related_id AS CHAR(11)) SEPARATOR "," ) AS related, GROUP_CONCAT( DISTINCT pt.tag SEPARATOR "," ) AS tags FROM `product` p LEFT JOIN `product_description` pd ON p.product_id=pd.product_id AND pd.language_id=1 LEFT JOIN `product_to_category` pc ON p.product_id=pc.product_id LEFT JOIN `url_alias` ua ON ua.query=CONCAT('product_id=',p.product_id) LEFT JOIN `manufacturer` m ON m.manufacturer_id = p.manufacturer_id LEFT JOIN `weight_class_description` wc ON wc.weight_class_id = p.weight_class_id AND wc.language_id=1 LEFT JOIN `length_class_description` mc ON mc.length_class_id=p.length_class_id AND mc.language_id=1 LEFT JOIN `product_related` pr ON pr.product_id=p.product_id LEFT JOIN `product_tags` pt ON pt.product_id=p.product_id AND pt.language_id=1 GROUP BY p.product_id ORDER BY p.product_id, pc.category_id;
-------------------------------------------------

I'm guessing the config on the new server does not match that of the old where the tool was working perfectly.

I'm trying to research what may be causing the issue but am not developer so it's sort of like wading through Russian

The max_join_size error it seems might be caused by an MySql config setting. I'm guessing I've just hit a limit on this new server with my latest import of products and now the thing falls over due to too many tables when trying to read the Excel file?

The Excel file stands at around 800k. Can I play with the MySQL config max_join_size limit somehow?

In file admin/model/tool/export.php, in function
populateProductsWorksheet, after the line containing

$storeIds = $this->getStoreIdsForProducts( $database );

add this:

$database->query( "SET SQL_BIG_SELECTS=1" );

Really appreciate the help J, honestly, you're a knight in shining armour at the moment.

So, I'd add the line straight afterwards to get:

$storeIds = $this->getStoreIdsForProducts( $database );
$database->query( "SET SQL_BIG_SELECTS=1" );
$query = "SELECT ";
$query .= " p.product_id,";
$query .= " pd.name,";
$query .= " GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR \",\" ) AS categories,";
$query .= " p.sku,";
$query .= " p.location,";
etc...


?

That's right.

Really appreciate the help J, I'll sit down Friday and try to figure this out.

*EDIT* It worked, and I'm so grateful, thank you.

Sincerely, I don't know how you are fixed work/skills-wise, but if there is something I can help you with from a design point of view- an opinion, some advice etc, then send me a PM

_x000D_ problem happens because of line break in single cell of excel. One can use formula "=clean(A1)" to remove line breaks and then upload excel. This should solve the problem. If you need more help you can write me at sportyhabits@gmail.com

- Sportyhabits
http://www.sportyhabits.com

sportyhabits wrote:_x000D_ problem happens because of line break in single cell of excel. One can use formula "=clean(A1)" to remove line breaks and then upload excel. This should solve the problem. If you need more help you can write me at sportyhabits@gmail.com

- Sportyhabits
http://www.sportyhabits.com

Line breaks are perfectly legal in a spreadsheet cell. Use LibreOffice Calc instead of MS Excel if the latter can't cope with line breaks.

Honestly I already googling for hours and found no answer for my problem
I installed Export/Import tool for my OC 1.4.9.4 and seems okay.

The problem is when I export, I always got 0 bytes excel file named backup_categories_products.xls.
When I open that file there is nothing inside.

Am I stupid or what

Please give me a clue...

wahid wrote:Honestly I already googling for hours and found no answer for my problem
I installed Export/Import tool for my OC 1.4.9.4 and seems okay.

The problem is when I export, I always got 0 bytes excel file named backup_categories_products.xls.
When I open that file there is nothing inside.

Am I stupid or what

Please give me a clue...

The most obvious thing to do is to check your server's error_log, and OpenCart's system/logs/error.txt.

JNeuhoff wrote: The most obvious thing to do is to check your server's error_log, and OpenCart's system/logs/error.txt.

I checked both log, but nothing there.
I guess it is web host problem.
I will try locally.

Thanks

G'day,

Apologies in advance if this one has been answered previously! (haven't seen a relevant post).

Prior to this month the Export/Import tool was working fine for me. Since then I have been receiving the following error on all my exports and content is missing from the file:

"Excel found unreadable content in 'backup_categories_products.xls'. Do you want to recover the contents of this workbook?..."

- Opening the file in Office 2010 I receive the above error with missing data
- Opening the file in Openoffice calc I simply have missing data from the file

Running Opencart 1.4.9.3

A followon issue to the above is the fact that I cannot get the export/import tool working since removing and reintalling the extension (as I originally contemplating extension corruption) - the option is now no longer available in Admin \ System...

- Any help would be greatly appreciated.

Thanks

tmorgan wrote: A followon issue to the above is the fact that I cannot get the export/import tool working since removing and reintalling the extension (as I originally contemplating extension corruption) - the option is now no longer available in Admin \ System...

Upload all the files, and install VQmod, and it will show up in the admin> system

Thanks J,

I followed the instructions within the readme for opencart-1-4-9-x-27-export-import.zip but it still will not appear? Ideas?

... Since having this issue I have installed a TEST site that I was planning to migrate to that is running Opencart v1.5.4 alongwith opencart-1-5-4-x-export-import-12.zip - I can confirm that this installation is working fine.

----> So in summary I have another instance (TEST) of Opencart running V1.5.4 working fine with the the exporter)

---->> But original (PROD) version running 1.4.9.3 not working

--------->>> Any recommendations you can provide would be greatly appreciated!

Hi - just purchased extension, running 1.5.4.1 and vqmod 2.1.7. I can export with no issue. However, after adding a category and importing nothing happens. I see the upload(%) go to 100% but it then returns me to the import/export screen with all my options but no success or failure messages. Is there a max products (I have 3700). Any help will be appreciated

sys1rak wrote:Hi - just purchased extension, running 1.5.4.1 and vqmod 2.1.7. I can export with no issue. However, after adding a category and importing nothing happens. I see the upload(%) go to 100% but it then returns me to the import/export screen with all my options but no success or failure messages. Is there a max products (I have 3700). Any help will be appreciated

This thread is about Opencart 1.4.9. I don't have a commercial Export/Import tool, ours is free under the GPL3 license. If you got it from someone else, ask for the full source code, as per the GPL3 license, before someone can help you here.

Thank you. I have yours installed and the same thing is happening. Am I missing a php or apache2 memory configuration? If I cut the products down to a handful the product works.

sys1rak wrote:Thank you. I have yours installed and the same thing is happening. Am I missing a php or apache2 memory configuration? If I cut the products down to a handful the product works.

Check the server's error_log and OpenCart's system/logs/error.txt. Most likely an issue with your PHP settings, especially the memory_limit, see embedded readme.txt for details.

thank you - seems to work better, however. the instructions are not clear to me. I want to be able to export all this information, modify it (products too) and add to it if needed. Then I want to upload again. Can I do that?
I am updating products and uploading them and getting a duplicate key message? How do I fix that?

Thank you

Thanks J,

I followed the instructions within the readme for opencart-1-4-9-x-27-export-import.zip but it still will not appear? Ideas?

... Since having this issue I have installed a TEST site that I was planning to migrate to that is running Opencart v1.5.4 alongwith opencart-1-5-4-x-export-import-12.zip - I can confirm that this installation is working fine.

----> So in summary I have another instance (TEST) of Opencart running V1.5.4 working fine with the the exporter)

---->> But original (PROD) version running 1.4.9.3 not working

--------->>> Any recommendations you can provide would be greatly appreciated!

Nvm