Update Data With SQLScripts Created In Excel
There is NO SUBSTITUTE for doing the initial creation of some, most, or even all of your products using Admin. Only after studying how Admin handled the data should you try to use scripts to input/update the data. However, updating a lot of products using Admin can be time consuming. In many cases, learning how to use SQL scripts (with phpMyAdmin, SQLyog, Easy Populate, Navicate, etc.) to update the tables is a necessity.
The following information provides one method to set up Excel worksheets that will generate for you the SQL scripts needed to UPDATE your ZenCart tables.
The methods described can be used with any table, but the 'products' table will be used for this example because it contains quite a few fields that you might want to mass update.
Setting up an Excel worksheet that generates SQL UPDATE scripts
This project should take from 10-30 min to complete
Capture information from needed tables
Using phpMyAdmin... export,
1) 'products' table
2) 'products_description' table
3) 'the_table' you want to work with
using CSV in Excel format, with field names in first row, save to file
Open a new Excel workbook, save it as 'zenupdates'
Create a product_id lookup table in 'zenupdates'
Open 'products' csv file with Excel
Highlight col 'A' (products_id), copy to clipboard, and paste into col 'A' AND col 'D' of sheet1 of 'zenupdates'
Highlight col 'D' (products_model), copy to clipboard, and paste into col 'B' of sheet1 of 'zenupdates'
Close 'products' file
Open 'products_description' csv file with Excel
Highlight col 'C' (products_name), copy to clipboard, and paste into col 'C' of sheet1 of 'zenupdates'
Close 'products_description' file
Highlight first row of sheet1 and make bold
Put cursor in 'A2' and do 'window / freeze panes'
The lookup table is used to associate the 'products_model' and 'products_name' to the 'products_id' in the table(s) you are working with. You will have human readable reference to insure you are working with the correct product_id. The most frequent use of the table will be to lookup a product model or name using col 'A:C'. But, you can also use it to lookup the needed product_id for a model number by using col 'B:D'.
Only one product_id lookup table is needed per workbook, regardless of the number of table worksheets you have in the workbook.
Add 'products' table information to 'zenupdates'
Open 'products' csv file with Excel
Do 'window / arrange / tiled / ok'
Drag 'products' tab onto 'zenupdates', position it after sheet1
Do 'window / arrange / tiled / ok' again, to expand window.
This method can be used to combine all the tables you want to update into one 'zenupdates' workbook. Each table with its own worksheet.
Setting up the Excel 'products' worksheet
Make room for formula and other information
Insert 9 rows at top of worksheet, column names should be on row 10
Insert 4 cols at left of worksheet, 'products_id' should be in 'E10'
Add referential information to top of 'products' worksheet
Knowing the constructs of the table helps when developing a formula
Optional (strongly encouraged)
In phpMyAdmin, on the 'products' table, Structure tab, below the list of fields, click on 'Print view'.
Using the mouse, drag and highlight the table. (FIELD... DEFAULT, thru last row).
Copy to clipboard.
Go to 'A15' of products worksheet and paste.
While cells are still highlighted, copy to clipboard.
Go to 'D1' and 'edit / paste special / values / transpose / ok'.
Delete the table layout (delete rows 15:45) you originally pasted from phpMyAdmin.
Add column names
Code:
C10 = products_model
D10 = products_name
Add constants used in SQL formula
Code:
A1 = 'UPDATE products SET '
A2 = ' WHERE products_ id = '
A3 = ' LIMIT 1;'
* Do not include ' ' in A1-3, used to show spaces
* Replace products' references with your_table_name references
Add formula used to create descriptive information
Code:
C8 = =VLOOKUP(E8,Sheet1!A:C,2,0)
D8 = =VLOOKUP(E8,Sheet1!A:C,3,0)
E8 = =E11
* in other tables, E8 = =E11 will have to be changed
Add 'model' and 'name' information to product rows
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.
Add formula to create text variables used in SQL formula
Code:
F8 = =" , "&F10&" = "
Copy F8 to G8:AH8
Highlight F8:AH8, copy to clipboard, and 'edit / paste special / values / ok'.
Add formula, so you can proofread the SQL formula results
This is done so you can keep col 'A' narrow (allowing max number of cols to show on your screen) but, still see the expanded SQL formula result, because there should be nothing to the right of 'A6'.
Point, click, & tap to create the final SQL script
I could write this out, but I want you to see how to point, click, & tap your way to the final formula.
Instructions contained in [ ] mean… use mouse to select cell
Instructions contained in ' ' mean… a keystroke on the keyboard
Code:
A10 = '=' [A1] 'F4' '&' [G8] 'F4' '&' [G10[ '&' [J8] 'F4' '&' [J10] '&' [A2] 'F4' '&' [E10] '&' [A3] 'F4' 'enter'
The formula in A10 should look like…
=$A$1&$G$8&G10&$J$8&J10&$A$2&E10&$A$3
And, A6 should look like…
UPDATE products SET , products_quantity = products_quantity , products_price = products_price WHERE products_ id = products_id LIMIT 1;
Correcting the formula
The formula is self-proofing, because it is on the same row as the field names. When replicated the relative references will automatically insert the proper values.
There is an error in the formula. The first field named in the statement should not have a ' , ' in front of it. Edit 'G8' and remove the ' , '
But, you intended to add to the inventory. Not set a new inventory level. And, you are not sure if any orders came in the last few minutes and changed the inventory on hand in the products table.
Simply, change 'G8' to 'products_quantity = products_quantity + '
A6 should now look like…
UPDATE products SET products_quantity = products_quantity + products_quantity , products_price = products_price WHERE products_ id = products_id LIMIT 1;
Conventions I use:
Green text, variables used in formula (A1, A2, A3, F8:AH8)
Blue text, formula (C8, D8, F8, A10)
Colors are used to avoid overwriting critical cells
After formula are replicated down the rows and converted to results, change the 'results' text color from blue to black.
Produce & run SQL script
Copy 'A10' to 'A11:A??'
While the range is highlighted…
copy to clipboard and 'edit / paste special / values / ok'
again, while range is highlighted copy to clipboard & paste
into the SQL window in phpMyAdmin ~or~
into a notepad text file and use phpMyAdmin 'textfile' option
When you return to Excel, the 'A11:A??' range should still be highlighted, change text color to black.
Now that the template is setup, you can make mass updates to the product table as quickly as you can enter the changes in the spreadsheet, replicate the 'A10' formula, copy/paste into phpMyAdmin. Each case is different, but you might be able to update a thousand records in the time it would have taken you to do twenty.
Some questions you might have
Do I need to export a new copy of the table every time I want to do an update?
No. You need a limited amount of information from the exported the table. Depending on the table, you can delete much of the data (except the '_ID' field contents and fields you might want to keep for reference) so that you are not confused by old data vs your new data for updating. Also, hiding the columns you don't frequently update gives you a much cleaner, compact data range table to work with. You can unhide the columns anytime you want to use them and point, click & tap yourself to a new formula.
Why do you say to covert the formula to results?
With a lot of tables & product rows, Excel's performance can be affected by having all the formula active. Having four formula open per worksheet is much better than 100's or 1,000's per worksheet. In my largest Zen workbook, I can have 50 or 300,000 open formula. The text color change from blue to black, after you convert the formula to its results, is to visually let you know the cell is a result and no longer a formula.
*note: 'paste special / values' is used several times in this tutorial and even more so, if you actively use this model. There is a special icon available in Excel that you can put on your Excel toolbar for this.
Do 'Tools / Customize / Commands Tab / left window 'edit' / right window 'paste values'. Drag the icon to your toolbar, in the area where the 'copy to clipboard' icon is. You can now simply highlight a range, click on the 'copy to clipboard' icon, and click on the 'paste values' icon. This will speed things up for you. But, watch the hour glass mouse pointer. When you have 1,000's of formula being copied to the clipboard, it can take a few moments… wait, then you click on the 'paste value' icon.
While the customize widow is still open, you can right-click on the icon and change to icon only (default style), 'text only', or 'image and text'… depending on your comfort level with new icons.
Can the data be sorted to aide in inputting the changes?
Yes, as long as you keep col B and row 9 empty. Excel should treat the table information as a data range with labels on top. Just to be safe, if you make line 10 bold or you make line 10 a larger font, Excel usually assumes that is an indication of a label. I also (put cursor in 'A11') do 'window / freeze pane' to help lock the column labels and to keep them from scrolling off the screen.
Do I have to resort on '_ID', before creating the SQL script?
No.
Do I have to include every row in the SQL script?
No, just the ones you are updating.
Do I have to 'escape' any special characters within my text fields?
Yes.
Text fields (i.e. product description) should be wrapped with 's. ('your text').
Any apostrophe (') within the text needs another apostrophe in front of it ('').
Any backslash (\) needs another backslash in front of it (\\).
How are blank/empty fields handled?
Not very well.
You need a value (not an empty cell) in EVERY Excel cell within the row/col that you include in your script. The formula will put as many fields as you tell it to use, on every row. And, mySQL is expecting a value for every field you say that you want updated. Something must occupy the space where the data should be. It can not be empty, thus causing the , delimiter or the WHERE to move into its place.
Say you want to update 100 rows and 3 fields. Some rows you have data in 3 fields, some in 2 fields, and some rows have only 1 field filled in.
There are several things you can do.
# Multi-pass method.
Sort the data range and make a pass updating the formula for the 3 field records. Copy the results of affected records into a text file. Change the formula to include only the 2 fields, make a second pass, and copy the affected records into the same text file. Change the formula to include only one field, make another pass, and copy the affected records into the same text file. Using phpMyAdmin, load the text file. This has the affect of leaving the contents of the non-updated cells as they are. The next method does the same thing in one pass.
# Retain whatever is in the database, for the empty cells.
Sort the data range to bring the empty cells together. Copy the field name into the empty cells. The SQL instruction for that field should look like it does in the 'A6' preview cell: field? = field?
# Pass an empty string.
Sort the data range to bring the empty cells together. In the empty Excel cells, enter 3 's ('''). The first tells Excel that what follows is a text string. The other two ('') will pass to the SQL instruction formula as '' and be interpreted by mySQL as an empty string. Text fields will appear empty and numeric fields will appear as 0. Passing an empty string is NOT the same as a blank field in Admin and the field will NOT take on the default value. ZenCart may or may not take kindly to this condition when testing for content, since NULL and '' are not the same.
# Pass the default value.
Sort the data range to bring the empty cells together. Copy the default value found in row 4 into the empty cells.
Date fields show up in the formula as Julian numbers! Why?
Because!
There isn't much reason to fool with the date fields. Unless, you have a fetish about exactness and feel you must update the 'last_date_modified', along with the other fields. In which case, you need to insert a ' at the beginning of the date to instruct Excel to treat the data as a string. Excel will pass the characters and not the Julian number to the SQL instruction formula and mySQL will accept it as a date value. Be sure to use the same format the exported date used. You can leave off the hr:min:sec.
Can these worksheets be considered my 'backup' of my tables?
I wouldn't. Use myPhpAdmin to export 'SQL', with 'add DROP TABLE', save as file. You can do all tables in one file or each table in its own file.
Some tables don't have a 'product_id' field, how come?
Not all tables are linked to a product. Some tables contain additional information that ZenCart needs access to and may or may not even have an '_id' field. Some tables are part of an interrelated group, like the 'countries', 'zones', 'geo_zones', 'zones_to_geo_zones' tables, and you have to conform to any '_id' links that they use to ensure that referential integrity is maintained.
Can I re-sequence the '_id' field?
Prior to your site going live, a qualified… Yes. You will have to insure that all linking fields in associated tables are also properly re-sequenced. Some tables, like 'featured', 'specials', 'layout_boxes', the countries/zones group, can be changed with little if any significant consequence after you are live.
What are the hardest tables to work on, using Excel/phpMyAdmin?
For me, 'product_attributes' & 'configuration'.
The 'product_attributes' table has 27 fields, all kinds of switches, offsets, factors, quantity this & display that. I use the attributes for a non-standard purpose, and developing the algorithms to fill-in those fields, so that I got the correct results on both the product info page and the cart page… was a task. For some things, it took many experiments putting data in via Admin and checking the results, before I was able to reproduce what I needed to do, in Excel. Partly due to lack of grey matter, partly from 'wonder why did Linda do it that way'. If you too experience the latter, stick with it. Her devious scheme will eventually unfold to you. But then, your experience may be quite different. Once you see the table layout and the reference information (row 1:4), and study the information in the SQL export files… it may all quickly come together for you and attributes will no longer be the mystery they once were.
The 'configuration' table is not hard to work with, its just a bunch of little things. Things like date formats, true vs TRUE, how 'NULL' is handled in a text field vs a number field, and more that you find out only by trial and error. My preferred method of updating tables is 'Load Data Infile' and I seldom use update. Many of my problems with the 'configuration' table are a result of that and I'll probably switch to using update, in the future. Regardless of my successes or failures with it, because of the importance of the 'configuration' table, I recommend that only those with the rank of 'Galaxy Overlord' and above mess with the 'configuration' table and 'Space Cadets' should stay away from it.
Things you probably should NOT do!
Update a primary site with a new, untested SQL script ::tdn
If you don't have a local copy of ZenCart, install one… install two.
Test your scripts on the local test site first.
If your only site is the remote one… install a local copy of Zencart, export all the tables from the remote site and run the resulting script against your local database. You'll have a mirror of your remote site and can play with it.
If you have a local site and it is primary and you ftp changes from it to your remote site, then work with a copy of your local site. Just copy the whole ZenSite folder to a new folder, copy the site's mySQL folder to a new folder and change the two configuration files.
The point is… until you are very, very confident your scripts do what you intended for them to do, test run them against a secondary site. Don't use your primary site to 'bench test' a new script.
Run SQL scripts without backing up your tables first ::tdn
It only takes a moment or two to have phpMyAdmin export a table. If you screw up, run the exported script to restore the table. Be sure select the "add DROP TABLE" option so the corrupted records will be cleaned out, before you restore the old records. If you forget to include "add DROP TABLE" option, you can manually 'EMPTY' the table before running the script to restore the data.
Update fields which are maintained by Zen-Cart ::tdn
That would include most of the fields in the 'orders' group of files, ALL password fields, ANY financial tables (like paypal or authorizenet, ). An exception would be the inventory quantity on hand, which was covered above.
Read the field names and assume you know what needs to be done ::tdn
Take a guess, where do you think the 'special price' of an item is stored?
While you are learning the interdependencies of the tables, where things are stored, and how different switches and options effect the data… export the entire database. Manually enter some changes that you want to make, using Admin. Export another copy of the database. Use your favorite file comparison tool to compare the two files. If everything changed just as you would have expected… 'atta boy', you were right! If not, aren't you glad you checked. When updating your live database, one 'gotcha' wipes out a whole bunch of 'atta boys'.
Using SQL scripts to update your database gives you a great deal of power. Be cautious and use it wisely.
There are no expressed or implied warranties given for the information provided. The normal "don't come crying to me if you screw up" applies in all jurisdictions in the known galaxy.
Juxi
