Importing Products Displays and related Products to Drupal Commerce

In this post I am going to look at Drupal Commerce and demonstrate how to prepare e-commerce product data for import into Drupal Commerce - and show how to create a delimited list of product references to link the products to their display nodes. These techniques supplement the wealth of great practical demos and documentation provided by the Commerce team.

Drupal Commerce

We have been developing a great new web site for a customer using the new Drupal Commerce.
Quite a journey - but well worth it and very inspiring.
If you are not familiar with Drupal Commerce then in a nutshell it is an e-Commerce extension to the core of Drupal 7 that benefits from many of the great advances in the latest version of Drupal - v7 and builds on the established history of Drupal's Ubercart e-commerce module. It has been written from the ground up as a completely new way of managing e-commerce within Drupal.

What is Drupal Commerce?

It is more than simply an add on module to Drupal - it integrates tightly with the core of Drupal to provide a robust and seemless deployment. The main reasons for choosing Drupal Commerce over Ubercart (other than the scaleability of working with the latest version of Drupal) were its out of the box support for multiple currencies and multiple languages - a mandatory requirement in our case. You can do that it Ubercart but why wrestle with an old timer  when you can wrestle with the new kid on the block! Before you get all sensitive - Ubercart is great!! and was originally conceived and developed by Ryan Szrama (..and hundreds of others over the years ..) the lead behind Commerce - so Ubercart was a great ancestor of the core implementation of Commerce (which in the early days was termed Ubercore!). And all of the lessons learned over the years of developing Ubercart provided a great opportunity to start again from scratch - how often do we get to do that!
One of the most distinctive features of Commerce when compared with Ubercart is its use of 'internal' entities for Products. These are displayed to the public using nodes of type 'product display' that allows multiple products to be attached to a single display.

e.g. You sell cups and jugs. Both come in various sizes and colours. In Ubercart you would create two products with size and colour attributes. In commerce you create one product for each variation product x size x colour. This models the real world more closely where in reality you do have individual cups of various sizes and colours.

If you didn't know that and want to find out more and what that means then watch this screen cast (http://vimeo.com/21681203)  and all will become clear - and see a demo here - http://demo.commerceguys.com/dc/

The advantage of this is that it models the real world where products are equivalent to SKUs (stock keeping units) and Product Displays provide a 'category' for products that group products of similar attributes (Cup) and allow you to select colour or size.

In Ubercart this was tricky as although you had product attributes it was hard to report on how many white cups had been purchased since the attributes didn't equal individual entities (skus) but were simply attributes of the product.
In any event, this is one of the most 'contentious' areas of Drupal Commerce and especially so for first timers and visitors from Ubercartland.
You will see/hear a lot of cries of  - 'Do I have to hand enter all 10000 of my products individually!!!' - 'In Ubercart I simply created x products with attributes for size, colour, etc!!!!'
Well the short answer to this is that yes you do have to - but you can import products and product displays and all of their links etc using the excellant Feeds module with its commerce plugin - 'commerce feeds'.
Randy Fay has some excellant screen casts covering all aspects of this on the Drupal Commerce web site - check them out - doubters will be converted!

In practical terms there are issues when it comes to importing large amounts of products and their related displays. It really depends on where you are getting your data from - e.g. your main stock keeping system, some spreadsheet or other database.

So in this post I am going to provide some supporting tips for those screen casts that show you how to create the files ready for import when you have thousands of products.

I will assume that you have watched Randy Fay's screen casts  as he tells you pretty much all you need to know - I am just adding some tips on how to prepare your source data for import.

Preparing your source data for import

To import products you will need to be able to create a delimited text file with the fields of the product.

So for example suppose we are going to sell cups and jugs of different sizes and colours
e.g. SKU, Title, Size, Colour, price
"001","Small White Cup","s","w","500"

and for the Product Display:
e.g. Title, description, sku_for_referenced_product
"Cup","A cup that comes in various colours and sizes", "001,002,003,004"

The trick here is to be able to create a delimited list of skus for the referenced product for each imported Product Display.
Easy enough if you have a few products and you can do that by hand - but what if you have 10,000 products linked to 100 displays and you need to keep them in sync with your stock control system on an AS400..
You can't do that by hand.... Don't panic!
Here are some simple but highly scaleable examples how to achieve this.

Source Data structure

Lets say for example that we have two tables in our external database:
ex1_products containing our Products (SKU, Description, Size, Colour, price,style)

CREATE TABLE `ex1_products` (
  `sku` varchar(5) NOT NULL,
  `Description` varchar(45) DEFAULT NULL,
  `Size` char(1) DEFAULT NULL,
  `Colour` char(1) DEFAULT NULL,
  `Price` int(11) DEFAULT NULL,
  `Style` char(1) DEFAULT NULL,
  PRIMARY KEY (`sku`)
)

ex1_product_display containing our product displays

