Difference between revisions of "Bulk Inventory Export and Import"

From Spiffy Stores Knowledge Base

m
 
(5 intermediate revisions by the same user not shown)
Line 28: Line 28:
  
 
Select the '''Import more products''' link to go to the '''Product Import''' page.  
 
Select the '''Import more products''' link to go to the '''Product Import''' page.  
 +
 +
The same process is used for the Product CSV files as well as the Inventory CSV files. The Product Import can determine the type of file being imported automatically.
  
 
=== Product Import Page  ===
 
=== Product Import Page  ===
 
 
<gallery widths="200px" heights="200px" class="right">
 
<gallery widths="200px" heights="200px" class="right">
File:Product import 2.png|Product Import page
+
File:Inventory Product Import.png|Product Import page
 
</gallery>  
 
</gallery>  
  
 
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.  
 
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.  
+
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. This is only used for full Product imports, and if you want to use an Inventory import CSV file, you should do an export of the Inventory first.
  
 
<gallery widths="200px" heights="200px" class="right">
 
<gallery widths="200px" heights="200px" class="right">
Line 52: Line 53:
 
<pre>Handle
 
<pre>Handle
 
Title
 
Title
Body
 
Body (HTML)
 
Vendor
 
Product Category
 
Type
 
Tags
 
Shipping Code
 
SEO Title
 
SEO Description
 
 
Option1 Name
 
Option1 Name
 
Option1 Value
 
Option1 Value
Line 67: Line 59:
 
Option3 Name
 
Option3 Name
 
Option3 Value
 
Option3 Value
Variant SKU
+
SKU
Variant Location Code
+
HS Code
Variant Barcode
+
COO
Variant Grams
+
Location
Variant Inventory Tracker
+
Incoming
Variant Inventory Qty
+
Unavailable
Variant Inventory Policy
+
Committed
Variant Fulfillment Service
+
Available
Variant Price
+
On hand
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
 
Collection
 
 
</pre>  
 
</pre>  
 
Note: The ''Collection'' field is optional.
 
  
 
The fields are explained below.
 
The fields are explained below.
  
