Stock & Price CSV Updater

Submitted by jorditr on Thu, 08/06/2009 - 14:14

Contrib type:

Module

Status:

Initial upload

Moderation:

Awaiting moderation

Downloads

Compatibility:

Ubercart 2.x

View all releases

UC Stock & Price Updater is a module that tries to cover a hole on the Ubercart ecommerce system: being able to update stock level and prices at once from a single file. Use this form to import stock data and sell prices into a Ãœbercart online shop from a CSV file. That way every day a real store or wharehouse for a group of physical store will be able to export from the central stock system a file with real stock levels that can be imported into your online Ubercart store. Imagine what would happen if you try to update stock levels every day on 500+ products store...

HOW IT WORKS: This is not a difficult module:

HOW THE CSV FILE HAS TO BE STRUCTURED: This is not a difficult module:

HISTORY: I'm not an expert programer so I tried to find some module doing something similar to try to understand how a .csv file could be openned and data extracted to be inserted on the database. After reading many forum threads at ubercart.org and drupal.org I found Dennis Stevense's taxonomy_csv.module. That module imports taxonomies but it opens .csv files and has a nice progressive bar giving interesting messages. Finally I discovered how to convert it to the sort of module capable to do the task I requiered.

HOW TO ADAPT IT TO YOUR NEEDS: Maybe the module works for you because you try to update just the same data that the module update right now: stock (level), sell_price and list_price. If that's not your case just play with the uc_stock_update_import_line function where you add to an array the different colums on the .csv file. Every line is sent to the function uc_stock_update_db where every column is sent to update the proper column on the database tables. If you have a low level of knowledge on PHP shouldn't be difficult to customize it.

FUTURE: Don't expect from me many updates, the module is ready to work and I see it as a base for web developers for building their update arrays inside it with just a small re-coding. Could the idea be implemented on core Ãœbercart? It would be a wonderful idea if the interface was reworked a little or expanded to cover a wider range of updating possibilities.

I just hope it could be useful for a lot of people, and it could save you all the time I had to invest on trying to solve that one-time-update of all your store issue. Maybe it's not the best module you could imagine but it does the work. Although my PHP skills are limited I've wanted to share it in returning for being able to use Ã¼Ãœbercart and find on that community so much information. If any skilled programer has ideas to improve don't hasitate to share your thoughts and code here

Development

View pending patches

Preview	Attachment	Size
	uc_stock_update.zip	9.06 KB

Wed, 08/26/2009 - 19:36

#1 View page

Prasad Shir

Offline

Joined: 08/06/2009

Juice: 6

Wish to test the module

Hello

This module sounds very promising & I would like to test this module. I cannot see any download link for this module's code. Where & how can I get this code for testing?

Prasad

Sun, 08/30/2009 - 11:54

#2 View page

jorditr

Offline

Joined: 10/31/2007

Juice: 256

Re: Wish to test the module

It's true, I didn't realized that I had not uploaded the file (I think it was some trouble when I uploaded the information). Here it is.

Mon, 08/31/2009 - 08:42

#3 View page

Prasad Shir

Offline

Joined: 08/06/2009

Juice: 6

Suggested changes

Thanks for uploading the file.

I tested the module & it is working, with some issues as explained below.

I observed that if any columns or cells are empty, the respective value in the database is set to NULL by the module. This could be potentially problematic. E.g. I tried imported ONLY stock figures & kept the other two columns (Prices) empty. This resulted in all the prices of all the products getting erased from the system!!

To overcome this problem, I would suggest this solution:

on line 192, you have

db_query("UPDATE {uc_product_stock} SET stock = %d WHERE sku = %d",
      $stock_level, $ref);

wrap this with IF clause like this:

 if ($stock_level!=NULL){
  db_query("UPDATE {uc_product_stock} SET stock = %d WHERE sku = %d",
      $stock_level, $ref);
}      

Similarly for Price updating code, instead of

  db_query("UPDATE {uc_products} SET sell_price = %f, list_price = %f WHERE model = %d",
      $sell_price, $list_price, $ref);

use IF clause to check if the respective variable is not empty & then only update the DB values.

 if ($sell_price!=NULL){
  db_query("UPDATE {uc_products} SET sell_price = %f, list_price = %f WHERE model = %d",
      $sell_price, $list_price, $ref);
      }

I am also a non-coder like yourself! So anyone with more Drupal coding experience may suggest even better way of handling this!!

Prasad

