Bulk Product Export and Import

From Spiffy Stores Knowledge Base

Jump to: navigation, search

You are able to Export and Import your store products using CSV (Comma-Separated Values) files. This feature gives you the ability to perform a bulk import of new products into your store, or to update product attributes such as prices and inventory levels. The update capability is useful if you need to keep your store prices synchronized with an inventory management system, or if you have a large number of products and need an easier method for maintaining them.

Contents

Exporting Products

Select the Export all products link on the Products page to download a CSV file containing all of the products in your online store. This file can be used as a backup for your product data, or as a template which you can modify to create an update CSV file to modify your product data.

Please refer to the following section on Importing Products for details on the format of the CSV file.

Importing Products

Select the Import more products link to go to the Product Import page.

Product Import Page

The Product Import page contains some instructions on how to use the product import process. In the side-bar are the answers to a number of commonly asked questions about importing products.

If you want a template CSV file to use to start building your product update CSV file, then click on the Download Template CSV button.

Your CSV file needs to adhere to the following conventions in order for it to be used to import products.

You can freely use UTF-8 character encodings for product titles and product descriptions, but be sure to create them using a Unicode or UTF-8 enabled program. The OpenOffice.org suite of programs is suitable for this purpose.

A CSV file (Comma-Separated Values) file is a plain text file with commas delimiting the fields. The first line of the CSV file is the header, and contains the name of the fields. The sample template CSV file can assist you with the correct heading values.

The first line of the CSV file must have the following heading columns.

Handle
Title
Body
Vendor
Type
Tags
Shipping Code
SEO Title
SEO Description
Option1 Name
Option1 Value
Option2 Name
Option2 Value
Option3 Name
Option3 Value
Variant SKU
Variant Barcode
Variant Grams
Variant Inventory Tracker
Variant Inventory Qty
Variant Inventory Policy
Variant Fulfillment Service
Variant Price
Variant Cost Price
Variant Compare At Price
Variant Discount
Variant Instalments
Variant Free Shipping
Variant Tax Free
Variant No Shipping Required
Variant Ship Separately
Variant Length
Variant Width
Variant Height
Variant Shipping Code
Variant Image
Custom Option Name
Custom Option Value
Custom Option Price
Image Src
Image Alt Text

The fields are explained below.

The products.csv file explained

HEADER WHAT IS IT?
Handle (Required when updating products. Not required when adding new products) The product handle is a unique identifier for a given product. The handle is used as part of the product's URL to identify that product. In the CSV file, each line must contain a Handle or Title.

If the Handle is not provided, one is automatically generated from the Title.

This option is usually used when importing new products. For referencing existing products, it is necessary to specify the Handle of the product. A product definition may take a number of records in the CSV file, and each of these lines will contain the product's Handle. When a different Handle is encountered, a new product is being defined.

The handle consists of lower-case letters and digits separated by dashes ('-'). No special characters or spaces are permitted. The handle must be unique for each product. The handle must start with an alphabetic character.

In the following example, the handle is "a-green-t-shirt".

