WooCommerce: CSV import of variable products and variations

22 May 2018

Good morning,

Today, a small tutorial about native and free mass import (or modification) of variable products and associated variations in a WooCommerce store, using a CSV file. Indeed, since version 3.1, the WooCommerce extension includes the native import/export of CSV file (without paying any plugin) but the process is not so trivial.

This article is intended for developers/webdesigners, so I do not explain every mechanism in details for it would be too long… I assume here that you know how the WooCommerce plugin’s products and variations work and that you have already explored a bit the native import/export procedure of the product base.

Important: This method allows you to “bulk” import, but also to modify products/variations when they already exist (just tick the appropriate checkbox on the import page). It allows you to automate changes in prices, categories, or any characteristic, on any number of products or variations.

Be careful however, with the modification mode, because it is very powerful and therefore very dangerous for your product database, just like SQL queries executed on a database… The slightest inadvertence (typing mistake, copy/paste error, ID error, parent error, etc.) can be fatal, since any line in the CSV file can modify existing products or variations as well as their dependency relationships, or even delete them altogether if you are not focused and rigorous!

Now, knowing that, you can forget about all these useless paid extensions – at least for this kind of simple management.

Project specifications

I tried to make it as simple and generic as possible so as not to weigh down the article and so that everyone could imagine their own story…

So, I just wanted to import hundreds of products of the same type with categories and labels, each with variations defined by a global attribute, with the ability to select the variations on the product page with a drop-down menu displaying the possible choices of the global attribute.

Note: Products do not necessarily have the same variations, but they are all defined by the same global attribute (in other words, they don’t have to share the same set of attribute values).

Example: Selling photos (the product) with different sizes (variations) and associated prices, selectable on the product page.

The CSV import file format

Here are the three template lines allowing you to import (or modify) the whole product database at once:

Type,SKU,Name,Published,"Visibility in catalog","In stock?",Categories,Tags,Images,"Attribute 1 name","Attribute 1 value(s)","Attribute 1 default","Attribute 1 visible","Attribute 1 global",Parent,"Width (cm)","Height (cm)","Regular price"

variable,REF_PRODUCT,"Product",1,visible,1,"Category 1","Tag 1, Tag 2",product-image.jpg,Attribute,"Attr Val 1, Attr Val 2, Attr Val 3","Attr Val 2",0,1,,,,

variation,REF_PROD_VAR,"Variation name",1,visible,1,,,,Attribute,"Attr Val N",,,,REF_PRODUCT,30,20,100

The first line is the file header; it describes the columns, and it’s unique.
You will have to define here your attribute(s) (name, values, default value, visibility, global or not).

The second line defines a product, and the third one a variation.
The CSV file will contain any number of these last two lines, depending on your project. You can check the WooCommerce CSV import schema if you want more details about the import attributes.

Practical note: To finally find out this minimal set of columns (for my project), I followed a tip I found many times on forums: I set up products and variations by hand, with the associated attributes, categories and labels, then I used the export function to get a basic CSV file, which I then cleaned by deleting the useless elements (whose default value was OK for me).

Syntax elements:

  • separator = comma, no space before or after (otherwise import does not recognize columns!)
  • strings containing spaces must be delimited by double quotes
  • comma-separated list items (for example, labels or attribute values)
  • some attributes are not recognized because of capital letters (error in documentation or in software?), for example “Regular Price” is WRONG, and the correct label is “Regular price”

Defining products, variations and attributes combinations

The names and labels speak for themselves but here is the principle of product/variation creation:

  • the product (variable type) must define its characteristics (categories, labels, image) and an attribute “Attribute” (global or not but here, it is) and its possible values “Attr Val #”
  • the variations must reference the parent product and must be associated to a value of the attribute, then define their own characteristics (sizes, prices)
  • the default value of the attribute is used to directly display a variation when loading the product page

Several attributes could be used (and thus, their combinations) to define the variations; then 5 new columns (“Attribute # Name”, “Attribute # value(s)”, etc.) should be added for each new attribute to define them in the products and reference them in the variations. However, the number of variation lines can increase very quickly to cover the possible combinations defined by all the attributes!

Notes regarding CSV import compared to manual mode (insertion via WordPress back-end):

  • there’s no need to define the global attribute or categories/tags beforehand, they will be created by the import process
  • there’sno need to specify that variations use the attribute for it is automatic

Conclusion

I know you won’t have the exact same requirements, but I hope you understood that the principle will be the same for any shop configuration… You can now automatically manage your online store’s product database from a CSV file (stored locally and updated with a text editor or a spreadsheet): the first import will create the product base, then the next imports will update it.

Of course, if the variations are defined by many attributes with many possible values, there will be a lot of possible combinations, and if in addition the variations are not the same for each product, the CSV file will become complicated to generate and maintain… (Who said “awk”?)

Good luck with your e-shops!