Ubercart

Hi, David!
I've just played few minutes with your module. Thank you for your great work!
But I have to say that in some way it lacks features of original jorditr's version.

First of all, what if CSV file is generated by a 3rd party software that doesn't have any idea of ProductIDs, but only of SKUs?
Second, is there any option to export/import both prices and stock levels at once?

Yes, what you propose is definitely desirable. However, if you look at the database schema you will notice that SKU is not actually a key of the uc_products table (represented by the "model" column). Despite its connotation, the SKU does not actually uniquely identify a product in the crucial uc_products table. There is nothing in Ubercart to stop you creating many products all with the same SKU - try it.

The only guarantied unique identifier for a product is its Drupal "nid" which I have called Product_Id in an attempt to avoid jargon. The import code does check that the SKU matches the "model" field on import, and will report a warning if there's no match, but that's just a safety check. Updating by SKU without a SKU key would also mean that updates would be slow for a large store.

However, all is not lost. You could probably do some kind of relational join or lookup type operation in your spreadsheet that would essentially merge the 3rd party and exported spreadsheets on the SKU field - once you'd satisfied yourself the SKUs were unique in the exported file. Remember, you can go to town with extra columns in the imported spreadsheet and the import code will just ignore them.

The reason why the stock level and price info is not exported in the same file also relates to database structure. In this case, SKU _is_ a key on the uc_product_stock table (called 'sku', not 'model' in this case - go figure... ), so can be used as a unique identifier, and (as far as I can tell) indicates the stock level for both product options that have "alternate" SKUs and products themselves. I guess it would be possible to include the stock level for products in products.csv, and the stock level for product options in product-options.csv. Hmm... I'll have a think about it.
cheers
David

The fact that 'model' is not unique in products table (and even there's no indexing on it) is kinda surprise for me (I was pretty sure it is, so I even didn't check this out).
Bad news, but thanks for pointing this out.

Anyway, I'm considering all these import/update modules as a starting point for developers. So again, huge thanks for another great possibility.

this is AWESOME!!!!

Looks promising,thanks!

Does it use drupal batch api?

Feature request: allow translated nodes to be updated at once.

Yes, it uses the batch API for the database updates. Feature request noted.
cheers
David

I'm developing a shopping cart system for a conference and we have two different prices for tickets ... one for regular and the other for students. I want to be able to control the tickets so that I do not sell more tickets altogether not having to set limits for students and regulars. I was going to have one SKU shared by two products with different prices. Am I going to get into difficulty doing this?

Also I have one product that is a package of some of the others ... is there any way that I can adjust the other levels rather than having a level for that package?

The database schema does allow several products to have the same SKU. This may well be a design decision to allow the very thing you're proposing. In my very limited testing, it seems like there's nothing to stop you having several products sharing the one SKU.

Note that this version (Stock & Price CSV Updater 2) is designed to handle your case. The original version won't handle it properly, as it doesn't differentiate between products that have the same SKU.

As for the package stuff, I've yet to investigate this. I know Ubercart has a "product kit" feature that provides this kind of functionality, but I don't know how stock levels of a kit's components are handled.

I have some sku numbers that begin with the digit 0. ex/ 093456. When I export this to a cvs file the 0 is cut off leaving 93456. I haven't imported yet, because I do not want to run the risk of breaking something.

When I get time I'm going to set up a dummy product with a 0 prefixed sku and try it out.

Does anyone know off hand if this will work ok as is?

The leading 0s in SKUs are actually preserved when exporting. However, you might find that your spreadsheet is dropping them when it loads the file if it is set to auto-detect the type of the field. I note that OpenOffice does this, and you have to tell it the column is type "Text", not "Standard".

David, Thank-you for this update. well thought out and elegant.

I have 1 issue that I can't figure out. I have products that all run off attribute classes. Now, I've been pushing the existing ubercart modules to their limits with this, but every module I try can't handle the the product options well, specifically stock. Your module is the first that has allowed me to successfully modify the price and cost of the product options, but I also need to be able to get the stocks changed too.

How my products are sold, the main SKU of my product will never have any stock (stock=0) and I just track/change the stock of the product options. Does this make sense?