Wed, 09/30/2009 - 04:00

#4 View page

park27

Offline

Joined: 06/18/2009

Juice: 6

Only works with numeric skus

This is exactly what I need for a project I'm working on, but in my tests I can only get it to work with numeric skus. If the sku contains letters all of the product attributes are given the same stock level, which is always the last one on the .csv file. Has anyone else found this, or does anyone have a solution?

Thanks

Chris

Wed, 09/30/2009 - 10:42

#5 View page

park27

Offline

Joined: 06/18/2009

Juice: 6

Non-numeric skus

I worked it out, the %d placeholder is for an integer, so if you change it to %s for a string it all works fine:

db_query("UPDATE {uc_product_stock} SET stock = %d WHERE sku = '%s'"

I was showing my Drupal ignorance there!

Sat, 10/10/2009 - 10:44

#6 View page

jorditr

Offline

Joined: 10/31/2007

Juice: 256

Re: Stock & Price CSV Updater

I'm glad you really found it so usefull, I really think it is

About your comments on the string assigment, it simply means that everyone can write the SQL they need. I've been thinking a lot to offer a big textarea where everyone could write their own SQL piece of code. Anyway, I think it's a "developers module"

Fri, 11/27/2009 - 03:52

#7 View page

cyaneo

Offline

Joined: 10/18/2009

Juice: 58

Is there any export module...

...out for this module?

I search for a tool/module where I can export my products to the needed csv format, modify the prices and import it via Stock & Price CSV Updater.

Any idea?

Thank you + regards

Fri, 11/27/2009 - 10:35

#8 View page

calbasi

Offline

Joined: 03/01/2009

Juice: 107

Te bona pinta :-p

Hola Jordi,

Encara no l'he provat, perÃ² te bona pinta! Has canviat el codi per corregir els "bugs" que et comenta en Prasadh?

Salut!

---
calbasi.net
If you can not understand me, it's my poor English skills fault

Sat, 01/02/2010 - 04:21

#9 View page

susanb

Offline

Joined: 01/02/2010

Juice: 3

How to export the data the 'lazy way'

I also searched for a way to export my existing 'products' in a way so that I could easily update things and re-import them as described here. After a futile attempt at finding nothing this is what I did:

1) I created a 'View' for all nodes of 'type product' and defined my fields for display as: SKU, Stock Level, Sell Price, List Price.
2) I made the 'View' display as a table and in seconds I had a lovely list of everything the way I wanted it.
3) Copy, Paste, Change in Excel (or whatever program you want to use)...

Now, after changing all things to the way I want them, I am hoping this will just upload easily as described here and all will fly according to the SKU!

I hope that helps you.

Susan

Wed, 02/17/2010 - 14:42

#10 View page

Jurgen8e

Offline

Joined: 10/02/2008

Juice: 87

Backport drupal 5

Hi,

I have tried to backport it to drupal 5, but it doesn´t work, can somebody look at it. Or ask someone to look at it.

I use Ubercart 5.x-1.6 and Drupal 5.2
(Attribute 5.x-1.6)

Attachment	Size
uc_stock_update5.zip	8.96 KB

Thu, 05/20/2010 - 03:37

#11 View page

BayerMeister

Offline

Joined: 07/21/2009

Juice: 197

So what does it do?

Say you have a product, say a brick. The brick comes in sizes and quality classes. That makes 2 attributes with 3 options each.
The product has a base sell_price of $1.
-sizes: S (+$0), M (+$0.2), L (+$0.5)
-quality: C (+$0), B (+$0.3), A (+$1)

The combo of size: M + quality: B has an SKU of "MB" etc.

The CSV file you are going to import states just that "MB sells for $2".
Or it states all combinations with their respective prices, it doesn't make a change.

What can you do about it? What will the module do?
I guess you have thought about this stating this module supports attributes. (No offense, please, just searching for a solution)

Sat, 05/29/2010 - 23:06

#12 View page

david.houlder

Offline

Joined: 05/29/2010

Juice: 104

uc_stock_update-6.x-2.0

I just posted a major rewrite of this module to http://www.ubercart.org/project/uc_stock_update-2x

Since it's a major update with built-in export functionality I decided to make it a new contribution. Hope that's OK

David Houlder
http://davidhoulder.com

	User login
	Log in using OpenID: What is OpenID? Username or e-mail: * Password: * Remember me Log in using OpenID Cancel OpenID login Create new account Request new password

Ubercart