Export/Import tool for OpenCart 1.5.5.1

Hi,

I thought id start a new post as not sure where to put it!

I have many mods all working well. One being Import/export free.

Everything works fine with no errors as such...

When i upload an excel file that has 712 products with 2600 options and 140 cats all works well.

I added another 155 products to the excel file and uploaded. No errors but the browsers stops doing anything and goes to w white page.

I can see this is a memory issue so i upped my servers ram to 10gb but the same issue happens.

The mod notes say:
For example, for a store that has 13,500 products and 300 categories:

XLS File Size: 9 to 10MB
Memory Usage Export: up to 13MB
Memory Usage Import: up to 355MB

Which works fine with the following PHP settings:

memory_limit 512M
post_max_size 16M
upload_max_filesize 16M

I changed my php to:
memory_limit 800M
post_max_size 50M
upload_max_filesize 50M

Just to see if it made any difference and it didn't. Any suggestions would be great!

Matty.

Go higher -- execution, mem, post, upload maxima, to 300, 512M, 999M, 998M. (Note that 800 is not among normal binary steps and that 512M works.) See varied nuances in the main thread. The php settings that are amply high enough for the tool's potential demands, e.g., at http://forum.opencart.com/viewtopic.php ... 11#p444711 , usually let the tool work, the exceptions seem to owe to other causes.

In your own instance, mattybulman, you seem to have reset those on the server by way of the control panel, but perhaps not. If the server honors your two php.ini files, which are set low, then reset at account level and disarm the two files. Alternatively, up the ante in the two php.ini files. It would be best, if using the two files, to set the server in the account, so that they fully agree. If you cannot reset in the account, then the server might honor elevated settings in php.ini anyway.

Hi, Ok i have upped the numbers in the global .ini file within WHM and also in the cpanel of the OC software. I still get the same issue. Unless im missing something im not a "server guy" so im going on my limited knowledge.

Then if you didn't edit the two php.ini files in OC (which have low default values and not all of them anyway), they are being honored, so just disarm them (rename to xphp.ini or php.ini.txt, whatever, to silence them).

I just noticed something. OC has an admin panel that you log into (not a "cPanel"), the host itself has the cPanel (or Plesk or other equivalent). If you were in "cPanel" you were in the host. The admin panel of OC can't reach those settings, so you must have been in the host instead. Ya done good.

Yes that's correct. So i have been in the WHM and set global server to the above and higher.. Have checked cpanel and there is no options there to change (That i can see.) I have disabled the the 2 .ini files like above and get the same.

Quite frustrating as i have spent a longggg time working on this and now cant really use haha!

Try phpinfo.php (back in a moment, getting link) to see what the server thinks is going on.

