Update Data With SQLScripts Created In Excel

Quote:

---

Originally posted by TheWhiteKnight@May 19 2005, 11:25 PM
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...

Quote:

---

Originally posted by DrByte@May 20 2005, 04:04 AM
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.

Quote:

---

Originally posted by dmcl1@May 20 2005, 09:27 AM
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

Quote:

---

Originally posted by sticky@May 22 2005, 10:30 PM
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.

Doesn't this seem a very very long-winded way of doing the same thing as Easypopulate? Or Merlin's quick updates contrib?

I mean, I wanted to correct a whole load of poor capitalization, and change the word "wigs" to "wig" (for google mainly).
I just exported an easypopulate file, opened excel, chose the column, find/replace, upload, done. 20 minutes in all, 4000 products taken care of.

I think I'm missing something here!!

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.