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

C10 = products_model
D10 = products_name

Add constants used in SQL formula

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

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

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

A6 = =A10

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

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

Just a note;

The table 'products_description' mentioned has a 'language_id' column, so for each language installed you will get multiplied results if you export the entire table as described. Therefore, to get the intended results from the above instructions, you should either export only the fields with a given 'language_id' or double all the other tables that don't have a language_id field, in your excel worksheet.

To export only the fields where language_id equals 1, try this SQL query:

SELECT * FROM `products_description` WHERE language_id = '1' ORDER BY products_id INTO OUTFILE '/path/to/file_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

This requires that you have the FILE privilege granted, and that the file "/path/to/file_name.csv" is writable by MySQL. If you're using a DB prefix, you would also have to add that to the table name.

To show all privileges granted for a user, you would do this SQL query:

show grants for username;
``` where "username" is your MySQL user name.

I'm not sure about this, but I think windows users may have to replace > **LINES TERMINATED BY '\n'** with > **LINES TERMINATED BY '**\r**\n'** for it to be imported by excel. I don't know, I don't use either windows or excel.

If you don't have the FILE privilege, it should be very easy to write a php script that outputs database tables to csv files. I have not found a way to export only parts of a table by using phpMyAdmin, but if there's a way, I'd appreciate to know it.

I haven't dealt with Excel in ages but do have Open Office. Is there anything that has to be done differently using it instead? I know a lot of people who'd rather use OO and any help would be greatly appreciated!

Thanks!

I believe that OO is superior compared to Excel. Excel add all kinds of junk to your files ::tdn

Thanks Juxi for the detailed info :)

dwno:

The table 'products_description' mentioned has a 'language_id' column, so for each language installed you will get multiplied results if you export the entire table as described.
I appreciate Eivind's comments about the multi-language tables. In fact, it took several PMs to persuade him to contribute his thoughts, because I didn't have any personal experience with alternate languages. Thanks, Eivind.

But, I've had a chance to look into the issue of doing updates with scripts on multi-language tables and came to the conclusion that… working with all the languages in the same worksheet is a good thing.

If you want to work with only one language at a time… you can do what Eivind suggested; you can also export / import into Excel, sort on language_id, and delete the rows you don't want; or you can export / import into Excel, copy the worksheet to additional worksheet(s), sort on language_id, delete the rows you don't want, and have a worksheet for each language.

I'm going to try to convince you should be work on tables with multiple languages in them as one complete table, with all translations intact.

In ZenCart (v1.2.4), 12 of the 87 tables have a language related field.

They are

1. categories_description, coupons_description, products_description, reviews_description
2. languages, manufacturers_info, record_artists_info, record_company_info, template_select
3. orders_status, products_options, products_options_values

The first group of four tables have alternate language descriptions contained in the same table. These tables are prime candidates for using scripts.

The second group of five tables use only a language flag, the tables are small, you set them and forget them, and it would be a waste of time setting up scripts to update them. Maintain them in Admin.

The third group of three tables is a mixture of the first two. There is multi-language content in the table, but, it is possible that you will set them and forget them, they may not justify the effort to setup a SQL worksheet, and updating in Admin may be the better method… depending on how many records you need to maintain.

You can evaluate the database schema and decide for yourself.
To get a copy of the ZenCart database schema…
In phpMyAdmin, on the Structure tab, below the table names, you should find Data Dictionary. Click on it, when the new page comes up, click in right frame, do <ctrl><a>, <ctrl><c>, go to a new worksheet in the ZenUpDates workbook, go to B1, and paste. There will be some objects on the page that you will want to remove. In row 1, <right-click> on the two icons and 'cut' them. Delete row 1. Do <end><home> and at the bottom of the table you will see another icon. <right-click>'cut' twice to remove it. You can highlight the four columns, do format / column / autofit selection and have a table of all of ZenCart's tables and fields that you can study and refer to.

Regarding the first group of tables.
When you export the tables with all of their records, your are rewarded with enhanced editing abilities.

By sorting on the table_id field and then the language_id field, you can <arrow-up>/<arrow-down> between description fields and ensure the translation, embedded html code, etc. is correct and is consistent between languages. You can create formulas to check if you have an alternate language description for every table_id and, if not, insert a row, copy the current record into the new row, edit language_id and description.

The reviews_description table is an excellent candidate. Someone writes a review that you think should be seen in all the languages. Insert a row, copy record, change language_id, translate the text, and the review can be read in your other languages.

After you have edited the table(s) for missing records, translations, consistent presentation, adding reviews to other languages, etc., etc., replicate the formula to create the SQL script, and run it. Your site will be consistent from language to language and I think your customers will appreciate the professional image it projects.

Your tablename_description worksheets become your original sources and backing up these important tables is no longer needed. Any changes are first made in your worksheet and then the table is updated via a script. Making changes to your descriptions, even adding a whole new language, etc. can all be done offline, at your pace, and uploaded at one time.

skhilled:

I haven't dealt with Excel in ages but do have Open Office. Is there anything that has to be done differently using it instead?
The main purpose for using a spreadsheet is to organize the information in columns and rows. Then compose a SQL statement, by concatenating the information into a usable string. I don't have personal experience with the spreadsheet you are using… but, I have used VisiCalc, Multiplan, Lotus 1-2-3, QuatroPro, and a few others. There is nothing in the tutorial that I could not do in any of those programs.

I am sure you will do fine. The technique might be a little different than I described, but you should have not any problem getting the desired results.

Juxi, where in the world do you find time to put these together? More importantly, where can I get some of it? ;)

Awesome work, as usual. ::tup

**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**

hi there. First of all thanks a lot for sharing the info.
My actual problem is finding "the_table" to export. Or do i have to create the table myself?
Any info is welcomed

Sorry for confusion. Maybe I should not have made it look like a table name.
Export the first two tables plus
I literally meant for you to export the table you want to work with.
You then combine information from the first two with the third one.

i must be really dumb cause i still dont understand how do i export the third table :(.
but thant doesnt matter because i already gave up from using this tip. Guess i dont have a way to escape from hard work... (i have to add near 9000 products)

I was able to follow everything up until:

Add column names
CODE
C10 = products_model
D10 = products_name

what do you mean? paste products_model into cell 10?
There is nothing below cell C 10 in the workbook, and to the right of it, (in e10), there is
2;"1";"3";;;"30.0000";"0";"2005-04-09 14:32:44";NULL;NULL;"0.00";"1";"0";"0";"2";"1";"1";"0";"0";"0";"1";"0";"1";"0";"0";"0";"0";"30.0000";"1";"1"

etc...

Please advise!!! Thanks.

Let me ask this.....is there anyone on these forums who has actually followed this tutorial from start to end and completed it?

Just curious.

lol, mate, I'm with you. It's a bit cryptic, and I'm not willing to test this stuff on even a test database.

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?

I'm not exactly sure what that script does.

I ended up writing my own script that takes data from a .csv file my client prepared (columns for item name and price)
and dumps them into ZenCart with attributes.

Works OK and I was able to get 1000 items into the database pretty quickly.

Well, personally, I LOVE Juxi's solution and it has worked great for me. It does take a certain level of comfort with Excel and its formulas however.

** Let me ask this.....is there anyone on these forums who has actually followed this tutorial from start to end and completed it?**
** lol, mate, I'm with you. It's a bit cryptic, and I'm not willing to test this stuff on even a test database.**
** Yes!! it works! I created the code generating workbook in excel.
NEVER GIVE UP**
I'm not exactly sure what that script does.
** Well, personally, I LOVE Juxi's solution and it has worked great for me. It does take a certain level of comfort with Excel and its formulas however.**
This is a 'hands on' tutorial.

When mastered,
you can do things in a fraction of a second that could take forever in Admin…
update your products' descriptions and prices,
reorganize your categories, zones, geo-zones, attributes and options,
re-associate paths and names of your images to products,
put products on special or featured,
and more.

It shows you…
how to set up a worksheet,
how to import data from the tables into Excel,
how to label your rows with user friendly descriptions,
how to learn about a table's fields and their constraints,
how to learn what tables/fields need to be updated in ANY situation,
how to compose your SQL script using a simple point-click-tap method,
how to load those scripts to update your database.

It also provides…
suggested 'best practices',
things you should avoid,
useful tips for working within Excel.

It is a beginner's guide, NOT a final solution.
NO final scripts are provided, but self-proofing methods to write them are.
NO templates are provided, but the processes needed to create them are.

It does not put any constraints on…
how far you can extend its use,
how many problems you can cause for yourself.

For sure, the more you know about Excel and mySQL instructions, the easier it will be to see how it all comes together.

The tutorial is not perfect or definitive.

It addresses UPDATING your tables, not INSERTING new records. I never finished the 'inserting new records' addendum that I was working on.
But, that has not prevented numerous users from adapting their worksheets to do that.

It is not a plug-in and go solution for your situation.
But, it contains the tools and framework needed to get you working on a solution to your needs.

That is all it was ever intended to be.

Juxi

And nicely-written, at that too Juxi! ::tup

nmuta:

I was able to follow everything up until:

Add column names
CODE
C10 = products_model
D10 = products_name

what do you mean? paste products_model into cell 10?
There is nothing below cell C 10 in the workbook, and to the right of it, (in e10), there is
2;"1";"3";;;"30.0000";"0";"2005-04-09 14:32:44";NULL;NULL;"0.00";"1";"0";"0";"2";"1";"1";"0";"0";"0";"1";"0";"1";"0";"0";"0";"0";"30.0000";"1";"1"

etc...

Please advise!!! Thanks.
I to am stuck here, with the exception that I don't have this problem: There is nothing below cell C 10 in the workbook, and to the right of it, (in e10), there is
2;"1";"3";;;"30.0000";"0";"2005-04-09 14:32:44";NULL;NULL;"0.00";"1";"0";"0";"2";"1";"1";"0";"0";"0";"1";"0";"1";"0";"0";"0";"0";"30.0000";"1";"1"

;;Add column names

CODE

C10 = products_model
D10 = products_name

Now, do I enter this as products_model, or as C10 = products_model, and this goes into sheet2, correct? The trouble I am having is I keep getting NAME# and REF# errors.

Thanks for any assistance. I am slowly getting there with this script. I'm having to learn both spreadsheeting and sql at the same time 8-P.

For: nmuta & stiiixy

First, when I wrote the original message… I went through the process that I had done myself many times and wrote down the steps that I performed. I proofed it for accuracy, but did not actually do one from scratch using the instructions. In trying to recreate the situation that you described, I have gone through it as if I was a first time reader. I've noted a couple typos and some areas that could be worded better. I will submit an edited version to Kim with those changes. I do not expect there will be any substantive differences, only minor editorial changes for clarity.

RE: Code Blocks
For the most part, they are intended to be copy / pasted.
The format is cell reference = code block to be pasted.
If the code block begins or ends with a <space>, then it is bounded with ' ' to denote there is a space that must be included. Only the code block should be entered into the referenced cell. The first = is a delimiter between the cell reference and the code block to be pasted and serves no other purpose. Possibly, removing the first = sign would eliminate the confusion.

RE: The contents in E10
Because you have something other than product_id in E10, I think one of the early steps was missed.

Capture information from needed tables
using CSV in Excel format, with field names in first row, save to file

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'

These are important and all subsequent cell references and formulae are dependent on them. Please check to see if that is the problem. (column names should say field names)

RE: NAME# and REF# errors
The information above will hopefully clear them.