Update Data With SQLScripts Created In Excel

Juxi,

ok I followed the instructions and it didn't take much to figure everything out. Just a few minor questions. I PM'd you but after diving in the SQL coding I figured it out (Almost)

Is there some reason that (when following your guideline) that the column names don't line up? On the columns C1:???1 don't line up with the appropriate descriptions at the bottom in the "8th" row. It's like it is 1 column off. I've done this twice now, but I double checked and there is no "products_description" in the "products" table. I know that you merged the two tables together in Excel, but just as a question, Did you forget to put the products_description in there? I noticed you went up to D1 and pasted the info in, but your cells were one off. You added the description at C10 and D10 but you pasted the info in D1. Just a though but I would have thought you would have pasted in the Field name in C1 instead of D1, unless I am mistaken.

Maybe I did something wrong and I should go back and re-double-double check things again. It only took about 40 minutes to set it all up, so it's not that difficult, but I just wondered if there was an error in the coding or if I'm just being paranoid about things. I need to set up Zencart on my PC here so I can see if it works as well before I dump it into my catalog on my .com

I have to say this is one of the most INCREDIBLE ideas I have ever seen. Made me toss out the java populator program I was working on for it. :D Please keep up the good work! ::tup

P.S. if you want to see the excel sheet that I have of this, I have no problem posting it. All of the data is getting a fresh start and the new prices are going in as soon as I do this so the data in it is :yk. Thanks again!

• Josh (Your Resident Non-Search Box User) ;)

opentrees:

Yes!! it works! I created the code generating workbook in excel.

I get an error when running in MYsql though:

products_ id = 43 LIMIT 1

> 
> I think it was error #1064 I believe.
> 
> NEVER GIVE UP
> 
> PS, does this also work for images?
 Yup, I get the exact same error.  

My SQL statement is this:

UPDATE products SET products_model = GM2359TB WHERE products_ id = 1 LIMIT 1;

The error is this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id = 1 LIMIT 1' at line 1 

Any advice or help on this would be nice. :)  SQL PWNZ me :) ::tup

Might toss some quote marks around the: GM2359TB

I'd suggest you change your output formula to put quotes around the model number, so it looks like this:

UPDATE products SET products_model = 'GM2359TB' WHERE products_ id = 1 LIMIT 1;

DrByte:

I'd suggest you change your output formula to put quotes around the model number, so it looks like this:

UPDATE products SET products_model = 'GM2359TB' WHERE products_ id = 1 LIMIT 1;

 I've tried rewriting the SQL Statement to have it put quotes around the GM59* and also the first "1" in the [1 LIMIT 1] section coding.  I still get the error.  I'm checking my SQL statements but from what I know, they are coming up good.

You have a space in products_id

Ajeh:

You have a space in products_id
Holy crap, I just saw that. I couldn't see it at all in the excel spreadsheet or the PHPMyAdmin here. I would have enver even seen it if I hadn't recieved the email that there was a response for this thread.

Hmm... It took, but I can't peek into the SQL table to check. I'll post more on this here in a few minutes.

Also sorry for the double post. You can delete it if need be to save DB room :)

Ok,

It's taking the SQL statement, but its giving me this:

UPDATE products SET products_model = 'GM2359TB' WHERE products_id =1 LIMIT 1 ;

~~> # MySQL returned an empty result set (i.e. zero rows).

It told me that the query WAS successful though....

I wonder if it is because I am only updating 2 fields in the table and not the entire table.

0 rows returned means nothing was found where products_id=1

Also, if you follow Juxi's instructions, you have to play with the Excel sheet a little bit, but I have this formula modified to automatically put the ' around the product_model that is read out of the Excel Spreadsheet:

=$A$1&$F$8&"'"&F11&"'"&$A$2&E11&$A$3 set in A10 (note this is NOT a QTY update, but a model and a product ID update)

***** Posted By DrByte:  0 rows returned means nothing was found where products_id=1*****

Well this IS a blank DB that I am testing it on. I wonder why it is coming back with nothing. Hmmm.... It should be putting the info into the DB instead of returning nothing. It's pulling that info straight out of the Excel sheet too. ::frust

TheWhiteKnight:

Well this IS a blank DB that I am testing it on. I wonder why it is coming back with nothing. Hmmm.... It should be putting the info into the DB instead of returning nothing. It's pulling that info straight out of the Excel sheet too.
If your database is BLANK, then you cannot use an UPDATE statement....

INSERT statements ADD records to the database.
UPDATE statements, well, they update EXISTING information.

Perhaps you're looking at the wrong set of statements in your spreadsheet for what you're trying to do...

DrByte:

If your database is BLANK, then you cannot use an UPDATE statement....

INSERT statements ADD records to the database.
UPDATE statements, well, they update EXISTING information.