Anyway, Do you know of a way to change the stock levels of the product options? is this possible with your module? Are there any slight code changes I could make to do this?

Again, thanks for your contribution, great module

Hi David

Excellent module, thank you - to be honest it would be hard to run without it!

Some feedback - I have problems importing/exporting using excel - which I think might be because of the leading zeros issue.

This was the error message...

* Couldn't find an existing entry matching (1002, 3495). Skipping this row.
* The CSV file has been imported.

And this the wordpad csv file...

PRODUCT_ID,SKU,name,Cost,List_price,Sell_price
"3495","001002","Aerochamber Mask Child","28.02000","45.9","41.80909"

The odd values are to do with sales tax, not an insurmountable issue.

Thank you and best wishes

James

http://www.pharmacys.com.au/

Sorry, I've just read my own comment and it sounds really whingy!

This is a great module and totally needed.

The excel point is just a small observation, it is easy to work around - either don't use excel(!) or just save the exported file with notepad or similar first. Next, open with excel using the little wizard thing that it has to open csv files and make sure to set the relevant columns to text. Then it works fine.

Cheers

James

http://www.pharmacys.com.au/

I think so. You're using the "alternate SKU" feature as documented in http://www.ubercart.org/docs/user/10962/product_skus_and_adjustments , right? If so, I think you'll be fine. In fact, this is the very reason that I export the stock levels as a separate file, instead of including it in products.csv, which only has records for the "primary" SKUs.

Anyway, give it a shot and let us know how you go.
cheers
David

No worries - not whingy at all by some standards!

OK, I think I get it now. It's actually a problem with the way Excel opens the .CSV file when you click the "Export ... to CSV file" link, right? Presumably it's opening the CSV file using some default settings that drop leading 0s in field values. A few ideas spring to mind...

• As you say, don't use Excel. OpenOfffice opens CSV files with an initial "import" dialog that lets you fine-tune the field types if you need to.
• There might be a setting in Excel that lets you specify that all fields in a CSV import are text, or at least makes it open CSV files with the wizard you mention.
• You could probably just do a "save as..." (right click menu in Firefox, maybe same in IE) on the "Export ... to CSV file" link, instead of getting wordpad involved. Once you have the CSV as a local file, open it with Excel using the import wizard as you describe.

cheers
David

Hey David,

Thanks for the quick response. I should have been more clear, I AM using the alternative skus , which is my problem. When I download the "stock" csv from your module, it only includes the primary SKUs, as you said, but I need to be able to edit the "stock" of the sub-skus.

I've been looking through the code of your module (well written, btw) as well as the sql schemas UC2 uses, and I'm having trouble figuring our where the stock levels are stored for the sub skus. As far as I can tell, the "model" in the uc_product_adjustments schema is a sku that combines the product sku and the options chosen for each attribute. I would assume that UC2 would then treat this as any other sku for stocks purposes, but that doesn't appear to be the case.

thoughts?

I just went back through and read your response #2 on this thread, and understand it much better now after coming up to speed. I've spent the afternoon trying to code the stock ability into the products-options.csv. I'll keep trying, but any insights you have would be appreciated. I'm not sure what to join the tables on, because, like you said, stock is tracked off "sku" while that is not a key in the other relevant schemas.

Any idea why this is set up so backwards in the first place? seems like their would be an easier way to do this

I don't think it actually "combines" the product's primary SKU with anything - it holds whatever SKU you supply as the alternate SKU for a particular set of options for a product. I suspect there's no uniqueness restriction either - it might or might not match any other SKU (also known as "model") in the system.

Here's my current understanding of the relationship between SKUs and products:

• One product can have several SKUs. This happens when you assign an alternate SKU to a certain combination of options in the "adjustments" tab.
• One SKU can be assigned to several products. This happens when you simply give two or more products the same SKU. I can think of a couple of reasons why you might want to do this. One is the student-pricing scenario in #7 above. Another is if you have, say, a box of 10 widgets. You might want to sell them individually for one price (product "A"), but a whole box at a discount (product "B"). On the shelf, however, you only want to have to deal with one SKU, and you just keep one box open to cater for the one-off purchases.

