Multiple, critical problems - EZ POP

[schoolboy]

If you are using MS Excel to work the initial spreadsheet, you will find that it forces unwanted formatting - even if you choose to save the file as a tab-delimited text file.

Annoyingly, when Excel sees " in the data, it assumes you also want to delimit text using inverted commas, and it will pepper the resultant txt file with inverted comma symbols. Sometimes, it places these after a tab space resulting in the next field being delimited by inverted commas.

Easy Populate gets confused with this. However, it CAN tolerate inverted commas within the field contents - so your 12" should be OK.

So... how does one circumvent excel's tendency to pepper the TXT file with " . ?

I have found a possible solution - but you will need a program like CRIMSON EDITOR or EMERALD EDITOR (both are free text editors - use Google to find the download).

Install Emerald Editor, open it at an empty file... then...

1. In your excel spreadsheet, HIGHLIGHT the range of cells containing your data (ie: all the columns across and all the rows down THAT CONTAIN DATA. (Do not highlight the entire sheet.

2. COPY that highlighted block.

3. Go over to Enmerald editor, and use CTRL+V (paste) the block.

4. It should behave nicely, and paste EXACTLY the data, and NOT put unwanted " marks directly ahead of, or behind TAB spaces.

5. SAVE the emerald editor file as your xxxxxxxxx.txt file.

6. Load that through easy Populate.

(You could delete ALL product data in your store - to remove duplicates - by grabbing a EP dump of your current store data, then using v_status = 9, then importing that into EP. Be careful here... you MUST backup your images folder first. I usually rename my images folder to images_bak, and then create a new, empty images folder. If you fail to do this, a v_status=9 will delete all your images too. Then, with a cleaned out product database, load your new file created via Emerald Editor, into your store.)

One other downside of this is that all your products will assume new ID's, so if you have attributes, you may lose the associations. Also, historical order data may be lost too, as the original id will be gone, and the product that owned it will have a new id. I have worked around this too, by getting EP to export the product ID field contents, then making sure to start the database's increment at 0 again - (requires phpMyAdmin knowledge).

[double-happiness]

Wow, this is great stuff, many thanks.

As soon as I read your post I went straight to the tab-delimited text file, opened it with Notepad++, and sure enough, what did I see?

6 1285073.jpg """Weird Al"" Yankovic: Eat It 7""" """Weird Al"" Yankovic: Eat It<BR>7""<BR>Scotti Bros. Records A 4257<BR>UK 1984<BR><BR>Eat It<br>That Boy Could Dance<BR><BR>Condition media / cover: Very Good (VG) / Generic<BR>The labels have some water damage and there is a small crack in the edge of the vinyl, the playing surface is in good shape however." 0.99 50 1 Synth-pop --none-- 1 EOREOR

On that one row alone I count no less than 10 quotes that shouldn't be there, it looks like you've hit on a possible major issue...

So I tried following your instructions using Notepad++, and here is the result for the same line:

6 1285073.jpg "Weird Al" Yankovic: Eat It 7" "Weird Al" Yankovic: Eat It<BR>7"<BR>Scotti Bros. Records A 4257<BR>UK 1984<BR><BR>Eat It<br>That Boy Could Dance<BR><BR>Condition media / cover: Very Good (VG) / Generic<BR>The labels have some water damage and there is a small crack in the edge of the vinyl, the playing surface is in good shape however. 0.99 50 1 Synth-pop --none-- 1 EOREOR

This is exactly right, yee ha!

Nice post schoolboy, it looks like you've managed to identify a major issue and solve it in one go.

What I don't understand is though, you say I could delete all product data in your store to remove duplicates, and all my products will assume new ID's, I don't get this, can't I just overwrite all my existing products using their old ID's 1 - 3700? Should I run tests using this method in any case?

[double-happiness]

NB I'm not sure if this will help but I can confidently say my spreadsheet product IDs are currently the same as they were when I uploaded, I have exactly 3665 products on my sheet and the order should still be the same, as I always sort by artist, then title, then cat # before I upload. They will certainly all have changed by the next time I want to upload though, and I will need to replace the entire stock with an up-to-date inventory by then.

[schoolboy]

Understand what I mean when I refer to ID.

This is NOT the "model number", which you insert.

A relational database (like the one ZC applies), uses dbase-generated ID numbers in many instances, to give a unique identification to a line of data.

A database usually uses a function called AUTO INCREMENT... a technique of assigning the next number up from the previous number, so that there is always a unique, single ID number to identify a particular piece of data.

You can see these id's appear in many instances in your admin panel. If you go to ADMIN >>> CATALOG >>> CATEGORIES/PRODUCTS, you will see the ID numbers appear in the left column.

These ID numbers are used to CROSS-REFERENCE (relate) one set of data with another. For example, if a customer buys a widget in your store, that widget is referenced by an ID number. Instead of re-writing the name and description of the widget in the table that stores purchase history, the association is referenced by the ID number - so the purchase history will just show a list of Product ID numbers. Sort of like saying:

" Joe Smith bought 112, 134, 154, 165, 225 ".

[double-happiness]

I see. But the issues that you mentioned before about losing attributes and historical order data won't apply to me as 1) I don't have any products atributes and 2) I don't have any historical order data because I've never had any orders to my Zen Cart store.

I'm still a bit unsure what to do next. Backup my database and reload my catalogue as you advised, I guess?

[schoolboy]

Yes... do a backup of the database.

Then, get EasyPop to CREATE a file of your current data to store in the TEMP folder.

You can also then use a COPY of this file to do a v_status = 9 "mass-delete" (see below).

Then, either manually delete all your product data (via your admin >>> catalog >>> categories/products ), or as i said, run an easypop file where v_status column is all equal to 9.

BUT... rename your images folder first. If you use EP to DELETE products, it will remove its associated image as well.

If your store is not yet trading, you can "afford" to try things out. But doing regular Dbase backups is critical.

[schoolboy]

These ID numbers are used to CROSS-REFERENCE (relate) one set of data with another. For example, if a customer buys a widget in your store, that widget is referenced by an ID number. Instead of re-writing the name and description of the widget in the table that stores purchase history, the association is referenced by the ID number - so the purchase history will just show a list of Product ID numbers. Sort of like saying:

" Joe Smith bought 112, 134, 154, 165, 225 ".

Actually... this is a "bad example" because in zencart's case it DOES record the name and model number in the orders history table... Clever zencart... it knows that if the original product data is deleted, the order history must still maintain these essential bits of info!