http://www.myshop.com.au/products/a-green-t-shirt
Title (Required) The product's title.
Body The product's description. This field may contain Textile markup codes. It may also include UTF-8 characters.
Vendor (Required) The name of the product's vendor.
Type (Required) The product type. e.g. shoes, necklaces, dress, jeans etc.
Tags This is a comma-separated list of keyword tags used to tag the product. Tags help the software to show products related to your products. They also display in the keywords meta tag when viewing a product page.
Shipping Code An optional shipping code may be specified which indicates that all of this product's variations will use the shipping code to determine the shipping rate for the product.
SEO Title You can override the default title of the product's page using this field. You may wish to do this if you need to alter it for Search Engine Optimization purposes. By default, the page title is the same as the title of the product.
SEO Description By default, a standard set of tagged values for the product will be generated and used to populate the meta description tag for the product page. You can override this and provide your own custom description for Search Engine Optimization purposes.
Option1 Name (Required) This is the name of the default option that is used to distinguish a product's variations. For products with only single option, this should be left as 'Title'. A value MUST be provided if a variation is being defined. e.g. Title, Size, Colour, Name, Material, Style.
Option1 Value (Required) This is the value used to distinguish between a product variations. A value MUST be provided if a variation is being defined. If you have only one option, this should be set as 'Default'. e.g. Small, Medium, Large, Blue, Green, Yellow
Option2 Name If a product has a second option used to distinguish a product's variations, then enter the name of the option here.
Option2 Value If a product has a second option, enter the value of the option for this variation here. A value MUST be provided if a variation is being defined. e.g. Small, Medium, Large, Blue, Green, Yellow
Option3 Name If a product has a third option used to distinguish a product's variations, enter the name of the option here.
Option3 Value If a product has a third option, enter the value of the option for this variation here. A value MUST be provided if a variation is being defined. e.g. Small, Medium, Large, Blue, Green, Yellow
Variant SKU This is the SKU (Stock Keeping Unit) identifier used to identify the variation.
Variant Barcode This is the UPC/EAN or ISBN number for the variation. It is not required, but may be necessary in order to use some product data feeds, such as Google Merchant Center.
Variant Grams (Required) This is the weight of the product variation. If your store uses metric weights, then this is the weight in grams. Alternatively if your store uses imperial weights, this is the weight in 1000ths of a pound.
Variant Inventory Tracker This specifies how the tracking of inventory is managed. Valid values are "spiffy" or blank. If set to "spiffy", you are enabling stock tracking on the product and should ensure a number is entered for "Variant Inventory Qty".
Variant Inventory Qty This is the number if items in stock for this variation. If inventory tracker is set to "spiffy", this is required. If inventory tracker is blank, then this should also be blank.
Variant Inventory Policy This specifies how to handle orders when inventory level for this variation reaches zero. Valid values are "deny", "continue" and "archive". If inventory tracker is set to "spiffy", this is required. If inventory tracker is blank, then this should also be blank.
Variant Fulfillment Service (Required) This determines how the product is fulfilled and if a fulfilment service is being used. Valid values are "manual", "store-line", "shipwire", "webgistix" and "amazon". If you don't use a fulfillment service, set the value to "manual".
Variant Price (Required) This is the price of the product variation.
Variant Cost Price This is the Wholesale Price of the product variation. Customers who have Wholesale prices enabled are shown the Wholesale Price of a product after they have logged in.
Variant Compare at Price This is the compare at price for the product variation. It is used to provide a comparison price to show the amount of discount that is being offered. In some themes, a "Variant Compare at Price" that is lower than the "Variant Price" will result in the product being flagged as "On Sale".
Variant Discount This is a discount price for a variation that can be used in conjunction with Shopping Cart discounts.
Variant Instalments Payment for a product may be offered in instalments. This field specifies the number of payments required for the variation. Note, it is the store owner's responsibility to bill the customer for all instalment payments after the first.
Variant Free Shipping (Required) This is a true or false value indicating if the variation has free shipping.
Variant Tax Free (Required) This is a true or false value indicating if the variation is tax free.
Variant No Shipping Required (Required) This is a true or false value indicating if the variation does not need shipping. This setting is used to support non-physical goods such as digital goods, services and gift certificates.
Variant Ship Separately (Required) This is a true or false value indicating if the variation needs to be shipped separately, and is used in conjunction with the shipping rate calculators to return an accurate shipping cost for the customer. It would normally be used for bulky goods where the physical dimensions of the product are used to calculate shipping rates based upon volume instead of weight.

If this is set to true, then you must supply values for Variant Length, Width and Height.
Variant Length This value specifies the length of the package in centimetres or inches according to the Unit system you select in the "Standards and Formats" section of your store's preferences. This value is required if you have specified that the variation requires separate shipping.
Variant Width This value specifies the width of the package in centimetres or inches according to the Unit system you select in the "Standards and Formats" section of your store's preferences. This value is required if you have specified that the variation requires separate shipping.
Variant Height This value specifies the height of the package in centimetres or inches according to the Unit system you select in the "Standards and Formats" section of your store's preferences. This value is required if you have specified that the variation requires separate shipping.
Variant Shipping Code An optional shipping code may be specified which indicates that this variation will use the shipping code to determine the shipping rate. This shipping code will override any shipping code specified for the product.
Variant Image An image may be optionally associated with a particular variant. Specify the image URL here if needed. The URL must point to one of the product's images, as specified by the Image Src field.
Custom Option Name This field defines the name of a Custom Option.
Custom Option Value This field add a value for a Custom Option.
Custom Option Price This field specifies the additional price for a Custom Option Value.
Image Src Since images cannot be placed directly into a CSV file, you can specify the URL of an image to be attached to the product definition. Spiffy Stores will download the image during the import from the server specified by the URL. Images must be hosted on a publicly accessible web site, and cannot be uploaded directly from your computer by the CSV import method.

