-2

I am trying to upload an .xlsx file, with products, prices etc, in an Opencart e-shop. and, I am getting the following error:

Notice: Error: Column count doesn't match value count at row 1 Error No: 1136 INSERT INTO oc_product (product_id,quantity,sku,upc,ean,jan,isbn, mpn,location,stock_status_id,model,manufacturer_id,image,shipping,price,points, 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) VALUES (5546,999,'','','','','','6.00340.00','',6,'6.00340.00',13, '',1,785,0,'2016-07-27 17:29:43','2016-07-27 17:29:43','2016-07-27',7,7,1,1, 0,0,0,0,0,'1','1','1','1'); in /var/www/vhosts/......

I have checked the .xlsx file for errors, or mistyped things or things like that. but, I cannot find the problem that causes the error.

Any ideas why? Your help would be really appreciated.

HDP
  • 4,005
  • 2
  • 36
  • 58
nik-OS
  • 327
  • 1
  • 4
  • 14

1 Answers1

1

I see an extra value in your insert statement, I aligned the commas in your query:

INSERT INTO `oc_product` 
(`product_id`,`quantity`,`sku`,`upc`,`ean`,`jan`,`isbn`      ,`mpn`       ,`location`,`stock_status_id`,`model`     ,`manufacturer_id`,`image`,`shipping`,`price`,`points` ,`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`) VALUES 
(5546        ,       999,   '',   '',''   ,''   ,''          ,'6.00340.00',''        ,6                ,'6.00340.00',13               ,''     ,1         ,785    ,0        ,'2016-07-27 17:29:43','2016-07-27 17:29:43','2016-07-27'    ,       7,                7,       1,             1,        0,      0,      0,       0,                0,         1' ,       '1',      '1',      '1');
Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41
  • The query was automatically generated when i am receiving the error. I did not write code, just completed an .xlsx file to be uploaded. – nik-OS Jul 28 '16 at 12:19
  • @NickH then some of cell values has a comma `,` as value, you must change it to another character – Ivan Cachicatari Jul 28 '16 at 12:21
  • Thank you very much for the replies, but, no, commas are not the problem. They can be "read" fine. Something else i cannot figure out causes this error. – nik-OS Jul 28 '16 at 12:26
  • OK, it seems opencart has a bug in that import module.. can you found the .php file thats do the import process? – Ivan Cachicatari Jul 28 '16 at 12:29
  • I do not have access to the .php file unfortunately. It's the Import/Export Tool of Opencart. But the thing is, that i used it successfully yesterday, without any problems. That is really strange. – nik-OS Jul 28 '16 at 12:31
  • If you can compare both files, the success and fail xls, maybe you can found the source of error. – Ivan Cachicatari Jul 28 '16 at 12:37
  • I can not find any solution till now, thank you although once again for the replies – nik-OS Jul 28 '16 at 12:52
  • 1
    @NickH well, technically the answer is correct. If you don't have any influence on the script producing the sql, you might be in trouble anyway ... or you should read up on the manual, maybe you had an extra column in your xlsx? – Jakumi Jul 28 '16 at 15:20
  • @Jakumi, finally the problem was solved.There was a bug with commas and dots in price column of .xlsx file. I fixed it and the products were uploaded correctly. – nik-OS Jul 29 '16 at 16:02
  • 1
    @Jakumi great!... I mentioned that in my second comment... BTW feel free to mark my answer as accepted :) – Ivan Cachicatari Jul 29 '16 at 17:41
  • @IvanCachicatari I had the feeling OP read past it, so I reiterated ;o) – Jakumi Jul 29 '16 at 17:59