Thread: re-categorizing products via import file

I'm in the middle of re-categorizing my products and noticed when I did an excel import, the product exist in 2 locations now:
Bags and Cases/Bags, Totes, Backpacks (OLD)
Bags and Cases/Cinch & Backpacks (NEW)

The only thing different is the CATEGORY1 in the import file. What can I do to re-categorize the products?

How many products are in this situation? If a small number you could manually uncheck the old category for each product in the Product Mappings tab, category section.

If it's a large number of products, a SQL statement will be your best bet. Do you have access to the sql server your storefront uses? Secondly, is there a predictable way to determine which category/product mappings you'd want to remove?

I have close to a thousand products with access to sql and it would just take a lot of time to figure it via sql. I was hoping that we can do this via the import file. Is that your understanding that this only be done via sql?

This should be fairly simple to do via SQL.

Do you want to remove everything from "Bags, Totes, Backpacks" or do you only want to remove the products that are (now) in "Cinch & Backpacks".


If it's the former - then this will do the trick :-

DELETE FROM ProductCategory WHERE CategoryID = 123;

(the above is presuming that 123 is the category ID for "Bags, Totes, Backpacks".


If it's the latter - then the following will do the trick :-

DELETE FROM ProductCategory WHERE ProductID IN (Select ProductID FROM ProductCategory WHERE CategoryID = 456) AND CategoryID = 123;

(this presumes that the category id FOR "Bags, Totes, Backpacks" is 123 & the category ID for "Cinch & Backpacks" is 456)


Insert usual Dire warnings about backing up, checking everything on a development server, checking everything twice, getting someone competant to look at your queries before running them (if you're not confident yourself). YMMV, It is possible to kill your site if you get this badly wrong.


Sorry for the above - I'd hate to be in a position whereby you broke something & I got the blame.



TTFN

BFG

Actually, I dont want to delete the product, I want to recategorize it from Bags and Cases/Bags, Totes, Backpacks TO Bags and Cases/Cinch & Backpacks.

I Know.

As I understand it - you now have the same product mapped to 2 categories?
The scripts I gave simply delete the mapping (ProductCategory is just a mapping table).


TTFN

BFG

Thx BFG...
I adjusted the command to:
DELETE FROM ProductCategory WHERE ProductID IN (Select productid FROM ProductCategory WHERE productID = xyz) AND CategoryID = 123;

Does anyone from ASP support know if this is the correct behavior of an excel import?

Originally Posted by jamotion

Thx BFG...
I adjusted the command to:
DELETE FROM ProductCategory WHERE ProductID IN (Select productid FROM ProductCategory WHERE productID = xyz) AND CategoryID = 123;

Does anyone from ASP support know if this is the correct behavior of an excel import?

Your 'adjustment' is incorrect. You don't need to specify the productid (xyz).
The way I wrote it - that one command will find ALL the productIDs that are mapped to "Bags, Totes, Backpacks" and "Cinch & Backpacks" & remove them from "Bags, Totes, Backpacks".


The behaviour is perfectly correct as far as I can tell.


TTFN

BFG

Thx.
Does anyone form ASP Support know if this is the correct behavior of the excel import file? Should the import file unmap the old category and re-map it to the new category?

I'm not entirely sure I'm clear on what you mean by correct behavior, but this is the expected behavior of an Excel import. If you wish to wipe the mappings, the SQL BFG9000 provided would be ideal, such wouldn't actually be removing any of the products, the ProductCategory table provides the Storefront with all the mappings between products and categories, hence deleting from this table removes neither products or categories, just the existing relationships between the two.

Dusty
ASPDotNetStorefront Staff

What I meant by correct behavior of excel spreadsheet is, if I were to upload the same product with everything being the same except CATEGORGY1, is it suppose to leave the old mapping as well as create the new mapping OR should it delete the old mapping and create the new mapping?

After my import, the product exist in 2 categories, the new and old. So before I try running a script for the update categories on specfic products, I want to make sure aspdotnetstorefront does not have a tool can do it for us... if not, I suggest we have one for a new release.

thx

As a follow up question, does the WSI have the same effect, duplicating the category? I just want to know what are all my options are, thx.

I'm struggling to think of a case where it would be normal to want to move products from one category to another.

Its because my manufacturer updated their category... in this case, my manufacturer has about 1000 products with new and old categories. And some products that exist in the old category doesnt necessary move to the new category...

OLD MAPPING
Product Name - Category
6 Panel Twill Cap - Headwear/Twill
Cotton Cap - Headwear/Twill

NEW MAPPING
6 Panel Twill Cap - Headwear/Twill
Cotton Cap - Headwear/Cotton

If I do the excel import, the Cotton cap will exist in both categories.

Hope this explains it.

BFG, what about the subcategory?

DELETE FROM ProductCategory WHERE ProductID IN (Select ProductID FROM ProductCategory WHERE CategoryID = 456) AND CategoryID = 123;

(this presumes that the category id FOR "Bags, Totes, Backpacks" is 123 & the category ID for "Cinch & Backpacks" is 456)

Do you still need the old category? If you have no use for it and would prefer the new one, you could update the CategoryID on the ProductCategory and alter it to the new one and that should be it.

1) Sometime I do because it hasnt changed. Would the excel import unmap it?

2) For scenarios where I do not need the category, I deleted via Webadmin which works, but does not delete in db, is there a tool to delete it in db other then using a sql query?