Simple when you know how?!?

[niknakgroup]

I messed up and truncated my description database table by accident. I have a backup, but only in excel. No matter how much I try I cannot convert this back to SQL, but all I need to update is my descriptions field. There must be an easy way to import just this information (i.e. for if you add a new language to your site - you dont need to update all the other fields, just the new language description...?)

 

I have over 3000 products active and a couple thousand pending, can anybody tell me how to do this without manually going through and re-describing every item?????? I have easypopulate and the excel import thingies active, I just cant figure out how to tell them to only look for products ID and descriptions - I dont want to overwrite all the other fields, which I have edited online since the backup was taken!

 

And yes, I know you're thinking it, I am a muppet for a) not taking more frequent backups and B) editing a live store. If you are new to this world, please learn from my misery!!! :blink:

[Paycheck]

Open the file in excell and copy and paste the insert stements that apply to what items you want to overwrite, in this case the description feilds.

 

Paste that into your phpMyAdmin SQL feild and hit go

 

If you downloaded the file with insert statements to an excell formatted with comma delimited it should have the sql insert statemnts and drop table if exist in place already. If not paste a few lines of your products here so I can take a look at it.

 

JM

[niknakgroup]

Thanks for quick reply; I am using the froogle uploader, which is where my "backup" for product descriptionshas come from; my host takes daily backups but has point blank refused to revert the products table as it is my error not theirs (I even tried a bribe!)

 

I am kind of familiar with the conversion from excel to SQL by using the insert code, but no matter what I do I cannot get excel to convert this info to a point where phpMyAdmin is happy with it - it keeps saying syntax error - the most common error is the inverted comma marks which are showing in the csv file not coming out in the sql statement - I have no idea why!!!! Anyway, 'cos the file is a bit big with the amendments in, here is where I am at so far - what am I doing wrong?????

 

"INSERT INTO `products_description` (products_id, products_description) VALUES('1040",Baby bangle in British standard hallmarked 9ct gold with heart centre set with guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1041",Gold baby bangle with cross fixed on and embedded glistening diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1043",Kids 14cm bracelet with cross charm in gold and embedded glistening diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1045",Kids teddy bear bracelet in gold with centre set guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1047",Double tag bracelet with yellow gold dog set with guaranteed quality H colour SI clarity or higher diamond and white gold bone From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1048",Double tag bracelet with yellow gold cat set with guaranteed quality H colour SI clarity or higher diamond and white gold fish From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1050",Triple heart bracelet in white and yellow gold and embedded glistening diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1052",Kids 14cm bracelet with Id plate and embedded glistening diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1054",Kids ID bracelet with butterfly set with guaranteed quality H colour SI clarity or higher diamonds to wingtips From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1056",Heart stud earrings in British standard hallmarked 9ct gold From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1057",Star shaped stud earrings for kids with centre set guaranteed quality H colour SI clarity or higher diamonds From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1059",Teddy bear design gold earrings with centre set guaranteed quality H colour SI clarity or higher diamonds From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1061",Gold butterfly stud earrings with guaranteed quality H colour SI clarity or higher diamonds set to wing tips From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1063",Open heart stud earrings with centre set guaranteed quality H colour SI clarity or higher diamonds From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1065",Triple heart design necklace with white gold and yellow gold From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1066",Hinged locket style pendant with heart cutout revealing interior set guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1068",Kiddies heart pendant in gold From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1070",Kiddies star shaped pendant in gold with centre set guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1072",Kids letter cube pendant in gold From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

"INSERT INTO `products_description` (products_id, products_description) VALUES('1073",Kids dummy style pendant with flush set guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.' ,);,

 

Notice on the first item, the inverted comma has dissappeared itself before Baby bangle - it is there until I save the file, then it vanishes - also there is a comma at the end of every line that I don't really want - I think it is confusing the hell out of phpMyAdmin, but I cant get rid of it!! Am I thinking on the right lines here, or am I way off the mark?

[Paycheck]

You are using th ewrong SQL function. Use the update function:

 

UPDATE `products_description` SET `products_id` = 1040, `products_description` = Baby bangle in British standard hallmarked 9ct gold with heart centre set with guaranteed quality H colour SI clarity or higher diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies.';

 

Try this one update snippet and tell me if it works. All you need is to tell it to update the table product_description, Using the id=1040 and the description.

 

JM

[niknakgroup]

:unsure: Thanks, thats getting me somewhere - at least it is reading the file now, but it is now coming up with the following:

MySQL said:

 

 

#1062 - Duplicate entry '1040-1' for key 1

 

Sorry to be clueless, but what the ** does that mean? Is it trying to crowbar another line in when there is already a line 1040? I though "UPDATE" told it to change an existing line...?

[Geotex]

I believe you want to use "replace" command, not update. Here's from mySQL manual

 

14.1.6 REPLACE Syntax

 

REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...),(...),...

 

Or:

 

REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name

SET col_name={expr | DEFAULT}, ...

 

Or:

 

REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name [(col_name,...)]

SELECT ...

 

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. See section 14.1.4 INSERT Syntax.

 

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

[Geotex]

before messing up your data anymore, pay close attention to this line!

 

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, <u>the old record is deleted before the new record is inserted.</u> See section 14.1.4 INSERT Syntax.

[niknakgroup]

OK! Thank you for your words of advice; I have tinkered and come up with a statement which works, but I have had to include the title into the statement as the original title was being deleted (as the previous post states would happen). Anyway, the long and the short of it is that it worked, problem solved and thank you all for your help! In case someone looks at this post for reference at a later date, the statement I had to use to update each product is:

REPLACE `products_description` SET `products_id` = 1040, `products_name` =  'GB128 9ct Gold & Diamond Heart Baby Bangle', `products_description` = 'Baby bangle in British standard hallmarked 9ct gold with heart centre set with guaranteed quality H colour / SI clarity (or higher) diamond From the amazingly popular D for Diamond kids range - style and fashion with the worlds most precious stone for your most valued little ladies. ';

 

This worked a treat; bear in mind that if you have other columns in your products_description table, you will need to update those columns too. B)