Page 1 of 1

Overwrite attributes

Posted: 17 Nov 2017, 19:05
by Bevi
Laravel 2017.10

Hello Sir, :)

I don't understand why it is not possible to overwrite attributes through csv files.
The very first time I've imported the csv, I had no problem with it.
Now I had to manually remove the attributes, because I was getting this error:

Code: Select all

Unable to import product with code "TOPR2420": An exception occurred while executing ' INSERT INTO "mshop_product_list" ( "parentid", "typeid", "domain", "refid", "start", "end", "config", "pos", "status", "mtime", "editor", "siteid", "ctime" ) VALUES ( 89776, 2, 'attribute', '10518', NULL, NULL, '[]', 0, 1, '2017-11-17 18:40:48', 'aimeos:jobs', 1, '2017-11-17 18:40:48' ) ': SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-attribute-10518-2-89776' for key 'unq_msproli_sid_dm_rid_tid_pid'
What am I supposed to do? I must overwrite attributes through csv, because I receive new price lists every month, and I do map them through a macro I made. :roll:

Check this image, you can see that I get the error the second time I enter the command. Notice that I didn't modify the csv.zip file. :?
Image

Re: Overwrite attributes

Posted: 18 Nov 2017, 11:09
by aimeos
Updating/overwriting existing records should be no problem. The importer is built for that.
Do you get that error for every of the 5275 lines in your CSV that the importer was not being able to update?
Can you post a minimal file that generates the error? If it contains sensitive data, change them to something different.

Re: Overwrite attributes

Posted: 18 Nov 2017, 16:14
by Bevi
aimeos wrote:Updating/overwriting existing records should be no problem. The importer is built for that.
Do you get that error for every of the 5275 lines in your CSV that the importer was not being able to update?
Can you post a minimal file that generates the error? If it contains sensitive data, change them to something different.

Hi again, Sir
Ok, here's my whole xlsX file. I deleted the values inside the price column, for privacy. :roll:
Don't worry, there is no vba code inside the file, so you won't think you're going to be infected by some virus. ;)
https://mega.nz/#!7o8SXS7R!5zvl-Cud_Av ... 9-C9cfVdM

Re: Overwrite attributes

Posted: 19 Nov 2017, 13:32
by aimeos
Please attach a CSV file with a minimum amount of lines only that produces the error.

Re: Overwrite attributes

Posted: 23 Nov 2017, 13:28
by Bevi
Ok, here's 1 single row csv file that produces the error, and the mapping file as well.
https://www.mediafire.com/file/sxm8fsuo ... Aimeos.zip

Re: Overwrite attributes

Posted: 27 Nov 2017, 10:45
by aimeos
Mapping and the line in the CSV file are OK. After creating the referenced category and all attribute types, it imports without problems you can execute the job several times without an error:

Code: Select all

./artisan aimeos:jobs product/import/csv default
If there are any errors, you can see them in the madmin_log table.

Here's the configuration we've used for testing in ./config/shop.php:

