1. nopCommerce version
Version: 4.20
2. Expected behavior
When imported products from Excel with duplicated SKUs it should not import the same item multiple times
3. Actual behavior
I have Excel file with products (from my supplier) with some multiple rows for the same SKU. (From 2000 items I have 65 duplicates.) Each row has a different category which should go this item under. (It is because one item fits under multiple categories.) When I import the file instead of either showing an error or importing those items with multiple categories the system imports the same SKU more than one time. This generates an error when looking at the SKU in Products
4. Steps to reproduce the problem
Create Excel with 2 rows with the same SKU and import it.
5. Any private modifications you made to your nopCommerce
none
SKU duplicity when importing
MVP
Certified Developer
- 3943
- 35192
- 2/8/2010
- Australia
The system allows you to have two products with the same SKU so its not really an bug as such
You need to massage the data in your excel file and not have the duplicates before you import
If you want to add the one product to multiple categories when you import you can enter multiple categories in the excel file separated by a colon
i.e.
Categories Column
Software;Books;Electronics
You need to massage the data in your excel file and not have the duplicates before you import
If you want to add the one product to multiple categories when you import you can enter multiple categories in the excel file separated by a colon
i.e.
Categories Column
Software;Books;Electronics
- 30
- 330
- 6/23/2016
- United States
Yidna wrote:
How system knows when updating products from an Excel which item to update? Plus if this is by design as you said why is there an error in admin > products: "The entered SKU is already reserved for the product... ". Also: When I search in admin for SKU direct I get only one item.
Sorry, I do not think you are right. This is a bug. I the system does not like multiple items with the same SKU it should not allow it.
The system allows you to have two products with the same SKU so its not really an bug as such
How system knows when updating products from an Excel which item to update? Plus if this is by design as you said why is there an error in admin > products: "The entered SKU is already reserved for the product... ". Also: When I search in admin for SKU direct I get only one item.
Sorry, I do not think you are right. This is a bug. I the system does not like multiple items with the same SKU it should not allow it.
- 30
- 330
- 6/23/2016
- United States
Yidna wrote:
So if the import has the same SKU as an existing product then the import just updates that product - It does not creating another record ?
No, that is working fine - it updates the record with the same SKU - but only the first one though. Just when the initial import has multiple Excel rows with the same SKU it creates duplicates. I looked up the import code and it checks only if the SKU is already in Product table (in that case it updates it). It does not check if the same SKU is in previous rows in Excel and imports it multiple times.
I think that Product table should have unique index on SKU field.
BTW: here is a query which returns Product IDs for the same SKU and in the same Category - real duplicates. Maybe somebody would need that (I had 44 such SKUs from 1,700 total):
So if the import has the same SKU as an existing product then the import just updates that product - It does not creating another record ?
No, that is working fine - it updates the record with the same SKU - but only the first one though. Just when the initial import has multiple Excel rows with the same SKU it creates duplicates. I looked up the import code and it checks only if the SKU is already in Product table (in that case it updates it). It does not check if the same SKU is in previous rows in Excel and imports it multiple times.
I think that Product table should have unique index on SKU field.
BTW: here is a query which returns Product IDs for the same SKU and in the same Category - real duplicates. Maybe somebody would need that (I had 44 such SKUs from 1,700 total):
SELECT
STUFF ((SELECT ',' + cast( p.Id as nvarchar)
FROM dbo.Product p
WHERE dbo.Product.Sku = p.Sku
FOR XML PATH('')),1,1,'') as IDs,
dbo.Product.SKU, dbo.Category.Name as CategoryName
FROM dbo.Product INNER JOIN
dbo.Product_Category_Mapping ON dbo.Product.Id = dbo.Product_Category_Mapping.ProductId INNER JOIN
dbo.Category ON dbo.Product_Category_Mapping.CategoryId = dbo.Category.Id
GROUP BY dbo.Product.Sku, dbo.Category.Name
HAVING (COUNT(dbo.Product.Id) > 1)
ORDER BY dbo.Product.Sku
MVP
- 11320
- 100942
- 5/22/2011
- United States
starlogic wrote:
It was done as a ...
... the import code and it checks only if the SKU is already in Product table (in that case it updates it). It does not check if the same SKU is in previous rows in Excel and imports it multiple times.
...
...
It was done as a ...
//performance optimization, load all products by SKU in one SQL request
var allProductsBySku = _productService.GetProductsBySku(metadata.AllSku.ToArray(), _workContext.CurrentVendor?.Id ?? 0);
- 30
- 330
- 6/23/2016
- United States
New York wrote:
It was done as a ...
Yes, this loads all products currently in database and later in code compares to metadata from Excel:
And the 2 identical SKUs are in metadata not in SQL which means the code highlighted by you does not have them yet a that is why it inserts it twice. This is exactly the place where the code logic is incorrect.
It was done as a ...
//performance optimization, load all products by SKU in one SQL request
var allProductsBySku = _productService.GetProductsBySku(metadata.AllSku.ToArray(), _workContext.CurrentVendor?.Id ?? 0);
Yes, this loads all products currently in database and later in code compares to metadata from Excel:
var product = metadata.SkuCellNum > 0 ? allProductsBySku.FirstOrDefault(p => p.Sku == metadata.Manager.GetProperty("SKU").StringValue) : null;
var isNew = product == null;
product = product ?? new Product();And the 2 identical SKUs are in metadata not in SQL which means the code highlighted by you does not have them yet a that is why it inserts it twice. This is exactly the place where the code logic is incorrect.