=== The products.csv file explained ===
+
=== The inventory.csv file explained ===
  
 
{| width="100%" border="0" cellpadding="20" class="wikitable orders_explanation"
 
{| width="100%" border="0" cellpadding="20" class="wikitable orders_explanation"
Line 120: Line 91:
 
| '''Title''' (Required)
 
| '''Title''' (Required)
 
| The product's title.
 
| The product's title.
|-
 
| '''Body'''
 
| The product's description. This field may contain Textile markup codes. It may also include UTF-8 characters. If you use this column to populate the product decription, make sure you leave the column "Body (HTML)" blank.
 
|-
 
| '''Body (HTML)'''
 
| The product's description using HTML tags. It may also include UTF-8 characters. This column may be used to populate the product description using HTML tags, but if you want to use this column, make sure that the "Body" column is blank.
 
|-
 
| '''Vendor''' (Required)
 
| The name of the product's vendor.
 
|-
 
| '''Product Category'''
 
| A label that describes the type of product and is used to help categorize products in search engines and other sales platforms. This label must be taken from the predefined standardized list of product categories.<br><br>You can input the standardized product category in either as the full path from the Standardized Product Taxonomy, or use the numerical ID for the category.
 
|-
 
| '''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)
 
| '''Option1 Name''' (Required)
Line 166: Line 110:
 
| 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
 
| 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'''
+
| '''SKU'''
 
| This is the SKU (Stock Keeping Unit) identifier used to identify the variation.
 
| This is the SKU (Stock Keeping Unit) identifier used to identify the variation.
 
|-
 
|-
| '''Variant Location Code'''
+
| '''HS Code'''
| This is an optional value that you can use to specify a physical location for the variation. For example, you could use a shelf location, box number or some other identifier to indicate where you would find the item in your inventory. If the location code is available, then the packing list and order view will be sorted in location code order so that you will have a pick list sorted to make order fulfilment easier.
+
| The HS Code (Harmonized System Code) column is used to estimate duties and taxes for international customers. This column is optional. If you include it, then the values can be left blank.  
|-
 
| '''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 fulfillment service is being used. Valid values are "manual", "default", "eparcel", "store-line", "shipwire", "webgistix" and "amazon". If you don't use a fulfillment service, set the value to "manual". If you have a default fulfillment service set in your store, set the value to "default".
 
|-
 
| '''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 [[Information about your customers#Enabling Wholesale Prices on a Customer Account|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)
+
| '''COO'''
| 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.
+
| The COO (Country/Region of Origin) column is used to estimate duties and taxes for international customers. Accepted values are ISO 3166-1 alpha-2 country codes. This column is optional. If you include it, then the values can be left blank.
 
|-
 
|-
| '''Variant Ship Separately''' (Required)
+
| '''Location'''
| 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.<br><br>If this is set to true, then you must supply values for Variant Length, Width and Height.
+
| The names of each of your locations. The location row is case sensitive and must be the name of one of your locations. The inventory for each location is displayed in the corresponding location row. For each variant, include a row for every location where you want to update inventory. Remove any other location rows. Use 'Default' if multiple locations are not enabled for your store.
 
|-
 
|-
| '''Variant Length'''
+
| '''Incoming'''
| 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.
+
| Inventory that's on its way to your location. Incoming inventory isn't available to sell until it's been received.
 
|-
 
|-
| '''Variant Width'''
+
| '''Unavailable'''
| 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.
+
| Unavailable inventory refers to inventory that isn't available for sale or committed to an order. It's not available to be sold.
 
|-
 
|-
| '''Variant Height'''
+
| '''Committed'''
| 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.
+
| The number of units that are part of a placed order but aren't fulfilled.
 
|-
 
|-
| '''Variant Shipping Code'''
+
| '''Available'''
| 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.
+
| Inventory that you can sell. Available inventory isn't committed to any orders or reserved for any draft orders.
 
|-
 
|-
| '''Variant Image'''
+
| '''On hand'''
| 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.
+
| The number of units that you have at a location. On hand inventory is made up of the total of your Committed, Unavailable and Available inventory. The inventory values that you input can be positive, negative or 0. The number cannot include a decimal, such as 1.5. You can also enter '''not stocked''' to indicate that the product is never stocked at the location.
|-
 
| '''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.
 
|-
 
| '''Collection'''
 
| You can optionally add a collection title to be associated with the product. This field can only occur on the first line of a product definition. If the collection exists, the product will be added to the collection. Otherwise, the collection will be created, before the product is added. Only new products will be added to the collection, so if you are updating an existing product, no changes will be made to the collections. Also, only Standard Collections can be used.
 
 
|}
 
|}
 
== 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 0ption 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  ====
 
 
<gallery widths="200px" heights="200px" class="right">
 
File:Product import 3.png|Handle Section in CSV File
 
</gallery>
 
 
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 ====
 
 
<gallery widths="200px" heights="200px" class="right">
 
File:Product import 4.png|Product Section in CSV File
 
</gallery>
 
 
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 ====
 
 
<gallery widths="200px" heights="200px" class="right">
 
File:Product import 5.png|Variations Section in CSV File
 
</gallery>
 
 
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 ====
 
 
<gallery widths="200px" heights="200px" class="right">
 
File:Product import 6.png|Custom Options Section in CSV File
 
</gallery>
 
 
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 ====
 
 
<gallery widths="200px" heights="200px" class="right">
 
File:Product import 7.png|Images Section in CSV File
 
</gallery>
 
 
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.
 

Latest revision as of 12:04, 10 October 2024

You are able to Export and Import your store inventory using CSV (Comma-Separated Values) files. CSV files can be created and edited with products such as Excel and Open Office.

This feature is similar to the more generic Bulk Product Export and Import feature, but is specifically designed to make it much easier to update just the inventory levels for your products. Just download the inventory for your current products as a CSV, and use that file as a template in order to update the inventory levels for your products. Once updated, the CSV file can be imported, as described below.

You cannot use the Inventory CSV file to import new products. You must use the full product CSV as defined in the Bulk Product Export and Import section.

You can also import a CSV file of your inventory downloaded from Shopify. If you're having problems importing inventory levels from Shopify, please send us your CSV file at support@spiffystores.com.au.

Exporting Inventory

Select the Export all Inventory link on the Products page to download a CSV file containing just the Inventory levels of the products in your online store.

This link is also available on the Inventory page.

You can also choose the Export all Inventory in current group to download a CSV file of the inventory levels for a selected group of products.

This file can be edited and used to update your product inventory levels in bulk.

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

Importing Inventory

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

The same process is used for the Product CSV files as well as the Inventory CSV files. The Product Import can determine the type of file being imported automatically.

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. This is only used for full Product imports, and if you want to use an Inventory import CSV file, you should do an export of the Inventory first.

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
Option1 Name
Option1 Value
Option2 Name
Option2 Value
Option3 Name
Option3 Value
SKU
HS Code
COO
Location
Incoming
Unavailable
Committed
Available
On hand

The fields are explained below.

The inventory.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.
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
SKU This is the SKU (Stock Keeping Unit) identifier used to identify the variation.
HS Code The HS Code (Harmonized System Code) column is used to estimate duties and taxes for international customers. This column is optional. If you include it, then the values can be left blank.
COO The COO (Country/Region of Origin) column is used to estimate duties and taxes for international customers. Accepted values are ISO 3166-1 alpha-2 country codes. This column is optional. If you include it, then the values can be left blank.
Location The names of each of your locations. The location row is case sensitive and must be the name of one of your locations. The inventory for each location is displayed in the corresponding location row. For each variant, include a row for every location where you want to update inventory. Remove any other location rows. Use 'Default' if multiple locations are not enabled for your store.
Incoming Inventory that's on its way to your location. Incoming inventory isn't available to sell until it's been received.
Unavailable Unavailable inventory refers to inventory that isn't available for sale or committed to an order. It's not available to be sold.
Committed The number of units that are part of a placed order but aren't fulfilled.
Available Inventory that you can sell. Available inventory isn't committed to any orders or reserved for any draft orders.
On hand The number of units that you have at a location. On hand inventory is made up of the total of your Committed, Unavailable and Available inventory. The inventory values that you input can be positive, negative or 0. The number cannot include a decimal, such as 1.5. You can also enter not stocked to indicate that the product is never stocked at the location.