Ubercart: Product Imports

When a client asked me to build him an eCommerce site that allowed him to occasionally re-upload his product catalog, it seemed like Drupal and UberCart would be the obvious solution. Both are well established projects with tons of high-quality free plug-in modules. It was a safe bet there was a collection of downloads I could assemble like Lego bricks into the perfect site. But, oddly enough, it turns out that the product upload capability was simply not supported.

I was a bit surprised at that, as I assumed this was the sort of request that a lot of vendors might have. Anyone with a large number of products, particularly anyone who is already tracking that data in some other format, would not want to have to maintain that process by hand. None the less, the gap existed, and it was up to me to find a way around it.

I should acknowledge that there are modules for uploading nodes in Drupal, which is essentially what a product is. Unfortunately, these tools have far too many 3rd party dependencies, are too thinly supported, and are too difficult to integrate to be considered ideal solutions. So I set out to build my own.

Now for anyone reading this, hoping for a solution to their problem, let me tell you exactly where this is going. I AM going to show you how I built the import solution. And I AM going to give you my code, an UberCart (v2.2 for Drupal 6) module, for you to freely use (and maintain). But you ARE going to have to tweak it to work in your environment. If there is anything I learned in this process, there is no simple way to do this that will work for everyone.

To start off, I needed to provide my client an interface by which they could delete old products and add new ones. To keep it as simple as possible, I consolidated those interfaces. Step one was to create a link to the tool using the menu hook.

  function uc_prodimport_menu() {
    $items = array();
    $items['admin/store/products/uc_prodimport'] = array(
    'title' => 'Import products',
    'type' => MENU_NORMAL_ITEM,
    'description' => 'Import products into UberCart.',
    'page arguments' => array('uc_prodimport_form'),
    'page callback' => 'drupal_get_form',
    'weight' => -2,
    'access arguments' => array('administer products'),
    );  
    return $items;
  }

As you can see, I chose to name my module uc_prodimport, or “UberCart Product Import”, to match the normal UberCart naming convention. In the $items array primary index value, I set the menu item to appear under the pre-existing Products adminstration structure. I used the “page arguments'” index value to make the link call the uc_prodimport_form function I am about to build.

  function uc_prodimport_form($form_state) {
    $form = array(); // reset array
    $form['#redirect'] = array('admin/store/products');
    $form['#attributes'] = array('enctype' => "multipart/form-data");
   
    $form['purge'] = array(
    '#type' => 'checkbox',
    '#title' => t('Delete current product set'),
    '#description' => t('For multiple uploads, uncheck after the first run. Purge occurs before import.'),
    );
   
    $form['datafile'] = array(
    '#type' => 'file',
    '#title' => t('Upload data'),
    '#size' => 48,
    '#description' => t('A CSV in the requisite format'),
    );
   
    $form['submit'] = array(
    '#type' => 'submit',
    '#value' =>  t('Submit'),
    '#id' => 'upload-products',
    );

    return $form;
  }

This is a form with three elements - A checkbox to choose whether or not to purge the current database, a Drupal file element to select the upload file, and a submit button. The $form['#redirect'] = array('admin/store/products'); setting sends the page back to the normal Product lists after the purge/upload to see the effects of your change.

  function uc_prodimport_form_submit($form, &$form_state) {

    // set datafile upload variables
    $source = 'datafile';
    $validators = array( 'file_validate_extensions' => array( 'csv' ) );
    $dest = file_directory_temp();
   
    // upload datafile
    $file = file_save_upload($source,$validators,$dest, false);
    if($file){
        drupal_set_message("File successfully uploaded as " . $file->filepath);
    }else{
        drupal_set_message('No file uploaded.');
    }        

    // purge tables of current products, if checked
    if($form_state['values']['purge']==1){
      // delete all product nodes
      $n = uc_prodimport_purge_nodes();
      drupal_set_message("$n products deleted.");
    }else{
      drupal_set_message("!!!No products were deleted.!!!");
    }
       
    //insert $file into current table
    if($file){
      $iInsertCount = uc_prodimport_import_new($file);
      drupal_set_message("$iInsertCount rows imported.");
    }
    //confirm process completed
    drupal_set_message("Process completed");
}

The default action for a form submission in Drupal is to execute a function named “module_formname_submit. The first thing the function does is import the data file, if it exists. I’ve already documented that process in detail, so I’ll move on to the next step - the purge. Here, a function I named uc_prodimport_purge_nodes is called. Let me jump out to that function.