You may not import images from your own Spiffy Store. Any such images will be ignored by the import process. If you're uploading a CSV to update products that already exist in your store and you're not changing your product images, you should leave this field blank.

Image Alt Text You can add an optional text description to your image which is then used in the ALT tag for the image. The ALT tag is used to display a description of the image, if the image itself is not available for display. The text description can also be used by search engines to associate searchable text with each image.
CSV File Usage Notes

The first record for a given handle must contain the product data section (Title, Body, Vendor, Type and Tags). Title, Vendor and Type must be specified.

Optionally this record and subsequent records with the same handle can contain Variant, Image and Custom Option records.

As many secondary handle records are included as necessary to list all possible variant, image or option records.

If the product is new, the first record must contain at least the product data, a variant record and optionally an image or option record. This and subsequent records will define the product and all its variants, images and options.

If the product is being updated, then the product data in the first record will replace the existing product data. The variant, image and option data are optional on the first record. If a variant, image or option is specified on the first record, then ALL existing variants, images or options are deleted and replaced by the data from the CSV file.

If there is no variant, image or option on the first record, but data appears on subsequent records, then these records are used to update or add new records. Thus, if there is no variant on the first record, but one appears on the second record, then this data replaces any existing variant with the same title or adds a new variant if it doesn't already exist.

The same rules apply to options, and also to images. However, for images all images are treated as new images and an attempt is made to upload them to the product.

If a product exists and the overwrite option is not specified, then the product is skipped entirely.

NOTE: If the first record does not contain product data, it will be skipped.

NOTE: Only the product data on the first record for a handle will be used. It will be ignored on all other records.

Policy for updates -

  • If a product is new, then obviously all the data in the CSV will be added to the product.
  • If a product already exists and the overwrite flag is not set, then the product will be skipped and no changes will be made to the product, its variants, options or images.
  • If a product exists and overwrite is specified, then the main product attributes will be replaced. Also, all variants, options and images will be deleted and replaced by the ones specified in the CSV. This is equivalent to the product being deleted and replaced with a new version.

With the addition of the Option1, Option2 and Option3 fields, the variant title is replaced by up to three of these pption names (and values).

The Option Names are the titles of each of the options and are specified by "Option1 Name", "Option2 Name" and "Option3 Name". Only the values on the first record will be used when creating a product. All the names on subsequent lines are ignored, and are assumed to be the same as the first record. An error will be issued if the names change. When a product is being updated, then an error will be issued if the names do not exactly match the existing option names.

If you are using your CSV file to update variant data such as prices and inventory levels, then we'd recommend deleting the Image Src, Custom Option Name, Custom Option Value and Custom Option Price fields as these won't be changing your product images or custom options and it's not necessary to update them every time you make minor changes to the product and variation settings.

CSV File Examples
Identification Section

The CSV file is divided into five main sections.

The first section consists of the first two columns, the Handle and the Title fields.

These two fields identify each product, and either a Handle or Title MUST be specified for each product. The values are repeated for every line of the product definition.

Product Section

The next section of the file contains the product description.

The four fields in this section include the Body, Vendor, Type and Tags fields.

This data is only required only on the FIRST record in a product definition. If any of these fields are specified on subsequent records for the product, they will be ignored.

Variations Section

The next 16 fields define each variation.

Each product variation requires one record in the CSV file, so include as many entries as necessary. Remember to include the Handle and/or Title fields for the product at the start of each record.

Custom Options Section

The following 3 fields define all the custom options and their values.

An entry is required for each custom option name and each of the values and prices for that name. Each combination requires one product record, and you need to include as many records as necessary to define all custom option combinations.

It doesn't matter if you have more or less Custom Options than Variations. In the example on the right, there are 3 variations, but 4 custom options, so we need a total of 4 product records for the first product.

Images Section

The final field defines the images for the product.

Each entry is the URL of the image on a web server. Each product image is listed on a single record. You need to include as many records as you have product images to define.

It doesn't matter if you have more or less Images than Custom Options or Variations. In the example on the right, there are 3 variations, 4 custom options, but only 1 image, so we need a total of 4 product records for the first product.