SKU duplicity when importing

Posted: June 21, 2019 at 6:51 AM Quote #238373
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
This post/answer is useful
2
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: June 23, 2019 at 9:10 AM Quote #238570
I was wondering why I have multiple items after import and had to delete them manually. Now I know about this bug. Thanks.
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: June 24, 2019 at 1:50 AM Quote #238588
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
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.SelectSystems.com.au
Select Systems International is a computer systems technology solution developer and integration service provider.
Posted: June 24, 2019 at 5:26 AM Quote #238596
Yidna wrote:
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.
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: June 24, 2019 at 6:01 AM Quote #238601
Oh ok you are right you can't have duplicate SKU's now
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 ?
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.SelectSystems.com.au
Select Systems International is a computer systems technology solution developer and integration service provider.
Posted: June 24, 2019 at 6:40 AM Quote #238607
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):

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
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: June 24, 2019 at 11:11 PM Quote #239648
starlogic wrote:
... 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);
This post/answer is useful
-1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.noptools.com
Posted: June 25, 2019 at 2:11 AM Quote #239656
New York wrote:

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.
This post/answer is useful
3
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Premium support services
  • Dedicated premium support services provided by core developers are intended for persons who run mission critical websites, work on projects with tight deadlines, or want to get dedicated support.
Professional services
  • Want to open a new store? Want to take your store to the next level? Need a custom extension? We can customize nopCommerce to fit your store perfectly. Request a quote to get started.
eCommerce CONFERENCE 2019
Learn more