Excel import duplicating products version 2.65

we are currently building a 2.65 solution. We imported the products using the excel import. Couple of problems.

Worked great up until it couldnt find a photo then just stopped importing. However no notice to say it failed on the import of the rest.

So we found the reason for the failure. An image was named .jpg.jpg so it couldnt find it and didnt upload the rest of the products.

So we corrected the issue and uploaded the excel sheet again. Same SKU's, same names etc etc. However it still duplicated all fo the products. I thought it was supposed to overwrite anything with the same SKU? This will be a problem for clients who use the spreadsheet to update prices. Is it supposed to overwrite?

Not to much of a problem for us as I will just delete them from the DB and reupload them. But for the clients will be an issue.

Is there something we missed to make sure it didnt duplicate the products?

thanks
Simon

Simon.UK wrote:
I understand that the key for creating or replacing products is Product_Id and not the SKU. Therefore the best when updating prices/products is to export to Excel, change prices, add new products folowing the Product_id sequence and import the table

There is no 'Product_id ' column in the excel spreadsheet download. Thats why I thought it was on the SKU? Should it be on there?

Interestingly the XML export does have a product ID. I think this may just be a small oversight on what is exported/imported in the EXCEL export/import.

Might be beyond me to fix that (and by might I mean definately) unless it is easie to change what is exported?

btw - Thanks for the quick reply above :)

Simon.UK wrote:
I am sorry. I was misinformed by the developer. Then I am as confused as you. Then my best guess, after checking demo admin export, is that maybe the key are the product+product_variant names, but I guess you did your import keeping both names as they were exported. Right?

The key for matching is the SKU.   I've used it many times including 2.65.

I suspect the problem could be the "data type" of the column

https://www.nopcommerce.com/boards/t/14503/importing-excel-updating-products.aspx

Yeah we are not changing a thing. Except a couple of prices.

The instructions are download the excel sheet make changes and then upload. Do the clients now neeed to add a ' to the sku everytime?

also is this from the other post something i need to find and change in the source code or is this an explanation of why its not working?

---------- (courtesey of New York)
SKU

                    var productVariant = _productService.GetProductVariantBySku(sku);
                    if (productVariant != null)
                    {... update
                    else
                    {... insert
----------

For the excel function to work or not work it needs a column that is recognised easily by a key. Download the product list make changes and then upload.

We are using Microsoft excel so it should be fine? Right???

Just rememberd a similiar problem in 2.3
https://www.nopcommerce.com/boards/t/12492/importing-products-and-images-nop-22-not-working.aspx?p=2
we ended up just deleteing the whole product list from the database and reimporting them using the excel.

In this post NY said to just make sure the SKU was the same, but it didnt work for us then either.

"(ver 2.x)
Without any modifications to ImportManager code, you must supply all the columns, or an error will results.
If SKU field is populated and matches an existing record's SKU, it will update, otherwise it will insert. "

The code above (...GetProductVariantBySku(sku); ...)  is just to show that the lookup is indeed by SKU.

The leading single quote is necessary if you have all digits in SKU column in the first n rows of the sheet (I forget exactly what n is - maybe 7), and then have alphanumeric SKUs later on.  This has to do with the way Excel tries to determine the data type of the column (Text, Numeric, etc.).   I'm not sure if this problem still exists with 2.65, since it now uses a different Excel library than what was used before.   It would be best to just try importing a single record with a known SKU with leading ' and see what happens.  Check to be sure SKU does not have leading or trailing spaces.  I can't think of anything else to try except to debug it in Visual Studio.