The tiny one-line utility phpinfo.php (here: http://forum.opencart.com/download/file.php?id=18153) can be uploaded into OC (or your account, public) and addressed in the browser. Put it in both your OC root and your /admin/. Address it as usual, but after domain/whatever/phpinfo.php (and domain/whatever/admin/phpinfo.php).

It will show php version and settings that are in effect. It is informational, it is safe, it does not disclose anything hazardous, it is a standard tool. Since phpinfo.php tells all, and cannot tell a lie unless the server did, you can see your version and its settings. The server's own 2 kb master php.ini contributes most of what you will see. Your own few settings also affect the roster, if they take.

WHOOP! Its working. Thank you so much for your help. Saved me months!!!

The settings that did work are:
memory_limit = 512M;
post_max_size = 999M;
max_execution_time = 300;
upload_max_filesize = 998M;

I put these in php.ini in the root shop location and in admin. I can now import 21k products and 15k options with no issues.

And this was with only 2gb of ram. I have not tested above this but ill just add ram if i need more!

Again thank you!

Welcome. Mind your php.ini settings, there are reasons for having execution and memory limits as well as for raising them. The php.exe sweet spot is generally 5.3.x (I can elect any 5.y.x version, and settled upon 5.3.x for all OC versions). The 2 gb should suffice for quite a while but doubling that will be moderately affordable anyway. (In retrospect the widespread relief when we finally had all of 64 kb with no gui to support is amusing. At the time 2 gb that would even fit anywhere wasn't even engineered, yet, and would have cost what an early 1 mb hard drive the size of a washing machine cost, one megabuck plus change, and there was no such thing as an on-line shopping cart.)

Hi all,

As above everything works fine. Just a little niggle now...

I have added another 500+ products with options etc.

I can see all the options are still in the online shop. When i export it does not pull them into the excel spreadsheet, so if i upload again the options are not appended to the products.

There are no errors it downloads the file successful. My ini file still has the same settings as per my last message on this post.

Any ideas?

Thanks

You have option fields (columns) that are not being exported? What happens if you manually force the spreadsheet to match the database option fields? Or is there another options table that the tool is not even seeing? Do ALL of the tables have the same db prefix (none, oc_, or [other]_?

https://www.dropbox.com/s/w3i6mt2teg3b6 ... tled-1.jpg

I'm not entirely sure now as i have done some more tests. See Image link above.

I have a data file which has options and products. 1 that i can see for example has 3 options which is assigned to the correct product id in the spread sheet.

I upload the file and it shows a white page which is what it used to do when i ran out of mem. However for the product above i see the options are correct in the options section and i see the product. When i view the product on the web page it does not associate the options with the product (Even though they are there and do match op.)

I'm now confusing myself lol!

The below mem settings were fine until i added yet more products. I obviously need to make the settings higher again to not get an error which i am getting now.

I have concluded that as the upload goes to a white page it does not finish executing the script to join the options and files together? Im not sure though!

What would you recommend i change the settings too?

Current Settings are:
memory_limit = 512M;
post_max_size = 999M;
max_execution_time = 300;
upload_max_filesize = 998M;

The options have a quantity of zero. Are you sure by looking at the products in admin the options don't show up?
Mike

EDIT: I realized I didn't provide enough info to be completely useful. If you get back to being able to upload your products without error and you still can not see the product options on your storefront, check the product in the admin section and see if it has a quantity attached to the option. If it is zero the option will not show up in the storefront.

Dinna notice post. These are okay:
Current Settings are:
memory_limit = 512M;
post_max_size = 999M;
max_execution_time = 300;
upload_max_filesize = 998M;

Hi Butte,

These settings are causing the white page which i had before i changed to the above so wondering if i can up them again?

Initially try changing just the 3rd line of these, to:

memory_limit = 512M;
post_max_size = 999M;
max_execution_time = 3000;
upload_max_filesize = 998M;

A piddly +500 products should not be swallowing the memory in one gulp.

Do you have php's mysqli extension enabled? Can you enable it? Can support enable it? (That'll depend upon the hosting setup.)

In another vein, do your Apache http logs and your session timestamps show concurrent onslaughts by rogue robots or by benign ones that went berserk? Both happen. Do you have a concurrent feed that went nuts?

Dreaded me again! Am now running on 1.5.6.1 all works fine. Same issue again with import/export. Loads and then goes to a white screen. Lots of products are imported but i see a lot of options are not there. I'm using the latest version of the import export which is compatible with OC version. I get the following error while inserting:

2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1584
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1597
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1623
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1636
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1649
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1662
2014-02-27 9:21:15 - PHP Unknown: Only variables should be assigned by reference in /home/sp/public_html/vqmod/vqcache/vq2-admin_model_tool_export.php on line 1675
2014-02-27 9:21:21 - PHP Notice: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0,,'',,'',,'','','0','','')' at line 1<br />Error No: 1064<br />INSERT INTO `oc_product_option_value` (`product_option_value_id`,`product_option_id`,`product_id`,`option_id`,`option_value_id`,`quantity`,`subtract`,`price`,`price_prefix`,`points`,`points_prefix`,`weight`,`weight_prefix`,`ob_sku`,`ob_sku_override`,`ob_info`,`ob_image`) VALUES (945,258,535,34,928,,0,,'',,'',,'','','0','',''); in /home/sp/public_html/system/database/mysql.php on line 50

Apparently the
'0'
should be
0
instead.

On seven lines it fussed, "PHP Unknown: Only variables should be assigned by reference" with consequent syntax error and in 15th field that is the only number that is different in format from the others.

Hi,

Thanks for the reply. Where should i be changing this?

In host control panel's phpMyAdmin FIRST Export a backup of your database, then in the main pane bring up the oc_product_option_value table. The field can be found by looking left to right through the VALUES for the 0 which is in this position: 945,258,535,34,928,,0,,'',,'',,'','','0','',''. You may need to change the field TYPE in the top row to an integer.