Model Numbers Starting with Zero

[jacdesigner]

I have searched near and far and am surprised there do not seem to be any other posts about this kind of issue. Here goes:

My client (website owner, retail merchandise), has 1000's of products they are trying to manage in the Zencart site each day and therefore they use EZ Populate a lot (as best they can).

Lately they have been having a big problem with products that have a sku number/model number starting with zero (0). I have looked at the file they are trying to upload, and I see the numbers there just fine. But it seems whenever they use EZ Populate to upload to the server, all the zeros get stripped out.....

So product 01234 is changed to 1234 instead.

This is especially problematic when they already did have that product in the database (from our original integration) as 01234.....and when EZ Pop uploads the 1234 version it is duplicating that product - so now 100's of products are in the catalog TWICE - once with the zero at beginning, and once without.

So they have 2 important questions:

1) Can we avoid the zeros being stripped? Is this a known issue or is it something they have to live with? Is this something related to the database structure?

2) Is there a way to do a massive delete of those 100's of duplicate products? Besides just using phpmyadmin..

Any advice appreciated. Thanks.

[schoolboy]

The problem is neither with Zencart, nor EasyPopulate.

It's with your spreadsheet software - (probably MS Excel?)

You need to format the cells in the column in your spreadsheet as a TEXT column.

MS Excel has a habit of auto-formatting to NUMBER when it sees just digits, so you have to highlight the column, right-click and make sure you format the cells as TEXT (before you add or paste data into it.)

[dbltoe]

Or.. try putting an apostrophe (') in front of the 0 in excel. No formatting needed.

[schoolboy]

Or.. try putting an apostrophe (') in front of the 0 in excel. No formatting needed.

Yes... good habit to get into when entering new records.

Now, to get rid of all those duplicates...

Via EP, get a full dump of the current product database. Remember, this will be a TXT file, so when you import that into a spreadsheet, it will probably retain the leading zeros.

SORT the spreadsheet by v_products_model column.

DELETE (from the sheet) all products that HAVE the leading 0.

You are now left with product records that do NOT have the leading zero.

REMOVE all columns except:

v_products_model
v_status
EOREOR

Set all values in v_status to 9

NEXT STEP IS IMPORTANT...

On your host admin C-Panel, make a copy of the images folder - call it images1 or something.

The reason for this is that v_status = 9 has a tendency to delete the product data AND its associated image. If it does delete the image, you have a quick backup... Just delete the original images folder and re-name images1 to images.

When you have a backuop images folder, run that tab-delimited file through EP. All those dupe products will go.

(I do this quite often with discontinued products from my stupplier - several hundred a week. I do a FULL BACKUP of the db first... !)

[jacdesigner]

Thank you, everyone. Really appreciate the tips.
My first suspicion was to ask the client (site owner) about formatting in Excel but when she showed me the exported file (txt tab delimited), the numbers with zeros were all in tact (not converted during export), so I assumed it was getting stripped out during upload. I will suggest the TEXT format option you described.

[jacdesigner]

Thank you but I just need to clarify.
When you say REMOVE ALL COLUMNS.....I assume that does NOT include the model number column? (Otherwise how will EZPopulate identify which item is in the row) I just need to be very specific and make sure we don't screw this up. Thanks.

Yes... good habit to get into when entering new records.

Now, to get rid of all those duplicates...

Via EP, get a full dump of the current product database. Remember, this will be a TXT file, so when you import that into a spreadsheet, it will probably retain the leading zeros.

SORT the spreadsheet by v_products_model column.

DELETE (from the sheet) all products that HAVE the leading 0.

You are now left with product records that do NOT have the leading zero.

REMOVE all columns except:

v_products_model
v_status
EOREOR

Set all values in v_status to 9

NEXT STEP IS IMPORTANT...

On your host admin C-Panel, make a copy of the images folder - call it images1 or something.

The reason for this is that v_status = 9 has a tendency to delete the product data AND its associated image. If it does delete the image, you have a quick backup... Just delete the original images folder and re-name images1 to images.

When you have a backuop images folder, run that tab-delimited file through EP. All those dupe products will go.

(I do this quite often with discontinued products from my stupplier - several hundred a week. I do a FULL BACKUP of the db first... !)

[jacdesigner]

Please disregard my last question. I now see that you DID specify to leave in the products model number.

Thank you!

[bonfire]

I was reading the reply from Schoolboy on 2/20/09 and I have a question. I am not real familiar with all the functions of Zen Cart so I am reluctant to try what you are suggesting without understanding the process better.

My first question, of many, is very basic. What does a v_status of 9 do versus 1?

I am taking baby steps here. I would hate to lose our entire product listing and I don't want to create more duplicates.

Scary!

I appreciate any input you can give me.