Mass Item Purge (EZ Populate)

[Hammerhead]

Is there a way we can do a compare and purge to eliminate discontinued items from a catalog?

I have about 8000 items in my store uploaded using EZP, but from time to time my supplier discontinues items. The item no longer appears in their CSV inventory file, but because it was uploaded previously, it still exists in my database. So I need to find a way to delete the items.

A manual deletion is fine because there aren't too many of them at any given time, but I can't seem to get the item search to work from the admin panel to pick up the item stock number.

Running Zen 1.38 and EZP 1.2.5.4.

Thanks!

[schoolboy]

See my Easy Populate WIKI where it shows you what you can do with the v_status column:

v_status = 1, product is rendered ACTIVE and live in the store
v_status = 0, product is rendered INACTIVE and not live (but data remains in the dbase).
v_status = 9, product will be DELETED from database along with its image in the images folder.

[Hammerhead]

See my Easy Populate WIKI where it shows you what you can do with the v_status column:

v_status = 1, product is rendered ACTIVE and live in the store
v_status = 0, product is rendered INACTIVE and not live (but data remains in the dbase).
v_status = 9, product will be DELETED from database along with its image in the images folder.

What I really need is a way to compare the new file to the old file and delete the items that are no longer present.

Here's the problem.

Let's say you have a CSV file of 100 products. Every day you refresh the pricing and inventory quantity using EZP. Then a week later your supplier stops carrying 20 items, but doesn't tell you which ones were discontinued, the daily price file only contains 80 items. Because you previously uploaded the items, they exist in your database, but you don't know which items were terminated because they don't have an 'exception' file, the items just no longer exist in the daily price feed.

How would you go about purging the items that are no longer carried while leaving the current items intact?

[schoolboy]

Not an easy one!

Why not ask your supplier to provide a list of discontinued items? Surely, as they are removing them from the list, what they could do is add a column to the spreadsheet with the header DISCONTINUED, and if the product is discontinued, their admin clerk puts a X in the cell alongside the appropriate product.

Contact them to explain the situation.

One of my clients works with a very reputable UK dropshipper, and on their site they have a daily notifications board. Here they list:

1. New items spreadsheet (EP v_status = 1)
2. Out of Stock spreadsheet (EP v_status = 0)
3. Back in Stock spreadsheet (EP v_status = 1)
4. Discontinued products spreadsheet (EP v_status = 9)
5. Full current product list.

Running these through EP takes a few minutes. All dropshippers/suppliers ought to have this sort of facility.

----------------------

You may have some success using XLOOKUP or LOOKUP features in MS Excel, or by doing a pivot table.

[schoolboy]

This MIGHT be an option, but it's messy...

A. When you receive the NEW list from your supplier, put it into Easy Populate spreadsheet format, making sure all v_status is set to 1.

B. Next, do an EP DUMP of all your current products on that day, to a tab-delimited text file. Open for editing, and set ALL v_status to 0.

C. Next, using copy and paste, copy the product data off the sheet in step A, and PASTE the data at the bottom of the sheet in step B. You now have a list of current products in your shop that will be set to "Out of Stock" (0), and a list of products that will be set to "In Stock" (1). MAKE SURE YOU HAVE YOUR ORIGINAL DATA ABOVE THE NEW DATA.

D. Use EP to load the sheet created in step C. It will chronologically load the data, FIRST setting ALL your current products to 0, and then setting the ones from your supplier's new sheet BACK TO 1

E. Do a second DUMP of the product data to your hard drive. This time, you will see ALL products. Some will be v_status=0, others will be v_status=1. Using Excel data sort function, SORT the data by the v_status column. CHANGE all those data rows where v_status=0 to v_status=9.

F. DELETE all products from that sheet where v_status=1. (This leaves you with only the products you want to delete from your database.

G. Use EP to run that sheet, so that the v_status=9 will now delete the products.

[schoolboy]

PS: BACKUP FIRST, and make sure to put your shop into maintenance mode while you do this.

[Hammerhead]

Why not ask your supplier to provide a list of discontinued items? Surely, as they are removing them from the list, what they could do is add a column to the spreadsheet with the header DISCONTINUED, and if the product is discontinued, their admin clerk puts a X in the cell alongside the appropriate product.

Yes, I already did that, but they're just not that sophisticated. And with over 8000 items in question, it becomes hard to handle.

Is there a way to globally purge all items in the database by model number only (which presumably would cause the catalog to not display them), then use EZP to replace only those items that are still active in the assortment?

[schoolboy]

Have you given any thought to my post #5 above?

[Website Rob]

What I really need is a way to compare the new file to the old file and delete the items that are no longer present.

Here's the problem.

Let's say you have a CSV file of 100 products. Every day you refresh the pricing and inventory quantity using EZP. Then a week later your supplier stops carrying 20 items, but doesn't tell you which ones were discontinued, the daily price file only contains 80 items. Because you previously uploaded the items, they exist in your database, but you don't know which items were terminated because they don't have an 'exception' file, the items just no longer exist in the daily price feed.

How would you go about purging the items that are no longer carried while leaving the current items intact?

If your Supplier cannot be bothered to tell you -- by name at a minimum -- what Products are no longer available, then you will continuously have a Major Headache on your hands.

You might able to use a program like WinMerg to do an ezPopulate file comparison... but with the amount of products you seem to carry and the size of the files involved for comparing, you will need one honking, powerful computer.

[JJDoench]

Have you tried a file compare using Excel? You can dump your EP file, and compare the item numbers to the current list from your distributor. Then you just set the missing items to status 9 in EP. I did it after our year end inventory, so I wouldn't be carrying around dead weight in my db. I think it also deleted the images for me...

I used this Excel tutorial I found online:

http://office.microsoft.com/en-us/ex...039151033.aspx

Good luck!

Jessica
Best Friends Quilt Shoppe
www.bestfriendsquilts.com