Given this arrangement, it's difficult to get the stock level into products.csv cleanly. It raises the possibility of duplicate SKUs on import, and the ambiguity that then arises with stock levels . Getting stock levels into product-optiuons.csv would be even harder.

You will need to have set the stock for the alternate SKU to be "active" before exporting the stock levels CSV. I'm pretty sure this goes for the primary SKUs too

I haven't tested it, but this module seems to be exactly what I need for one of my customers!
The only problem is his website uses Drupal version is 5.x and Ubercart version is 1.9.

Anyone has experience with using this module on drupal v5.x? Or maybe someone ported it?

Thanks

/Sebastian

Thanks David,

It sounds like using attributes and tracking the stock on them is not a viable option for me then. I'm tracking about 500 products (I sell plants) in different sizes, and need to ability to mod prices and stock from a .csv. The stocks file (attached as .csv.txt for uploading) didn't t track the sub-skus unfortunately. I went into the settings and verified they were all active, am I missing something?

Anyway, to avoid redundancy in pictures and info for the plants (same pics ans info despite what size I sell it, just different prices and stocks) I was hoping to use the attributes, but my plan now is to implement as skus without attributes for the time being until I have more time to tinker. Thanks for your help, and I'll post a solution if I find/write one.

Jon

Someone tried to port back the module this module was based on. no idea if it works, but might be worth a shot for you.
http://www.ubercart.org/contrib/12428#comment-50007

You've just made my day, great work!
Could the module be modified to update multiple fields? Like for instance if you want to change the title of the product AND the product price?

I imported my products using node import, but it did something funny, apparently, because this module worked like a champ when I entered a product with attributes by hand, both import and export.

Thanks for your help David,

I thought it was funny too that the skus didn't come up in the stock either. I'll post this on the forums for node import with ubercart as well. Node import saw the options you tell it to bring in, but must mess up the sub-skus. Even when you go to set the alternate skus on the edit page and set the alt skus they do not map properly and show up in the stocks.csv. thanks again,

It probably could, but I think it might be a case of feature-creep. The thing with stock and price levels is that they change frequently and in bulk, whereas titles and other fields change rarely, and generally one at a time.

Hi David

Agree it's total scope creep - but echoing samdds - the need to update more fields via csv is great...

http://drupal.org/node/422282 - Update existing nodes on import - gives you an idea of the demand...comment #110 is a good summary - you get a mention too!

My usage would be to update my free-tags - for example, tagging a set of products as "sale item" or "10% off" - at the moment it has to be done by hand which takes ages.

Another use would be where you make a mistake and have to change the category of 300 products by hand - but lets not go back there!!! - using a stock & price updater methodology would be brilliant for that...

If you don't ask and all that!

Cheers

James

http://www.pharmacys.com.au/

Using - http://drupal.org/project/views_bulk_operations

Allows me to update taxonomy and a whole lot else - another ace Drupal module...

(Just thought I'd share)

@james peck
views bulk operations don't have ubercart pice update

the module works like a charm on my price updates
(did not try attribute price adjustments, just simple list_price adjustments)

thanks

Bless you for this module. This is exactly what I've been looking for. I'm also using the Price Per Role module for use with wholesale customers.  Any way of modifying to include my role price for export and import? Thanks again!

I've not used the Price Per Role module, but I'll have a look at it. I've been thinking I should probably create a version for the upcoming Ubercart 3. While I'm at it, I might add some kind of "plugin" mechanism (maybe by defining some hooks) so that people can write helper modules to implement this kind of thing.

Thankyou for this brilliant module, have may products to update ! And it is ideal, a question....., could i use this module to change sku ? I have several products imported that did not retain the original sku and some how have "adopted" the product name as sku (using product import/export), so i was wondering if i could (by some modification..........i am not a coder !(yet))!! I have tried the module to simply print out the .csv and then alter the sku's but i get an error ! Any ideas drupalers ??
Any help would be just fantastic !

Maaak

I think what you're proposing might be feasible. Note that products and SKUs do not have a simple 1:1 relationship (see comment #18). Probably the best approach would be to introduce a new column in products.csv and product-options.csv where the new SKU could be entered. One problem that springs to mind though is that SKU is used as a key when uploading stock-level.csv, so changing SKUs would essentially invalidate any stock-level.csv you had previously downloaded. I'll put this on the to-do list and have a bit more of a think.

Thanx David ,I look forward to the reults of your head scratch !

This is Excellent module! Thanks a lot.

Now i am able to update all 800+ product price in one go.

Also I am looking for a Bulk Taxonomy Change module.

Regards,
AnAnD

Hi Dave, How do i add a column for name of product in the export .csv so i can more readily identify the products ?

i have had a brief look at your code ,my coding skills are zero !

'product-options.csv' =>
array('_uc_stock_update_export_product_options',
'product_options',
t('product option prices'),
'_uc_stock_update_import_product_options',
array("Product_Id", "Product_SKU",
"Option_Id", "Option_name", "Cost", "Price"),
array(0, 2),
array(4, 5)
)
);
}