function uc_prodimport_purge_nodes() {

    $amt = 8000; // how many items to delete in one go?
    $delnodes = array(); // array of node ids deleted
 
    $result = db_query_range("SELECT nid FROM {node} WHERE type LIKE 'product'", 0, $amt);
 
    $n = 0;
    while ($data = db_fetch_array($result)) {
      $delnodes[] = $data['nid'];
      node_delete($data['nid']);
      ++$n;
    }
   
    // empty out product tables
    // Note that TRUNCATE may only work with MySQL
    if(db_table_exists("uc_products")){db_query("TRUNCATE TABLE {uc_products}");}
    if(db_table_exists("uc_product_classes")){db_query("TRUNCATE TABLE {uc_product_classes}");}
    if(db_table_exists("uc_product_features")){db_query("TRUNCATE TABLE {uc_product_features}");}
    if(db_table_exists("uc_quotes")){db_query("TRUNCATE TABLE {uc_quotes}");}
 
    return count($delnodes);
}

The first thing to note is the $amt = 8000; line. I found that a large database could choke the web server, which could cause the script to fail in an unpredictable manner. The simplest solution was to set how many products I would try to delete at once to something I knew wouldn’t be a problem, and then run it multiple times, if necessary.

Next I select an array of node IDs of the appropriate type and count, then start to loop through the nodes, invoking the Drupal “node_delete” function to cleanly remove them from the site. Where ever possible, I used Drupal functions rather than straight SQL to avoid data corruption issues. Then I TRUNCATE the product table to quickly and cleanly empty them. This is a very fast operation that essential drops and then recreates the tables, but if you’re not running MySQL, it might not work with your database. Note the reference to uc_quotes. If you aren't using the module, you'll obviously not need this line.

Back to the form submit, I next call the meat of the module, the “insert” function.

function uc_prodimport_import_new($file) {
    drupal_set_message("Inserting new data into product tables");
    // initialize counters
    $iInsertCount = 0;
   
    // request an array of values to be imported
    $aProds = uc_prodimport_read_file($file);
   
    // confirm file contains expected number of columns
    if(count($aProds[0])!=18){
        drupal_set_message("!!!Unexpected column count on row in import file!!!");
        return $iInsertCount;
    }
   
    // remove the title row
    unset($aProds[0]);
   
    // loop through array  
    foreach($aProds as $aProd){
        $node = new stdClass();    
       
        // Your script will probably pull this information from a database.
        $node->title = $aProd[0];
        $node->body = $aProd[1];
        $node->teaser = $aProd[2];
        $node->type = 'product';   // constant
        $node->created = time(); // constant
        $node->changed = $node->created; // constant
        $node->status = 1; // constant
        $node->promote = 1; // constant
        $node->sticky = 0; // constant
        $node->format = 1;       // Filtered HTML - constant
        $node->uid = 1;          // UID of content owner - constant
        $node->language = 'en'; // constant
        // If known, the taxonomy TID values can be added as an array.
        $node->taxonomy = array(1,); // constant
        // product specific node fields
        $node->model = $aProd[3];
        $node->list_price = $aProd[4]; //formatting required
        $node->cost = $aProd[5]; //formatting required
        $node->sell_price = $aProd[6]; //formatting required
        $node->weight = $aProd[7];
        $node->weight_units = $aProd[8];
        $node->length = $aProd[9];
        $node->width = $aProd[10];
        $node->height = $aProd[11];
        $node->length_units = $aProd[12];
        $node->pkg_qty = $aProd[13];
        $node->default_qty = $aProd[14];
        //$node->unique_hash is handled by product api
        $node->ordering = '0';
        $node->shippable = '0';
        $node->shipping_type = 'small_package';
        // call insert function
        $inserted = uc_prodimport_insert($node);  
        // increment counter
        $iInsertCount++;    
    }
    return $iInsertCount;
}

This function starts by importing the file and assigning it to an array. That process was discussed previously and is in the source, anyway. The next step is to make sure there are the right number of columns in the source data, which is cheap way to make sure you didn’t upload the wrong file. Then I pop off the top row because, in my input file, this is just a title row to make it easier to read the import file. Finally, I create a new $node object, assign it the values from my rows array, and insert the row into the database with the Drupal uc_prodimport_insert function. This invokes the node insert hook and makes sure the appropriate add-in tables, like those associated with UberCart’s Product module, are correctly updated.