Code: Select all

	'controller' => [
		'jobs' => [
			'product' => [
				'import' => [
					'csv' => [
						'container' => [
							'type' => 'Directory'
						],
						'location' => base_path('import'),
						'skip-lines' => 0,
						'mapping' => [

						'item' => [

								0 => 'product.code', // e.g. unique EAN code

								1 => 'product.label', // UTF-8 encoded text, also used as product name

								2 => 'product.type', // type of the product, e.g. "default" or "selection"

								3 => 'product.status', // enabled (1) or disabled (0)

							],

							'text' => [

								4 => 'text.type', // e.g. "short" for short description

								5 => 'text.content', // UTF-8 encoded text

								6 => 'text.type', // e.g. "short" for short description

								7 => 'text.content', // UTF-8 encoded text

								8 => 'text.type', // e.g. "short" for short description

								9 => 'text.content', // UTF-8 encoded text

								10 => 'text.languageid',

								11 => 'text.type', // e.g. "short" for short description

								12 => 'text.content', // UTF-8 encoded text

								13 => 'text.languageid',

								14 => 'text.type', // e.g. "short" for short description

								15 => 'text.content', // UTF-8 encoded text

								16 => 'text.languageid',

								17 => 'text.type', // e.g. "short" for short description

								18 => 'text.content', // UTF-8 encoded text

								19 => 'text.type', // e.g. "short" for short description

								20 => 'text.content', // UTF-8 encoded text

								21 => 'text.type', // e.g. "short" for short description

								22 => 'text.content', // UTF-8 encoded text

								23 => 'text.type', // e.g. "short" for short description

								24 => 'text.content', // UTF-8 encoded text

								25 => 'text.type', // e.g. "short" for short description

								26 => 'text.content', // UTF-8 encoded text

								27 => 'text.type', // e.g. "short" for short description

								28 => 'text.content', // UTF-8 encoded text

								29 => 'text.type', // e.g. "short" for short description

								30 => 'text.content', // UTF-8 encoded text

								31 => 'text.languageid',

								32 => 'text.type', // e.g. "short" for short description

								33 => 'text.content', // UTF-8 encoded text

								34 => 'text.languageid',

								35 => 'text.type', // e.g. "short" for short description

								36 => 'text.content', // UTF-8 encoded text

								37 => 'text.languageid',

								38 => 'text.type', // e.g. "short" for short description

								39 => 'text.content', // UTF-8 encoded text

								40 => 'text.languageid',

								41 => 'text.type', // e.g. "short" for short description

								42 => 'text.content', // UTF-8 encoded text

								43 => 'text.languageid',

								44 => 'text.type', // e.g. "short" for short description

								45 => 'text.content', // UTF-8 encoded text

								46 => 'text.languageid',

								47 => 'text.type', // e.g. "short" for short description

								48 => 'text.content', // UTF-8 encoded text

								49 => 'text.languageid',

								50 => 'text.type', // e.g. "short" for short description

								51 => 'text.content', // UTF-8 encoded text

								52 => 'text.languageid',

								53 => 'text.type', // e.g. "short" for short description

								54 => 'text.content', // UTF-8 encoded text

								55 => 'text.languageid',

								56 => 'text.type', // e.g. "short" for short description

								57 => 'text.content', // UTF-8 encoded text

								58 => 'text.languageid',

								59 => 'text.type', // e.g. "short" for short description

								60 => 'text.content', // UTF-8 encoded text



							],

							'media' => [

								61 => 'media.type',

								62 => 'media.url', // relative URL of the product image on the server

							],

							'price' => [

								//32 => 'price.quantity', // the quantity the price is valid from (for block pricing)

								63 => 'price.type',

								64 => 'price.value', // price with decimals separated by a dot, no thousand separator

								65 => 'price.currencyid',

								66 => 'price.taxrate', // tax rate with decimals separated by a dot

							],

							'attribute' => [

								67 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								68 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								69 => 'attribute.label',

								70 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								71 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								72 => 'attribute.label',

								73 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								74 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								75 => 'attribute.label',

								76 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								77 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								78 => 'attribute.label',

								79 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								80 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								81 => 'attribute.label',

								82 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								83 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								84 => 'attribute.label',

								85 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								86 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								87 => 'attribute.label',

								88 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								89 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								90 => 'attribute.label',

								91 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								92 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								93 => 'attribute.label',

								94 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								95 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								96 => 'attribute.label',

								97 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								98 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								99 => 'attribute.label',

								100 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.

								101 => 'attribute.code', // code of an existing attribute, new ones will be created automatically

								102 => 'attribute.label',

							],
							/*
							'product' => [

								14 => 'product.code', // e.g. EAN code of another product

								15 => 'product.lists.type', // e.g. "suggestion" for suggested product

							],

							'property' => [

								16 => 'product.property.value', // arbitrary value for the corresponding type

								17 => 'product.property.type', // e.g. "package-weight"

							],
							*/
							'catalog' => [

								103 => 'catalog.code', // e.g. Unique category code

								//19 => 'catalog.lists.type', // e.g. "promotion" for top seller products

							],
							'stock' => [

								104 => 'stock.stocklevel', // e.g. Unique category code

							],

						]
					]
				]
			]
		]
	],

Re: Overwrite attributes

Posted: 27 Nov 2017, 15:21
by Bevi
Is there a way to reset every product related table in the database?
I mean, without having to reinstall the whole thing. :mrgreen:

Re: Overwrite attributes

Posted: 27 Nov 2017, 22:05
by aimeos
You can delete all entries in the mshop_product table using a proper SQL statement

Re: Overwrite attributes

Posted: 29 Nov 2017, 17:37
by Bevi
aimeos wrote:You can delete all entries in the mshop_product table using a proper SQL statement

So, will "DELETE FROM mshop_product;" do the job?
There are no other relations I should consider? :roll:

Re: Overwrite attributes

Posted: 29 Nov 2017, 23:49
by aimeos
The mshop_product_list and mshop_product_property records are deleted automatically due to the foreign key constraints. You should also delete the images, prices and texts of the products:

Code: Select all

DELETE FROM mshop_media WHERE domain='product'
DELETE FROM mshop_price WHERE domain='product'
DELETE FROM mshop_text WHERE domain='product'