i presume i would need to ad something here .as in "Product_name"

thanks,

mark

Hi Mark,

First you need to modify _uc_stock_update_export_product_options() to write the title field to the outpuit file. Something like this should do the trick (changes in bold):

function _uc_stock_update_export_product_options($csv_filename, $headings) {
  return _uc_stock_update_export(
             array('nid', 'title', 'oid', 'model', 'name', 'cost', 'price'),

             "SELECT {node}.nid, title, oid, model, ".
             "{uc_attribute_options}.name, ".
             "{uc_product_options}.cost, {uc_product_options}.price ".
             "FROM {node} INNER JOIN {uc_product_options} USING (nid) ".
             "INNER JOIN {uc_products} USING (vid) ".
             "INNER JOIN {uc_attribute_options} USING (oid) ".
             "ORDER BY model",

             $csv_filename, $headings);
}

Once you've done that, you have to adjust the array returned by _uc_stock_update_formats() to cater for the new field. e.g.

      'product-options.csv' =>
      array('_uc_stock_update_export_product_options',
            'product_options',
            t('product option prices'),
            '_uc_stock_update_import_product_options',
            array("PRODUCT_ID",  "product_name", "OPTION_ID",  "sku",
                  "option_name", "Cost", "Price"),
            array(0, 2),
            array(5, 6)
            )
      );

I haven't tested this, but assuming it's free of typos, I suspect it'll work. Let me know if there are any glitches.

Cheers,
David

Thankyou David,

I am sure this will also act as my intro into low level coding 101, i will give it a bash .

Thankyou for your time ,

if you ever hit our Southern shores (south africa) the beer is on me !

.m

Hi, did you ever progress this? I need to change 7000+ SKUs (to the barcode) - I've tried changing the code myself, but the "edit and replace" technique doesn't do it!
Cheers
James

Are you referring to #25? No, it's unlikely that I'll work on this unless I get a paying client that needs it. Sorry about that.

Hi,

I exported a file using Export data to CSV files and save it. I made some necessary changes to Sell_price without modifying anything else.
I import data from CSV file using an option Import product prices CSV file: and update product prices.

Once I update product prices it says "Importing data from CSV file" and starting import. It goes for quite a while and not uploading a CSV file. my CSV file is small in size.

Can someone help me for this particular situation.

Thanks In Advance

Sorry its working well...

Thanks

This is a nice module, and it works fine.

Hopefully someone can help me a little bit:

(1) Is there anything that speak against using one file to update price, stocks,.. at once ?

(2) Can i write the CVS files to the filesystem ? How could i save a new CSV when a stock reaches X or when a product is purchased ?

(3) In the other direction: Can i use cron to look at /site/whatever/here_is_the_new_stock if there is a new CSV and if yes update ? Can i trigger this from another programm that drupal looks for a new CSV ?

(4)Is it bad or OK to update Stock every 5 minutes ?

(5)Any chance for a D7 port ?

Thx a lot and if this doesn´t work right now i willing to donate a smallish bounty_/maybe other need this to ?

