Update Data With SQLScripts Created In Excel

[paulm]

Originally posted by pjcvdpol@Jan 12 2006, 11:53 AM
EP uses this as primary key, and I am a bit stuck on how to solve this

That'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).

[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'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.

[paulm]

Originally posted by dwno@Feb 22 2006, 12:27 AM
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 ;-) ).

[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.

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 :).

[paulm]

Originally posted by grayson@Feb 22 2006, 11:43 AM
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?

[dwno]

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

[chai_rick]

Originally posted by chai_rick+Feb 21 2006, 06:19 PM--></div><table border='0' align='center' width='95%' cellpadding='3' cellspacing='1'><tr><td>QUOTE (chai_rick @ Feb 21 2006, 06:19 PM)</td></tr><tr><td id='QUOTE'> <!--QuoteBegin-blindsnake@Apr 18 2005, 10:35 AM
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&#39;m being dumb. Just found this handy thread , read it, think I follow it. Well, thought I did&#33;

Not going to give up though&#33;

Don&#39;t understand the "the_table" reference, and didn&#39;t understand the follow up response - but I refuse to give up&#33;

Please help&#33; [/b][/quote]
You are, and i&#39;m still waiting for an answer for my above query - unless one of you wants to give me a quick tutorial in Easy Populate&#33;&#33;&#33;

I&#39;ve downloaded and installed it fine, but honestly haven&#39;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&#33; I&#39;ll learn that another day.

Cheers

R

[paulm]

Originally posted by dwno@Feb 22 2006, 06:51 PM
I think we&#39;re hi-jacking the thread now, but DM can already do that on the fly during import (by for example combining 2 csv columns. more info)

Thx Eivind&#33;

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

[beachbum]

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

[Dusky]

Originally posted by JuxiZoza@Mar 25 2005, 05:21 AM


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) &#39;products&#39; table
2) &#39;products_description&#39; table
3) &#39;the_table&#39; 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 &#39;zenupdates&#39;

Create a product_id lookup table in &#39;zenupdates&#39;
Open &#39;products&#39; csv file with Excel
Highlight col &#39;A&#39; (products_id), copy to clipboard, and paste into col &#39;A&#39; AND col &#39;D&#39; of sheet1 of &#39;zenupdates&#39;
Highlight col &#39;D&#39; (products_model), copy to clipboard, and paste into col &#39;B&#39; of sheet1 of &#39;zenupdates&#39;
Close &#39;products&#39; file
Open &#39;products_description&#39; csv file with Excel
Highlight col &#39;C&#39; (products_name), copy to clipboard, and paste into col &#39;C&#39; of sheet1 of &#39;zenupdates&#39;
Close &#39;products_description&#39; file
Highlight first row of sheet1 and make bold
Put cursor in &#39;A2&#39; and do &#39;window / freeze panes&#39;
???

Hi -- relatively new here and trying to get it right.

I&#39;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&#39;t completely foreign... but still early days, if you will. **Note to all -- I&#39;ll be back again I feel... possibly quite a few times.

Also -- window/freeze panes?? I&#39;ll need another clue there if at all possible.

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