Update Data With SQLScripts Created In Excel

I'm not trying to update the products files. My problem is in updating the products_options_values file.

For instance, I need to create over 100 option values for an option name. Very easy to do in Excel. I downloaded the CSV file and did so.

Field Names are as follows:

products_options_values_id
language_id
products_options_values_name
products_options_values_sort_order

I am very new to MySQL but the statements are easy to follow, it's the set up for the rows I'm confused about in the CSV file. I don't need lookups to other files for this so the tutorial is a bit confusing for what I want to do.

I think :blink:

Thanks for your help!

The information you provided is very useful. One question, I am trying to import new products from csv or Excel file, into zen-cart, and I have no clue where to begin. Any idea?

Thanks in advance.

Now I am developing Easypopulate which works directly with Excel 95. I think it will be good additional tools for Zen Cart.

Can someone post a sample excel file they ended up with? Thanks.

wouldn't using easypopulate accomplish this as well? just wondering

Well,

I am struggeling with Easy Populate. EP works fantastic, and it is the ideal solution if you are not using the "model" field: EP uses this as primary key, and I am a bit stuck on how to solve this: i DO need the model field, but there are a lot of duplicates in my models meaning these are not inserted. It took me a while to figure this out.

So.... I have a couple of spreadsheets with thousands of products I want to insert in the database. I want to insert them in the Product Name, Model, Weight and Price fields in a number of categories. All fields (name, model, weight and price do have duplicate entries.

This should be faily easy IMHO..... How to tackle this?[B]

blindsnake:

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
Ok, I'm being dumb. Just found this handy thread :hug:, read it, think I follow it. Well, thought I did!

Not going to give up though!

Don't understand the "the_table" reference, and didn't understand the follow up response - but I refuse to give up!

Please help!

pjcvdpol:

EP uses this as primary key, and I am a bit stuck on how to solve thisThat's exactly what suprised me (unpleasantly!) with EP, why does it use the model as primary key?? The products_id seems so much more logical (or even a new produtcs_ep_id or something, if the products_id is not the best choice for some reason).

I can't really speak for EP, but Data Manager uses the products_model because it's more likely that suppliers provide a unique model column than anything else, and there must be a unique column to identify each product. ;) I'd assume this might also be why EP uses the products_model. I don't know how easy it would be to make another column the unique identifier in EP, but I plan to make that somehow configurable in DM.

Edit: actually it is already configurable in DM (although it's not just one place to edit), at least in the version I'm still working on.

dwno:

I can't really speak for EP, but Data Manager uses the products_model because it's more likely that suppliers provide a unique model column than anything else, and there must be a unique column to identify each product. ;)
I do understand that Eivind. But you would only be sure that the model number is unique then, if you only have one suplier. Or you need a prefix per suplier wich is added to the model number. Or even better (I think): a new unique (per product) prefix added to the model number. That would also solve the problem for the ones that do not have model numbers for a lot of products (like me ;-) ).

Hmm, I've always thought the model number was suppposed to be a unique identifier for your products. In general, not just in ZC. So customers can e.g. type in the model number to quickly find that product, and so you can keep track of your inventory.

Somewhere, somehow, you have to have a unique identifier for your products. I don't think it's unreasonable to assume that the model number is that identifier. Sure, for ZC specific applications like Easy Populate, there's the unique product_id field, but for general applications (like Eivind's Data Manager), assuming the model number is that unique identifier seems reasonable.

As a general comment (not ZC-specific), if you want to display the manufacturer's model number, it seems like the product name or description would be a good place for that (as it describes the product, rather than identifying the product within your store's inventory).

Of course, configurable is always very good :).

grayson:

Hmm, I've always thought the model number was suppposed to be a unique identifier for your products. In general, not just in ZC. So customers can e.g. type in the model number to quickly find that product, and so you can keep track of your inventory.
That sound logical to me :-)

In that case it would be nice if the Zen Cart script, or some contribution for Zen Cart, would help to generate these unique model numbers. By optionally adding the products_id to the model number for example? Would that be a good idea?

I think we're hi-jacking the thread now, but DM can already do that on the fly during import :D (by for example combining 2 csv columns. more info)

chai_rick:

blindsnake:

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
Ok, I'm being dumb. Just found this handy thread :hug:, read it, think I follow it. Well, thought I did!

Not going to give up though!

Don't understand the "the_table" reference, and didn't understand the follow up response - but I refuse to give up!

Please help!
You are, and i'm still waiting for an answer for my above query - unless one of you wants to give me a quick tutorial in Easy Populate!!!

I've downloaded and installed it fine, but honestly haven't had a chance to look at it yet.

I was really wanting to try to excel route, just to learn more, but time and deadlines are pressing! I'll learn that another day.

Cheers

R

dwno:

I think we're hi-jacking the thread now, but DM can already do that on the fly during import :D (by for example combining 2 csv columns. more info)
Thx Eivind!

And you are right of course, about hi-jacking the thread ::bsh

Ok I like the idea of using excel to update the tables

I downlaod an inventory "list" from my supplier once a week, it is in an execl format. How do I "merge" this data into the database then upload it with the images, prices and all?

I have tried to use the easypopulate but it comes out wrong or is not reliabe for I find items that are not priced properly or linked in correctly.

My server has MYSQL and MYPHP

Altough I have not written to the tables other than setting them up during install and using easypopulate.

Is it possible to email the supplier list and my info to see if these can be done for my website?

Another matter that pertains to this is the suplier is adding an XML feed I have the documentation is this feed to the tables via MYPHP?

Any help is appreciated.

Thx

JuxiZoza:

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'
???
Hi -- relatively new here and trying to get it right.

I've tried to export the required files to then follow through with the first few sequences, but my csv/xls file is populated in rows instead of columns? How can I sort that out please?

phpMySql isn't completely foreign... but still early days, if you will. **Note to all -- I'll be back again I feel... possibly quite a few times. :rolleyes:

Also -- window/freeze panes?? I'll need another clue there if at all possible.

Cheers in advance
Dusky.
[oh -- I'm working on this - http://www.maquillage.com.au/wholesale - which is currently password protected - I can remove that if anyone wants to view]

It's amazing what sleep can do.
Carry on. I'm making progress.
Call you if I mangle it. :D

paulm:

That sound logical to me :-)

In that case it would be nice if the Zen Cart script, or some contribution for Zen Cart, would help to generate these unique model numbers. By optionally adding the products_id to the model number for example? Would that be a good idea?
ok, that sounds acceptable enough. However: this means I am out of options with ZenCart for my products: I do need to display a couple of columns: Name, Height (used model here) Size (used Weight here) and Price.

The "De Luxe" solution would be to create two extra fields in the database: Height and Size and leave Model and Weight for what they are. Perhaps I can indeed use the model field to uniquely tag my products.

Now, if I would do this would I not head straight into some sort of update hell? I would have to hack away with every single small or larger update to either EP or ZC...

I am not afraid to make my hands dirty, but is this what others do? The problem is: I am not a programmer.... (but very good a t copy-paste and trial-and-error :-) )

Anyone with a workable solution?

This thread is rather old... Has any one greated any wizz-bang gui's yet?

An iterating wizard that simply remembers some handy defaults would do for initial cart import (and a DO-THE-LOT option)

also maybe the reverse of this thread (IE exporting to CSV/excel/something) would be a flexible interim step - or just some update/insert stock routines would do the trick.

Either way - if I have missed a doozy someone let me know - Ive got 1500 stock items to add. no digital data source for prices/catagories/itemText and a 1500 pictures to match em up to... :(

sigh...

your unexpectantly

si.

:blink: