Stock & Price CSV Updater 2

Contrib type: 
Module
Status: 
Complete and working
Moderation: 
Awaiting moderation

Downloads

Latest version: 
7.x-2.1.1, 6.x-2.0.2
Compatibility: 
Ubercart 2.x
Compatibility: 
Ubercart 3.x

This module allows you to transfer stock and price data between an Übercart online store and CSV files. It can export product prices, stock levels and product option prices to CSV files and import updated versions of those same files to adjust prices and stock levels.

4 January 2014 Updated the Ubercart 3 version to fix an undefined variable error. See
http://www.ubercart.org/project_release/uc_stock_update2x_211

Overview
Originally derived from jorditr's original Stock & Price CSV Updater, this module has the following features:

  • Writes and reads RFC-4180 compliant CSV files (unlike code based on PHP's fgetcsv() function, which doesn't handle quote escaping correctly).
  • Uses column headings to identify columns. This allows you to reorder or add new columns in the downloaded CSV file before importing it back into your store. It also guards against accidentally importing the wrong CSV file.
  • Updates prices only for the current revision of a product, leaving prices for other revisions alone.
  • Handles both product prices and product option prices

See the module's README.txt file for installation details.

History

27 April 2012 The Ubercart 3 / Drupal 7 version uc_stock_update-7.x-2.1.0-beta1 is now available for testing. See
http://www.ubercart.org/files/uc_stock_update-7.x-2.1.0-beta1.tar.gz

This is a fairly substantial rewrite, so there are bound to be bugs and typos in this test release. Please let me know if you find any problems. I've redesigned the module so that it can be fairly easily extended to import and export extra fields and tables if required. At this stage I haven't tested this extensibility. If you're familiar with writing Drupal modules, feel free to write an extension module and let me know how you go. See the README.txt for more information.

2 March 2012. An Ubercart 3.x version is currently about 50% complete. It should be finished sometime in April 2012.

Updated 6 October 2011 version 6.x-2.0.2
(The "releases" feature of http://ubercat.org/projects/ seems to be broken, so I've gone back to using attachments - see below)

Updated 17 January 2011

It seems like 2.0.1 is working well for people, so I thought it was about time I made a formal "release" on ubercart.org - see the downloads section above. There's no new code - I've just used the uc_stock_update-6.x-2.0.1.tar.gz attachment below as release 2.0.1.

Updated 2 September 2010 version 6.x-2.0.1

  • Added work-around for bug in Internet Explorer that prevents downloading CSV files over SSL. See http://support.microsoft.com/kb/323308/en-us
  • Added product name to exported fields. This helps to identify each product.
  • Added active-stock flag to exported and imported fields
  • Adopted a naming convention for the column names. Columns used as keys on import have names in upper case. Columns containing new values have names that are capitalized.

PreviewAttachmentSize
uc_stock_update-7.x-2.1.0-beta1.tar.gz14.88 KB
uc_stock_update-6.x-2.0.2-0.tar.gz12.86 KB
uc_stock_update-6.x-2.0.1.tar.gz13.61 KB
uc_stock_update-6.x-2.0b.tar.gz13.26 KB
afif227's picture
Offline
Joined: 11/30/2010
Juice: 54
where to find the uploaded csv file

Hi ,
just a general question, I am trying to find the csv file which is uploaded by CSV Updater 2 to update price for the product range , b'coz I need to update some of the cell of this file dynamically . so that my drupal site can read the updated price which I gonna change using AJAX .

Thanks

pixel8's picture
Offline
Joined: 01/14/2010
Juice: 33
Stock updates for options

Hi,

When I click on the download link for "Export stock levels to CSV file." I only get 471 products downloaded yet there are over 2000 products including attributes. How come I'm only getting this few number of products downloaded for a stock update?

Thanks,

AttachmentSize
stock-level-1.txt 29.07 KB

Drupal Developer with Manchester Gig Promotions on the side and an Ubercart shop where you can buy sexy lingerie

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: where to find the uploaded csv file

The module doesn't actually save it anywhere. I don't have time to check the details right now, but I think PHP writes the contents to a temporary file and the module reads the data from that. See the PHP doco for more details.
cheers
David

David Houlder
http://davidhoulder.com

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Stock updates for options

Ubercart only generates stock records if you visit the "stock" tab on the product edit page and hit "save". I'm guessing that you're only seeing those products for which you already have stock records.

cheers
David

David Houlder
http://davidhoulder.com

flevato's picture
Offline
Joined: 06/29/2011
Juice: 10
Add "Alternate SKU" column to "product-options.csv"

Hi David,
Would it be possible to add a column to "product-options.csv" (like comment #35) for the "Alternate SKU" from the product's "Adjustments"? It seems to be possible because "stock-level.csv" has a column for it (titled "SKU"), I just can't figure out how to include it in "product-options.csv".

Thanks.

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Add "Alternate SKU" column to "product-options.csv"

There are several problems here.

For starters, a product option can "belong" to several alternate SKUs, so adding a single column wouldn't be a good general solution. I guess the file could have as many extra columns as needed (alt SKU 1, alt SKU 2, ...),.

Secondly, the uc_product_adjustments table (which holds the alternate SKUs), uses a column called "combination" to select the desired alternate SKU for a product. That "combination" column is described in the Drupal schema as 'A serialized array whose keys are attribute IDs and values are option IDs.'. This means that you can't get the alternate SKUs by using SQL joins with other tables. I think you'd have to pull out all the records from uc_product_options, then from uc_product_adjustments, decode those combination values, annotate the product options with the alternate SKUs, then dump the data in CSV format. Thats sounds like a fair bit of work to code.

David Houlder
http://davidhoulder.com

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Possible explanation for "Couldn't find an existing entry ..."

I'm following up on comment #46

Quote:

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

I've discovered that if all the new values in a line in an imported CSV file exactly match the corresponding values already in the database, this warning message can appear. I've only noticed this with MySQL, but may happen with other database backends as well. What's happening is that the database backend has noticed that the record doesn't need to be updated, so doesn't count that row as an affected row, and I'm erroneously displaying an error in that case.

So essentially, everything is working OK, but it's showing spurious error messages.

I'm hoping to fix this in the next few weeks (i.e. before mid September 2011)

David Houlder
http://davidhoulder.com

rafaelcr's picture
Offline
Joined: 08/24/2011
Juice: 6
Hi David, This module is

Hi David,

This module is absolutely amazing. It'll save me from hours of tough work.
I'm only having a small problem, though... when I start the batch process for importing a CSV file, the process gets stuck on "Starting import..." and it goes nowhere from that, no matter how long I wait. I've checked the code, made some debug print_r's to see if the batch process was being formed correctly and it is. I checked if the CSV is being read correctly and base64 encoded and it is.

Do you have any ideas on what I could check to see what's wrong? Maybe MySQL isn't updating the database correctly and it gets stuck at some point.

I appreciate your answer.

Rafael

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Debugging stuck batch processing

Hi Rafael...

Check that your browser is behaving properly. As I understand it, the Drupal batch mechanism spreads its processing load over multiple http requests, and this is done by making the browser issue a series of requests until the batch processing is complete. I haven't looked at the details of this mechanism, but my guess is that it might require javascript to be enabled, or rely on http-equiv meta tags.

So check that javascript is enabled, and snoop on your browser requests (e.g. firefox "live http headers" or use wireshark) to make sure that your browser is "cranking the handle" so to speak. If the batch processing hangs after the browser issues a GET or POST, it's getting stuck on the server. If the browser fails to issue a GET or POST after receiving the initial page response, it's getting stuck in your browser.

cheers
David

David Houlder
http://davidhoulder.com

rafaelcr's picture
Offline
Joined: 08/24/2011
Juice: 6
Solved

Duh. You were absolutely right. I had updated jQuery to 1.4.2 and forgot that batch processes needed 1.2.6... made the fix and it worked perfectly.

Thanks a lot, great module.

flevato's picture
Offline
Joined: 06/29/2011
Juice: 10
Adding product name to update as well

Is it possible to use the above (#35) for updating the product names as well? I tried the code and it works great for exporting the additional column, but doesn't seem to import it.

Thanks.

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Fix for for "Couldn't find an existing entry ..."

New version here:
http://www.ubercart.org/files/uc_stock_update-6.x-2.0.2-0.tar.gz
Fixed spurious "no match" error when new
values match old values. See
http://www.ubercart.org/comment/64614/possible_explanation_couldnt_find_...
Please let me know if there are any problems.
cheers
David

David Houlder
http://davidhoulder.com

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Adding product name to update as well

Pardon the delay - it seems like email alerts from this site are not working any more.
If you send me your modified code I'll have a quick look at it.
cheers
David

David Houlder
http://davidhoulder.com

flevato's picture
Offline
Joined: 06/29/2011
Juice: 10
Re: Product name addition gives errors with new patch

I also had the "Couldn't find an existing entry matching" issue pop up all of a sudden and I applied your patch, which worked great. But then I applied the modifications mentioned in #35 to add the product name column and the error returned. Could this be related to the new patch?

Thanks.

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Product name addition gives errors with new patch

I'm glad to hear the 6x-2.0.2 version is working OK for you. As for merging in my suggestions for handling product names, I can't think of any fundamental incompatibility off the top of my head. It sounds like it's time to put your debugging hat on. See http://api.drupal.org/api/drupal/includes--form.inc/function/batch_set/6... for details on debugging batch callbacks.

David Houlder
http://davidhoulder.com

sdfoui's picture
Offline
Joined: 01/03/2012
Juice: 6
ported to D7?

Hello
Is this going to be ported to D7?
Thanks

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: ported to D7?

Yes, I see ubercart 7.x-3.0-rc3 is now available, so I guess it's getting close.

I should be able to have a Drupal 7 / Ubercart 3 version going by mid February. Probably not a lot of work, just a matter of scheduling.

David Houlder
http://davidhoulder.com

sdfoui's picture
Offline
Joined: 01/03/2012
Juice: 6
re re : D7

Ok cool...
What can I do to help and advance the module?
thank you very much
Regards

v8comp's picture
Offline
Joined: 01/24/2012
Juice: 3
Price per role update

Great module, very simple and useful, thanks!

I needed the price per role update functionality as mentioned above, if anyone is still interested here is the patch.

AttachmentSize
uc_stock_update-price_per_role.patch 1.73 KB
Infinitee's picture
Offline
Joined: 02/19/2010
Juice: 95
Directly import of attribute options?

Hi David,

Great module! Especially for updating option prices. I was wondering if it can be modified to import/edit attributes as well as the associated options & prices?

Ralph Manis
Infinitee Web Design
http://www.infiniteewebdesign.com

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Directly import of attribute options?

Hi Ralph...

I can appreciate that this would be a nice feature, and probably feasible, but I think I'm going to call "feature creep", as in http://www.ubercart.org/comment/58331/quote_could_module_be .

I will be working on a Ubercart 3.0 port over the next week or so, and I'll see if I can put in some hooks so that other people can extend this module without having to patch it. That might allow other people to add some of the extra functionality suggested in these comments.

David Houlder
http://davidhoulder.com

jester's picture
Offline
Joined: 02/24/2012
Juice: 6
More fileds in output?

Hi, David!

First of all - thank you for great module once more time Smiling

And my question - is it possible to add more fields to import/export feature? I have products with several additional attributes, for example my distributor's SKU(it differs from mine).

My job would be much easier if I have a table with this data for editing and importing into Ubercart again.

Thanks in advance!

Sergii Liubovych

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
More fileds in output?

Hi Sergii...

How are you storing (or proposing to store) the distributor's SKU? I'm guessing you're not actually storing it as a product attribute, as that's for user-selectable stuff like colour, size etc. Sounds like a job for http://drupal.org/project/cck

I'm currently porting this module to Druapl 7 (Ubercart 3), and I'm proposing to add some Drupal hooks to make it possible to write extension modules that can import/export more fields as required.

cheers
David

David Houlder
http://davidhoulder.com

jester's picture
Offline
Joined: 02/24/2012
Juice: 6
Re: More fileds in output?

Yes, David, you are right - I'm storing distributor's SKU and distributor's price in additional fields in Product node type. But I don't see any way CCK can help me with your module Smiling

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: More fileds in output?

My mistake - I forgot Ubercart uses CCK behind the scenes to allow extra fields in product nodes.

Adding export/import functionality to handle those extra CCK fields would be a lot of work. The hooks I'm proposing to add to the D7/U3 version of this module will hopefully allow that kind of thing to be bolted on afterwards though.

If your price-updating workflow is exclusively by import from CSV files (i.e. you don't update prices using the Ubercart admin interface), then you could maintain a "master" spreadsheet that included the supplier sku as an extra column that would simply be ignored when imported into Ubercart. Generating that initial spreadsheet would involve exporting the prices to CSV and tacking on the supplier SKU column using whatever tools you have at your disposal (manually if necessary, maybe some spreadsheet relational-join operation if such a thing exists, or even old-fashioned unix sort and join commands if you're familiar with those). I'm not sure if that would help you or not - certainly a bit painful to set up, but once you have the master copy you can maintain prices in it and re-import it as necessary.

David Houlder
http://davidhoulder.com

wmsmith's picture
Offline
Joined: 04/19/2010
Juice: 45
Started getting a PHP error

I have been using this module for some time now, it is a real time saver, and last used it a couple of weeks ago on the same actual site.
Now when attempting to update prices in the existing site that was updated to D7.24 last week using the "Import product prices CSV file" option is now generating these log errors when attempting to import
Notice: Undefined variable: _uc_stock_update_import_chunk_max_time in uc_stock_update_import_chunk() (line 274 of /home/website/public_html/sites/all/modules/uc_stock_update/uc_stock_update.module).

Apart for the D7.23 to D7.24 and Ubercart 7.x-3.6 upgrade I cant think of anything else that could have changed since the last successful import into this site a couple of weeks ago.

Any pointers on where to look will be appreciated.

Have tested on another site and updating to D7.24 and Ubercart 7.x-3.6 made no difference on that site, the importer still works.

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Started getting a PHP error

I haven't tested this yet, but the following changes (declaring the variable as global) might fix the problem. Why it works in D 7.23 but fails in D 7.24 on just one of your sites may be due to a difference in the error_reporting setting. See http://au1.php.net/manual/en/errorfunc.configuration.php#ini.error-repor...

(The text below is a patch fragment. See "man patch", or manually apply it to uc_stock_update.module taking care to omit the leading "-" and "+" characters)

--- uc_stock_update/uc_stock_update.module      (revision 7)
+++ uc_stock_update/uc_stock_update.module      (working copy)
@@ -14,7 +14,7 @@
define('uc_stock_update_SEMICOLON', 1);
define('uc_stock_update_URL_ROOT', 'admin/store/products/update-stock-csv');

-
+global $_uc_stock_update_import_chunk_max_time;
$_uc_stock_update_import_chunk_max_time = 3;

/**
@@ -237,6 +237,7 @@
  */
function uc_stock_update_import_chunk($handler_class, $delimiter,
                                      $filepath, &$context) {
+  global $_uc_stock_update_import_chunk_max_time;
   if (empty($context['sandbox'])) {
     $context['sandbox']['file_offset'] = 0;
     $context['sandbox']['first_line'] = TRUE;

David Houlder
http://davidhoulder.com

wmsmith's picture
Offline
Joined: 04/19/2010
Juice: 45
uc_stock_update-2x 2.1.1

Thanks heaps for the update, have just had an opportunity to use uc_stock_update-2x 2.1.1 and there are no errors at all.
Cheers

kopeboy's picture
Offline
Joined: 02/21/2014
Juice: 12
Can I find this on drupal.org?

I can't find this module on drupal.org, why?

It is a very needed module since there isn't any other way I know to bulk edit stock levels.

david.houlder's picture
Offline
Joined: 05/29/2010
Juice: 115
Re: Can I find this on drupal.org?

I'll move it over to drupal.org ASAP.

David Houlder
http://davidhoulder.com