OpenCart Community

Thanks EricP,

Appreciate your help. I'll try it that way round. I have to say I think that OpenCart is a great solution, I just struggle getting my head around stuff I guess.

I'll leave my feedback on the Import/Export extension once I've had a play (and hopefully uploaded all the products).

Limegreen.

Not having too much luck - keep getting this error message in one form or another.

This really doesn't seem too straight forward???

Error: Unknown column '$5.03' in 'field list'
Error No: 1054
INSERT INTO `product` (`product_id`,`quantity`,`sku`,`location`,`stock_status_id`,`model`,`manufacturer_id`,`image`,`shipping`,`price`,`date_added`,`date_modified`,`date_available`,`weight`,`weight_class_id`,`status`,`tax_class_id`,`viewed`,`length`,`width`,`height`,`length_class_id`,`sort_order`,`subtract`,`minimum`,`cost`) VALUES (000558,1,'0','0',5,' ',0,'',1,0.00,NOW(),NOW(),NOW(),0,0,1,0,0,0,0,0,'1','0','1','1',$5.03);

What does this mean?

Any help would be greatly appreciated.



Regards



Limegreen

Hi Limegreen,

It requires the exact headers, columns, with exacly the same worksheets, that you see in a XLS file created by an Export.

Having said that, importing 20000 products will be very memory hungry. So unless you have a dedicated web server just for yourself, and not the usual shared web host space, you should only run this Import on a local PC which you can configure to a high enough PHP memory_limit. You can then always upload your updated database via PHPMyAdmin (local export, remote import).

Adding 20 000 products manually into a spreadsheet file is a lot of work. If your supplier already has a special datafeed (e.g. XML or CSV) I could write you a special Import function for this. If interested, send me a PM.

I was reviewing the code within admin/model/tool/export.php and my suspicions were correct. You're loading the entire file into an array, and then wrapping the initial deletes of every table within the spreadsheet and then the subsequent insert of every item in that array in one transaction before the commit. This takes up a huge amount of memory. I can understand why this would work locally where you have tons of resources but not on shared web hosts where resources are tighter. I cannot imagine this working with 21,000 items on a shared host.

I am going to add a commit after the initial delete and see if that fixes my problem. Since I am copying this from an export of the master accounting system, I have no worries if the initial delete works but the second part fails.

After playing around with it and adding some logging, and some intentional bugs for good measure, I discovered that the upload is failing silently before it even gets to the call to function upload( $filename ). This host (JustHost) has a pretty sensitive firewall, and I had some intermittent problems with installing software because their firewall interpreted the massive FTP stream as a potential DDoS attack. This might be why the smaller files work and that nothing seems consistent. It might think the file transfer is huge, and since I'm testing all from my home PC, it might interpret me as a hacker and stop the upload. What a pain.

If that's the case, how difficult would it be to add a function that would take a file via FTP instead of browser upload?

The official answer from JustHost is this:

I have just renamed php.ini files in your public_html folder and in admin folder .
This seems fixed the problem .
Please test your site now .

Looks like you may have to find another webhost. It is quite possible that your require a PHP memory_limit value of more than 128M.

Now that they've renamed the php.ini files, I've had no issues.

Hi JNeuhoff,

I added a few products then did an export to give me the template, then tried uploading 1800 products, got no errors the message says "Success: You have successfully imported your categories and products!" but where are the products? It's like it did nothing? xls filesize is only 700kb's in products nothing shows and front and back office.
Latest version 1.4.9.1 using your latest export/import tool, the server I'm hosted on can definitely handle this software.

There could be several reasons for your products not showing up.

Are your status enabled and store_ids fields correctly set in your 'Products' worksheet?

status enabled is all good, store_ids is 0 just used the same store id for 1800 prods, as five products in the template used 0.

Send me a backup copy of your database and your XLS file so I can take a look at it.

Do your products show up in the admin's backend (Catalog > Products) ?

Are your products associated with correct categories?

Hi,

Here is Database file
http://www.mediafire.com/?9xbgh4l92grt6fj
Here is XLS file
http://www.mediafire.com/?dcz9187zb8picnd

I will manually add everything using sql program but for future it would be good if this program works.

Does your module, in the PHP code does it check for existing SKU, if SKU exists does it update quantity and price, if not exist disable product? Because I get an excel sheet from my supplier once a week and if products have been removed from excel it means either they are no longer available or discontinued.

Placing all fields with weight or filling in all categories with correct numbers still produces 0 products in both front end and back end admin.

I'm getting my host to monitor what happens when the import goes through to see if they find anything, i'm hoping to get this working as OpenCart is my last solution for my small IT business. I was using Prestashop but the shipping don't work correctly with weight and state and so on.

Thanks. I couldn't use your DB file because it was not in SQL format.
However, II took a quick look at your XLS file. It doesn't show the product_id column on the 'Products' worksheet, it is probably there, but hidden from view, or the XLS file is corrupted. Also, most of your products aren't associated with any categories, which they should.

Excel with everything unhidden
http://www.mediafire.com/?lmjt2jizhljjgut
SQL file
http://www.mediafire.com/?9rz147rtw3u1z1t
Even if I put categorie numbers in all fields, it still results in 0 products. Filling in categories makes no difference on my end.

Do I have to manually create my product ID's?

All fixed the XLS requires the ID field to be filled in with manual numbers, I originally thought the system would Auto generate numbers. thank you.

Hi

Sorry if this is hijacking the thread but I am having a frustratingly similar problem. Cannot get a spreadhseet to import without the error message:

Error: Unknown column 'PL80BLACK' in 'field list'
Error No: 1054
INSERT INTO `product` (`product_id`,`quantity`,`sku`,`location`,`stock_status_id`,`model`,`manufacturer_id`,`image`,`shipping`,`price`,`date_added`,`date_modified`,`date_available`,`weight`,`weight_class_id`,`status`,`tax_class_id`,`viewed`,`length`,`width`,`height`,`length_class_id`,`sort_order`,`subtract`,`minimum`,`cost`) VALUES (PL80BLACK,50,'PL80BLACK','0',7,' ',14,'data/PL80BLACK.jpg',1,102.12,'26/11/2010','26/11/2010','26/11/2010',0.46,2,0,9,1,0,0,0,'1','0','0','1',69);

Funny thing is I have a spreadsheet of 160 products that loads fine. I then replace the product and category information with new categories and approx 150 products (but leave the headers in the spreadsheet alone) and try and upload it but get the message above. So I know the import function works, just not with the spreadsheet of new products I am tryinmg to load. So would seem to rule out memory, system config issues etc.

Error log doesn't really seem to tell me much but am assuming that there is something in the data itself (weird characters?) that is throwing the import off?

Any ideas are appreciated....cheers

Richard

Hi Richard,

Please only use unique numeric values for the product_id or category_id.

I knew it would be me! That solved the problem fine - thanks J