Sometimes it is more efficient to edit the MySQL database directly than manipulating your inventory within Drupal. Don't worry- it is not that difficult.

Now that I've had some time to work with a Drupal 5.x and Ubercart 1.x store, I must say that I'm very impressed with what these two can do together. Ubercart leverages many other modules from the Drupal community, and bundles them into an impressive package.

That being said, I found some challenges relating to importing and exporting data. Most online stores have a large amount of data right from the start due to the product inventory. Fortunately, most of these product nodes are repetitive in structure, and lend themselves to relatively painless bulk editing. There are a couple of techniques you can use to import, export, and manipulate your inventory data.

Node Import

As a general rule, I look for solutions that operate through the drupal interface as a first choice. This exposes me to the workflow that my client is going to see plenty of. I'm more likely to spot a UI issue or expose a code glitch if I'm putting the site through its paces as I work.

Node Import is a module that allows you to import data in a CSV file directly into a series of nodes.
This module isn't just for Ubercart, but it is especially useful when faced with the task of importing hundreds of product nodes.

Ubercart's product nodes are unique to Ubercart, having some specialized fields that pertain to a product's properties. One in particular is attributes, and their associated options. Several members of the Ubercart community have created an excellent add-on to Node Import that enables the import of product attributes and options.

As with any new module and workflow, you will need to practice the import process on your testing server before you go anywhere near your production server. Structuring the CSV file properly and mapping the columns to node fields is not difficult, but it just takes a few tries to familiarize yourself with the process.

Updating Products

A challenge arises when you need to update your products in bulk. It doesn't take long for an active inventory to change- quickly taking you away from the original data in your CSV file. Prices may have changed, titles, options, and pictures may be completely different in a very short time.

There exist a number of patches and modifications created by others with similar needs. In my experimentation, these function as intended, but with an important caveat: nodes are compared by title, so if there's any difference between the titles in your database and the titles in your CSV update file, you may end up with multiple products in your catalog. Furthermore, the nodes that do get updated are replaced with the data you are importing. Again, if you've had any changes to the product nodes since you original import, you'll lose that information. Ideally, you'd want a way to export your catalog to capture current node data, and merge this data with your updates, and re-import. There's another way that can save you some time by targeting the precise tables and fields in your Drupal database.

Database-Diving

This is, admittedly, the first situation where I have edited a drupal database directly. I prefer to employ it as a last resort, because it is an easy way to break your site. Therefore, it bears repeating: practice extensively in a testing environment, and save/export liberally.

This will give you a chance to become familiar with how Drupal, and furthermore Ubercart, store their node information. Browse the tables in your database to get a feel for where things are stored. By changing the data in your tables, you'll be able to change a large number of nodes with relative ease. The procedure starts by making the change to one node, assessing what changes took place in the database, and duplicating this change as necessary for multiple products. Your Drupal installation will never know it happened.

As an example, suppose your client asks for all products to have an additional option added to an existing attribute. Perhaps an additional size or color has been made available to a t-shirt store. First, add the option to one product, so we can see what changes are made in the database tables.

Go Administer › Store administration › Products › Manage Attributes›, and find the attribute you wish to modify. "Options" gives you a list of options, use weight and order to control the list order. Once you've got one product set up the way you'd like, you can duplicate it for the other billion products in your catalog.

This example employs PhpMyAdmin for database work. It is certainly not the only tool available, but it is fairly user-friendly, and provides a reasonably nice GUI for interacting with your database.

Look up your uc_attribute_options table. You'll see your options listed including the new option you just created. Remember the option ID, or 'oid'. This number will identify the option in other database tables.
uc_attribute_options in PHPMyAdmin
Go to your uc_product_options table. The "nid" column stands for Node ID. Each nid corresponds to a product node. If you sort the table by the nid column, you'll notice the nids repeat for each enabled option, or "oid". Find the one product node that you updated manually- it should be the only one with the new oid. Your goal is to add a new row to this table for every product nid that gets the new option.
uc_product_options in PHPMyAdmin
Export this table a CSV, and open this up in your spreadsheet application of choice. You'll need to arrange this data such that you have a single row for every nid that is to receive the new option. Delete all other repeated nids. Remove any nids associated with products that do not recieve the new option. (You can reference nids to titles by viewing the node table) Make sure the oid column contains the oid number of your new option- the one listed in uc_attribute_options.
Editing the CSV in a spreadsheet
Adjust any data in the Cost, Price, Weight, or Order columns, and export this data as a CSV (no column headers).

Return to PHPMyAdmin, and go back to the uc_product_options table. Make sure you click on the "browse" tab so you're in the table view. Click on the "import" tab, and locate your CSV. You may need to set the "Fields terminated by ___" field to a comma in order to get a successful import.
uc_product_options import
If your import was successful, you'll get a satisfying message from PHPMyAdmin. Take a look at your table to make sure it looks as expected, and then go back to your Drupal site. Your new option should appear for all the affected nodes.

There are plenty of other changes that can be made in similar fashion. Need to change the default option for a particular attribute? Its called "default_option" the table "uc_product_attributes". Need to change a CCK field? These appear in the table "content_type_product". Note that you have the option "Replace table data with file" at import. Remember to check this if you are updating fields on existing nodes. Otherwise, you'll end up with duplicates.

By following these steps,

1. Make the change to one node
2. find affected tables in database
3. export affected tables to CSV.
4. import CSV into spreadsheet
5. quickly replicate addition or edit existing data
6. re-import CSV of altered data

you'll be able to change a large number of nodes with the ease of a spreadsheet. Good luck!