Well, we finally put the foot down with some of our clients who are clinging to their aging Zen Cart sites this past month, and convinced several to pack their bags for the bigger and brighter lights of Ubercart. There were a couple of reasons for us to open up this can of worms, but as I would come to find out, it definetely pays off in the end.
First off, we finally settled on Drupal as our CMS of choice after years of fiddling with all things Open Source (including Zen Cart), and Ubercart has become our eCommerce module of choice. Standardizing our secure hosting packages (read more here) means we can keep a tighter grasp on security releases by really focusing on what we do best.
Secondly, the Department of Revenue here in Washington decided to implement destination-based sales tax, and unfortunately none of our Zen Cart clients were willing to fund the development it would take in Zen Cart to implement. Thanks to Jennifer H up north, the Drupal/Ubercart community rose to the occasion and fulfilled the need.
And lastly, we were just tired of Zen Cart. The community was as lively as a funeral, and we could never theme the site enough to not look like a Zen Cart site. Having the flexibility of the Drupal CMS allows us to offer clients more, whether its blogs, integration with other services, or simply a nice looking site.
So where did I start?
After scouring the web, I ended up abandoning that ship and returning to my phpmyadmin/openoffice spreadsheet world and doing a bit of manual labor. I examined the Zencart database first, and noticed there were only really 4 tables of any importance to me: products (lists the products, descriptions and has the id needed to figure out the next 3), product_attributes (which products have which attirbutes), product_options (to get the names of the options), and products_options_values_to_products_options (mapping which options and attributes are on which product). After exporting those to a CSV file, I setup a quick pivot table, and viola, a csv file with every product, including options.
From there I used Node Import to import the list into Drupal/Ubercart. Attributes and options were a little difficult to figure out, so what I did was setup the attibutes manually, then imported each attribute as a seperate taxonomy vocab, and each option as a term. That allowed me to map the option values (terms) to nodes (products), and then I exported the taxonomy tables back to CSV, did a little mapping to make the taxonomy terms table fit into the uc_product_attributes and uc_product_options tables.
What came out was a perfectly mapped out Ubercart site with a system to check prices, since the nodes were still tagged with the option prices. Now, ideally node import would do that little import CSV magic automatically and a module could grab those values from the Zen Cart database directly, but I was able to do this using my Excel-foo and Drupal UI knowledge in less than 2 hours.
Got to love Open Source and Spreadsheets!