EDIT: There is no rules integartion obv but it would be very nice if it would be possible to make a rule "save new list when a stock is updated"

(1) See comment #18. Strictly speaking, there is a many-to-many relationship between products and SKUs that mitigates against putting them in the one CSV file. However, if you do have a one-to-one relationship between products and SKUs, you can construct a single CSV file that has all the necessary column headings and use it as a common input file for all three uploads.

(2), (3) For this you'd have to write a module to call uc_stock_update_import_submit() (for update), or write something based on _uc_stock_update_get_csv() (to dump the CSVs) and call your module from a cron job or something. Note that the update code uses the Drupal batch API, so you might have to do some work to make this run from a cron job. See http://drupal.org/node/873132

Alternatively, you might be able to run curl or wget from a cron job to GET or POST a CSV, but you'd have to get it to authenticate to Drupal first.

(4) My guess is that it's probably OK as far as Drupal and Ubercart are concerned, but you will want to check your caching configuration and the cache headers on your pages to check that the updates will be seen in a timely fashion.

(5). Yes - after Ubercart 3.0 is released.

Hi David, Thanks a lot -> I´ll have a look at it.

I was using this for a few months and it worked great. Last month it suddenly stopped working. All I get when importing is the below message, for every single product. It even does it when I export prices/stock and then immediately import the same file. Any ideas?

"Couldn't find an existing entry matching ([SKU], [node number]). Skipping this row."

Have you created any Drupal "revisions" of your products recently? The updater will only update the current revision of a product.

Can you visit /node/$one_of_those_node_numbers ?

What about /node/$one_of_those_node_numbers/edit ? Does the SKU there exactly match the one in the corresponding error message? Have any leading zeros been dropped?

Please post a few of the actual "Skipping this row." messages.

The only thing I can think may have done it, was delete old stock number from the database because I couldn't find another way of doing it. I restored the db from backup immediately after but it made no difference. I have tried to reinstall this module but it had no effect. Is there a db table I need to remove to completely uninstall the module first.
Here's a list of some errors. I've tested www.royalgems.co.nz/node/[number] which worked and SKUs match.

# Couldn't find an existing entry matching (CJ1, 368). Skipping this row.
# Couldn't find an existing entry matching (CJ2, 369). Skipping this row.
# Couldn't find an existing entry matching (CJ3, 370). Skipping this row.
# Couldn't find an existing entry matching (EDSS001, 399). Skipping this row.
# Couldn't find an existing entry matching (EDSS007, 471). Skipping this row.
# Couldn't find an existing entry matching (EDSS008, 246). Skipping this row.
# Couldn't find an existing entry matching (EDSS009, 472). Skipping this row.
# Couldn't find an existing entry matching (EDSS011, 37). Skipping this row.

That would definitely break updates of stock levels. Am I right in thinking that you're only having problems updating the stock levels?

Presumably you have access to an SQL prompt. If you don't have any uc_product_stock records, you should probably put them back. I think the "stock" tab in the product node edit page will create one if it doesn't exist.

Sorry, to clarify, I only delete old SKU from the db. For some reason, when you change the SKU, the old one stays in the db as well as creating a new one. All the ones I listed are still in the db, and are a sample from the .csv file create when exporting product prices. I haven't changed anything in the file, but it fails importing them. All the products also have stock enabled in edit/stock.

Are you saying that both product-price and stock-level imports fail?

I think you may have got your database into a weird state. Have a look at your node, uc_products and uc_product_stock tables. If you look at sites/all/modules/uc_stock_update/uc_stock_update.module you'll see the SQL "where" clauses involved - it's not particularly complicated and you should be able to figure out what's wrong by experimenting with a few SQL selects.

If it all looks OK, try doing an SQL update manually - it's possible that your database was not restored correctly and all updates on those tables are failing.

By the way, reinstalling the uc_stock_update module won't help here.

Problem resolved...apparantly. I was sure it was all three options, but my example was just for price. So I checked stock and product prices and they worked. And then I checked prices and it worked. I haven't even been on my website since I posted my example yesterday, so I have no idea how it got fixed.

Thanks for your help anyway...and a great module. It saves me hours of work.