Perhaps you're looking at the wrong set of statements in your spreadsheet for what you're trying to do...
OMG, I can't believe that I was doing that. To my credit it was late last night when I was working on this, but still.... I'm one STUPID individual. ::ops

Ok, now I'm on to make my INSERT INTO statements.... ::snk

Thanks again Doc. I appreciate it. You people at Zen are really great. And after seeing my mistake in the SQL.... I'm SOOOO not worthy.

• Josh

Just be aware when inserting products using this Excel method, that there are several tables involved in the process of adding a product to the database. You will need to use INSERTs in more than just the products table.

A good way to check this in advance (particularly if you haven't added any products yet) is to add a product using the admin panel and then go look at your database -- view which tables were changed and how.

dmcl1:

Just be aware when inserting products using this Excel method, that there are several tables involved in the process of adding a product to the database. You will need to use INSERTs in more than just the products table.

A good way to check this in advance (particularly if you haven't added any products yet) is to add a product using the admin panel and then go look at your database -- view which tables were changed and how.
Yes, I was looking at that. The more and more I am looking at the table structure of the DB the more I am rrealizing it is going to be more and more involved, but SO worth it in the end.

From what I can see, it changes these 4 tables (if you modify them with no options):

categories
categories_description
products
products_description

(and others if you set attributes and other things)

It seems that if you just have all of your products not broken down into categories then you will be fine, but if you have them broken down into categories it is going to get more involved. :)

I just wanted to get the basics out of the way first before I put anything more involved into the coding. I figure if I can get the SQL statements and the references right, the rest should be as easy as adding a few SQL commands as well and more references.

I'm not super strong on my SQL commands so I don't know if it can be done all in one statement or not (I mean updating multiple tables & data all at once)

I guess I'm not posting my full thoughts on this even though I should be. I know that since I have found this, I have incurred a few more gray hairs but I am plugging away at it S-L-O-W-L-Y. All I want is ONE HUGE Excel spread sheet with all of my stuff (or at least it all broken down by vehicle/part type) and work it in from there.

FYI, I got it to work :) This is NICE and fast.

Remember when doing this you have to set it up a few times for different tables. Once for the products table, once for the product_description table, and I believe once for the categories table. Also watch your manufacturers_id and category_id or your database will become FUBAR fast.

Oh yeah also don't delete tables out of your database like I did. I deleted all of the "music" oriented tables and my database crashed. Oh well at least I did this on a test database and can go back in and do this all again quite fast now.

I had it running just fine here in the test database on my home PC. However, I have one SQL statement that inserts an entire complete record into the database, so the SQL statement is quite large. A lot of concantinations in Excel (which BTW I had no idea you could do in it.) I'm going to export this to OO and see if I can get it to run there too.

• Josh ::tup

If anyone wants an actual excel sheet, let me know, I'll be willing to share (if it's ok w/ the admins)

you might also want to look at products to categories

how about a copy of that sheet

sticky:

how about a copy of that sheet
Just as an FYI, I emptied the data set in the sheet for you, you'll have to fill in the data, and the pricing, images and stuff, but the coding works (only for the products page) You need to write the other statements for the other tables you need to update in the Database. I'll need an email address to email it to you at.

Ok, I got this thing to 100% work and I am SOOOO happy.

If you go in and set up the categories through the Admin panel (which I HIGHLY SUGGEST) then you will only have to modify 3 tables in the database.

products
products_description
products_to_categories

You should make a print out from PhpMA of your categories so you can keep them all straight in the speradsheet and database.

I entered 64 products in 10 seconds on my test database, with 3 copy and pastes into the PhpMyAdmin panel. I went back through, cross referenced all of the product ID's with the correct corresponding categories and they are all in the right places. :)

Kelvyn, I don't have Easypopulate and have never used it, however, I like the OO and Excel way of doing this. Besides It helps sharpen not only your SQL skills but your spreadsheet skills as well. ;)

Thank you Juxi for the info that allowed me to do this. I really appreciate all the help you posted on this, and also all the help the Zen-staffers gave me on my [horrible at times] SQL.

I've tried to follow your tutorial and i am now stuck

I have got to the following chapter (if you will)

Add 'model' and 'name' information to product rows

it states:

Copy 'C8:D8' to 'C11:C??'
While 'C11:D??' is still highlighted, copy to clipboard, and 'edit / paste special / values / ok'.
Cols 'C' & 'D' are used for your own reference. Either can be left off or hidden, if only one column gives you enough information to be sure that you are working with the correct item.
Do NOT convert 'C8:D8' to values. They may be needed again.

Why do you change to ?? copy c8:d8 to c11:c??

This has really confused me???

Can you please shed any light on this subject for me.

Thanks
D4