This returns the inserted rows count to the calling submit function to use in feedback messaging to, for instance, ensure that you got more than 10 rows out of a 5000 line file. At this point, the product list loads, and you can see the results of your efforts.

And that’s basically it. I encourage you to tweak the attached code to match your own needs and give it a run. As with any other code I post, your mileage may vary, but this implementation works for me. Good luck!

AttachmentSize
File uc_prodimport.7z9.52 KB
File sample.csv_.7z322 bytes
File uc_imageurl.7z7.82 KB

Comments

I got a request for a sample import file to be used with the code, so I added "sample.csv_.7z" to the original post. A couple things to be aware of:

1. The contents of the file are imported as part of the uc_prodimport_read_file function, with each field inserted into the array that is passed to the uc_prodimport_import_new function.

2. This being PHP, the first column in the CSV will be $aProd[0], and the last will be $aProd[x-1].

3. You can ignore file content, as I have in this example, by simply not assigning the value to a node element. For example, my sample file has a "shipping type" at $aProd[17], but I chose to hard-code that value since it never changed. If I were tidier, I'd remove it entirely from the CSV.

4. If you make a change to the CSV format, make sure to update the column count test in uc_prodimport_import_new. If you get an error message that says, "Unexpected column count on row in import file!", well, that's what it means.

Dear Dan!

You save the day with posting this module to public. So Big Thanks for that! It's realy a must have module if you run a webshop.

I personaly have only one problem:

When importing products with your module, the add to queue button appear in different language, what I need.
I modified the
$node->language = 'en'; // constant to
$node->language = 'hu'; // constant and it's importing well in the SQL database.

So as you can see i need hungarian add to queue button :), but it appears in english.
If i make a product manualy then it's everything OK, i have my own buttons.

Can you help me with this?

Thank you in advance.

The easiest way to achieve this in Drupal is to use the Localization API. Enable the "Locale" module in your Admin screen, and set up a translation of "Add to queue" to "Adjunk hozzá a sor" (just an online translation). Then make sure to wrap any words you want translated in "t()" functions.

Hope that helps.

Hello,
My question is. Is this a module, called uc_prodimport? Should it work if I only install the module?
Other question, are product exports supported as well?

Q: Is this a module, called uc_prodimport?
A: Yes.

Q: Should it work if I only install the module?
A: Probably not. You'll need to tweak the import criteria to match the profile of the data you are attempting to import.

Q: Are product exports supported as well?
A: No. Sorry.

* Operating in off-line mode.
* File successfully uploaded as /tmp/sample.csv
* !!!No products were deleted.!!!
* Inserting new data into product tables
* Reading data file sample.csv
* /tmp/sample.csv deleted
* !!!Unexpected column count on row in import file!!!
* 0 rows imported.
* Process completed

After a lot of reading your solution seems to be the best. I was wondering how much you would charge to do a small customize to your module?

... on your definition of "small". Send me an email (see the link by my picture) or a PM and we can take this offline.

On another note, I'm glad you found this to be a good solution. I personally think it's a bit of kludge, and it shouldn't have to exist, but I guess it's not enough of a priority to make the features list for the core UC distribution.

Dear Dan!

You save the day with posting this module to public. So Big Thanks for that! It's realy a must have module if you run a webshop.

I personaly have only one problem:

When importing products with your module, the add to queue button appear in different language, what I need.
I modified the code and it's importing well in the SQL database.

  $node->language = 'en'; constant
 
-->
   $node->language = 'hu'; constant
 

So as you can see i need hungarian add to queue button :), but it appears in english.
If i make a product manualy then it's everything OK, i have my own buttons.

Can you help me with this?

Thank you in advance.

Thanks a lot for posting this module, it helped me a lot with my adapted prodimport module.
In the code you use the module uc_imageurl and this sounds like an interesting one. Unfortunately I wasn't able to find it, could you point me in the right direction (or mail me the source).

... and I (eventually) comply. Sorry - had to dig deep to find this one. Hope it helps.

I was wondering could this be translated for Drupal 7? Where can I look for it (assuming it would not work for D7?)
Also if this could be expanded to support adding/importing taxonomy/categories and options as discovered in the CSV that would be great. Are there a couple of API calls I could be looking into to do this?

I'm no longer working this project, so I couldn't give you a definitive answer. Since my code changes are all stand-alone modules, I don't see why they couldn't be ported to the latest versions, however. Just crack open the code and look at what the "modern" equivalents" are. I can't imagine they've changed *that* much.