CREATE TABLE `ex1_product_display` (
  `product_display_id` int(11) NOT NULL,
  `Title` varchar(45) DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `Style` char(1) DEFAULT NULL,
  PRIMARY KEY (`product_display_id`)
)

Obviously your data will be richer than this simple example - but you get the idea!

Now the real trick here to is to have something in your external system that enables you to link displays to products.
In our case we had a common field 'Style' - that enabled us to create a distinct list of product displays.
I.e. our original product list was like:

"001","Small White Cup","s","w","500","c"
"002","Medium White Cup","m","w","500","c"
"003","Large White Cup","l,"w","500","c"
"004","Small Black Cup","s","b","500","c"
"005","Medium Black Cup","m","b","500","c"
"006","Large Black Cup","l","b","500","c"
"007","Small White Jug","s","w","800","j"
"008","Medium White Jug","m","w","800","j"
"009","Large White Cup","l,"w","800","j"
"010","Small Black Cup","s","b","800","j"
"011","Medium Black Cup","m","b","800","j"
"012","Large Black Jug","l","b","800","j"

So we can create a list of product display from a distinct list of the styles - our Product displays:

'1', 'Cup', 'A cup that comes in various colours and sizes', 'c'
'2', 'Jug', 'A jug that comes in varoius colours and sizes', 'j'

Exporting the list of products is straight forward enough as they don't need to know about the link to the product displays.

To get a list of product displays with linked product references ready for import:

MYSQL
-------------------------

To get the full list:

SELECT pd.product_display_id as GUID,pd.Title,pd.Description, p.sku
FROM `bbtmp`.`ex1_product_display` pd
inner join bbtmp.ex1_products p on p.style = pd.style

Returns

1, Cup, A cup that comes in various colours and sizes, 001
1, Cup, A cup that comes in various colours and sizes, 002
1, Cup, A cup that comes in various colours and sizes, 003
1, Cup, A cup that comes in various colours and sizes, 004
1, Cup, A cup that comes in various colours and sizes, 005
1, Cup, A cup that comes in various colours and sizes, 006
2, Jug, A jug that comes in various colours and sizes, 007
2, Jug, A jug that comes in various colours and sizes, 008
2, Jug, A jug that comes in various colours and sizes, 009
2, Jug, A jug that comes in various colours and sizes, 010
2, Jug, A jug that comes in various colours and sizes, 011
2, Jug, A jug that comes in various colours and sizes, 012

But we want these collapsed into the (two) product displays with a delimited list of related products - ready for import.

Note that I prefer to use the pipe delimiter - but that isn't an option in the Feeds importer (comma is the default - but I have specified ',' in the query below to show you how to specifiy a delimiter - you could change it to ';') .

SELECT pd.product_display_id as GUID,pd.Title,pd.Description, 
group_concat(p.sku order by sku separator ",") as related_products 
FROM `bbtmp`.`ex1_product_display` pd 
inner join bbtmp.ex1_products p on p.style = pd.style group by product_display_id

returns:

'1', 'Cup', 'A cup that comes in various colours and sizes', '001,002,003,004,005,006'
'2', 'Jug', 'A great jug that comes in varoius colours and sizes', '007,008,009,010,011,012'

Update: Setting 'group_concat_max_len'

You may want to adjust your MYSQL settings to allow for long lists of SKUs in the group concat. By default group_concat has a limit of 1024 (when will see the back of 1024....). Fortunately you can up this either for your session.

SET SESSION group_concat_max_len = 8192

or permanently (best for development systems), by editing your MYSQL config file and adding:

# Set a larger limit for group concat
group_concat_max_len=1M

.. and of course restarting MYSQL.

Job done and scaleable to unlimited displays and  attached products! - Well, not really, as practically there will be a character limit on the concatenation somewhere along the line! - But good for hundreds if not thousands of products!

MS-SQL
--------------------------

What if you are using MSQL Server - Not so simple! (feeling brave - visit http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-value...)

However I have an old utility function that we can adapt for our specific purpose it's portable and does the job (..or export your data to MYSQL and don't look back!)

Create a user-defined function:

CREATE FUNCTION [dbo].[CommaSeparatedskus] ( @style AS char )
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @result VARCHAR(2000)
SELECT @result = COALESCE(@result + ', ', '') + sku
FROM         ex1_products
where style = @style
RETURN @result
END

Then call that with a query such as:

SELECT    Title, Description, Style, 
	  dbo.CommaSeparatedskus(Style) as related_products
FROM      ex1_product_display

So that about wraps it up.

In summary:

1. Check out the excellant screen casts by Randy Fay - http://www.drupalcommerce.org/node/467
2. Sort out your source data
3. Adapt the queries above for your use
4. Export the results to a text file and remember Windows users - convert your text file to UTF and use UNIX line breaks! _ Use Notepad++ for that.
5. Test, Test and test on a development copy not a live database!
6. Use Commerce for all your e-commerce projects from now on!
7. See also http://technology.blue-bag.com/case-drupal-commerce-detective