How does the importing products work?
Our import feature allows you to bulk add or update your products by uploading a Microsoft Excel spreadsheet that contains a single sheet of product data structured into specific columns.
How do I bulk add products?
Go to Commerce > Products > Products.
Click the Import button in the top right.
Tick Adding new products under Which type of import you are doing?
A help box will appear telling you how to structure your spreadsheet columns correctly, see How do I structure my spreadsheet columns?
Once you've structured your spreadsheet, add the product data, see What data do I provide when adding products?
Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.
Click the Import button.
If there have been any issues with your data, an error message will be displayed with the row number and issue. Make the relevant changes to your spreadsheet then repeat the process above from step 6.
The product import uploads and processes product images after the products have been added, go to Commerce > Products > Products to see how many are still being uploaded and processed. The images will continue to be uploaded and processed with no additional work on your part.
How are sub products added from a flat spreadsheet?
Each sub product should have it's own row with the core product data repeated multiple times for each sub product.
We group them together using the product TITLE (or NAME if provided) when adding products. So you need to ensure the TITLE/NAME is the same for each sub product.
How do I bulk update products?
Go to Commerce > Products > Products.
Click the Import button in the top right.
Tick Updating existing products under Which type of import you are doing?
A help box will appear telling you how to structure your spreadsheet columns, see How do I structure my spreadsheet columns?
Once you've structured your spreadsheet columns, add the product data, see What data do I provide when updating products?
Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.
Click the Import button.
If there have been any issues with your data, an error message will be displayed with the row number and issue. Make the relevant changes to your spreadsheet then repeat the process above from step 6.
How do I structure my spreadsheet columns?
The spreadsheet must be of the .xlsx file type and only contain one sheet, any additional sheets will be ignored.
The first row of the spreadsheet must list the column headings in separate columns, see What are the columns for adding products? and What are the columns for updating products? for the supported headings. All column headings should be in uppercase.
An easy option for structuring a spreadsheet is to first export your products, this will download a spreadsheet that is the correct file type (.xlsx) and already has most of the columns that are used for adding or updating. You can then use the downloaded spreadsheet as a template.
If your spreadsheet contains any columns that are not supported then those columns and their data will be ignored but if you wish to remove any columns, simply delete them from the spreadsheet.
You can add additional columns to the spreadsheet by adding a new column and entering a supported column heading into the first row of the new column. If you wish to remove any columns, simply delete them from the spreadsheet.
What are the columns for adding products?
When adding products, the TITLE column is required, all other columns are optional. The supported columns are as follows:
TITLE
NAME
TYPE_NAME
SUMMARY
DESCRIPTION
SKU
BARCODE
STOCK_CONTROL
HIDE_IF_NO_STOCK
NOTIFY_IF_LOW_STOCK
NOTIFY_IF_NO_STOCK
ALLOW_ORDER_IF_NO_STOCK
TAX_TYPE
PUBLISH_DATE
EXPIRY_DATE
STOCK
LOW_STOCK_THRESHOLD
XERO_INVENTORY_ITEM_CODE
XERO_SALES_ACCOUNT_CODE
XERO_TRACKING_CATEGORY_NAME
XERO_TRACKING_CATEGORY_OPTION_NAME
WEIGHT
WIDTH
HEIGHT
DEPTH
HS_CODE
COUNTRY_OF_ORIGIN_TITLE
CUSTOMS_DESCRIPTION
WEBSITE_STATUS
POS_CHANNEL
WEBSITE_CHANNEL
WEIGHTING
BADGE_LABEL
KEYWORDS
MISSING_PRICE_TEXT
YOUTUBE_VIDEO_ID
VIMEO_VIDEO_ID
INTANGIBLE
CATEGORY_NAMES
TAG_NAMES
HIDE_FROM_SEARCH_ENGINES
HIDE_FROM_SITE_SEARCH
HIDE_FROM_LISTINGS
SUPPLIER_FRIENDLY_ID
SUPPLIER_CODE
GOOGLE_PRODUCT_CATEGORY_ID
PAGE_TITLE
PAGE_DESCRIPTION
CUSTOM_FIELD_[NAME]*
PRICE_[CURRENCY_CODE]_[PRICING_TIER_NAME]**
VARIATION_[NUMBER]_TITLE***
VARIATION_[NUMBER]_VALUE***
IMAGE_[NUMBER]_URL****
SUB_PRODUCT_IMAGE_NUMBER
*The NAME component of the the CUSTOM_FIELD heading should be an existing product type custom field name (e.g. CUSTOM_FIELD_ISBN).
**The CURRENCY_CODE component of the PRICE_ heading should be an existing currency code (USD, NZD, etc) and the PRICING_TIER_NAME component should be an existing pricing tier name (e.g. CURRENCY_USD_RETAIL).
***The NUMBER component of the VARIATION_..._TITLE and the VARIATION_..._VALUE headings should start at 1 and increase with each variation, we support up to 5 different variations (e.g. VARIATION_1_TITLE and VARIATION_1_VALUE).
****The NUMBER component of the IMAGE_..._URL should start at 1 and increase with each image URL, we support up to 50 different image URLs (e.g. IMAGE_1_URL).
What data do I provide when adding products?
When adding products using the import feature, it is important that the product data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:
TITLE - must be provided and cannot exceed 200 characters.
NAME - must be unique, only contain letters, numbers and hyphens and cannot exceed 200 characters. Note, if you don't include NAME, it will be created automatically for you using the TITLE field.
TYPE_NAME - must reference the name of an existing product type.
SUMMARY - cannot exceed 600 characters.
DESCRIPTION - can only contain valid HTML tags.
SKU - cannot exceed 50 characters.
BARCODE - cannot exceed 20 characters.
STOCK_CONTROL - must be either yes or no.
HIDE_IF_NO_STOCK - must be either yes or no.
NOTIFY_IF_LOW_STOCK - must be either yes or no.
NOTIFY_IF_NO_STOCK - must be either yes or no.
ALLOW_ORDER_IF_NO_STOCK - must be either yes or no.
TAX_TYPE - must be no tax, tax exempt, zero-rated, standard rate or reduced rate.
PUBLISH_DATE - must use the following format: dd mmm yyyy hh:mm (e.g. 23 May 2022 15:38).
EXPIRY_DATE - must use the following format: dd mmm yyyy hh:mm (e.g. 23 May 2022 15:38) and fall after the PUBLISH_DATE.
STOCK - must be a positive number i.e. zero or greater.
LOW_STOCK_THRESHOLD - must be a positive number i.e. zero or greater.
XERO_INVENTORY_ITEM_CODE - Xero must be connected and must reference an existing Xero item code.
XERO_SALES_ACCOUNT_CODE - Xero must be connected and must reference an existing Xero account code.
XERO_TRACKING_CATEGORY_NAME - Xero must be connected and must reference an existing Xero tracking category.
XERO_TRACKING_CATEGORY_OPTION_NAME - Xero must be connected and must reference an existing Xero tracking category option.
WEIGHT - must be a positive number i.e. zero or greater.
WIDTH - must be a positive number i.e. zero or greater.
HEIGHT - must be a positive number i.e. zero or greater.
DEPTH - must be a positive number i.e. zero or greater.
HS_CODE - cannot exceed 20 characters.
COUNTRY_OF_ORIGIN_TITLE - must reference an existing country.
CUSTOMS_DESCRIPTION - cannot exceed 100 characters.
WEBSITE_STATUS - must be either published or unpublished.
POS_CHANNEL - must be either yes or no.
WEBSITE_CHANNEL - must be either yes or no.
WEIGHTING - must be a positive number i.e. zero or greater.
BADGE_LABEL - must reference an existing product badge label.
KEYWORDS - cannot exceed 1000 characters.
MISSING_PRICE_TEXT - cannot exceed 50 characters.
YOUTUBE_VIDEO_ID - cannot exceed 50 characters, if the video ID is unavailable then a valid YouTube video URL can be provided instead.
VIMEO_VIDEO_ID - cannot exceed 30 characters, if the video ID is unavailable then a valid Vimeo video URL can be provided instead.
INTANGIBLE - must be either yes or no.
CATEGORY_NAMES - must reference existing product category names, multiple names must be separated by commas.
TAG_NAMES - must reference existing product tag names, multiple names must be separated by commas.
HIDE_FROM_SEARCH_ENGINES - must be either yes or no.
HIDE_FROM_SITE_SEARCH - must be either yes or no.
HIDE_FROM_LISTINGS - must be either yes or no.
SUPPLIER_FRIENDLY_ID - must reference an existing supplier.
SUPPLIER_CODE - cannot exceed 50 characters.
GOOGLE_PRODUCT_CATEGORY_ID - must reference an existing Google product category.
PAGE_TITLE - cannot exceed 100 characters.
PAGE_DESCRIPTION - cannot exceed 200 characters.
CUSTOM_FIELD_[NAME] - a custom field can only be added if the product uses the correct product type. If the custom field uses the pre defined value type, then the option name must reference an existing option for that custom field.
PRICE_[CURRENCY_CODE]_[PRICING_TIER_NAME] - must be a positive number i.e. zero or greater
VARIATION_[NUMBER]_TITLE - a relevant product variation name (e.g. colour, size, flavour, etc).
VARIATION_[NUMBER]_VALUE - a relevant product attribute that relates to the corresponding VARIATION_TITLE (e.g. if the variation was colour, attributes could be blue, green, red, etc).
IMAGE_[NUMBER]_URL - must be a valid publicly accessible URL, A good way to test is to see if you can load the URL in your browser.
SUB_PRODUCT_IMAGE_NUMBER - must reference the number of a image URL that you wish to use for a sub product (e.g. if the sub product image you wished to use was being imported as IMAGE_5_URL then SUB_PRODUCT_IMAGE_NUMBER would be 5)
What are the columns for updating products?
When updating products, the PRODUCT_ID and the SUB_PRODUCT_ID are required, all other columns are optional. The supported columns are as follows:
PRODUCT_ID
SUB_PRODUCT_ID
TITLE
NAME
DESCRIPTION
SUMMARY
SKU
BARCODE
STOCK_CONTROL
HIDE_IF_NO_STOCK
NOTIFY_IF_LOW_STOCK
NOTIFY_IF_NO_STOCK
ALLOW_ORDER_IF_NO_STOCK
TAX_TYPE
PUBLISH_DATE
EXPIRY_DATE
STOCK
LOW_STOCK_THRESHOLD
XERO_INVENTORY_ITEM_CODE
XERO_SALES_ACCOUNT_CODE
XERO_TRACKING_CATEGORY_NAME
XERO_TRACKING_CATEGORY_OPTION_NAME
WEIGHT
WIDTH
HEIGHT
DEPTH
HS_CODE
COUNTRY_OF_ORIGIN_TITLE
CUSTOMS_DESCRIPTION
WEBSITE_STATUS
POS_CHANNEL
WEBSITE_CHANNEL
WEIGHTING
BADGE_LABEL
KEYWORDS
MISSING_PRICE_TEXT
YOUTUBE_VIDEO_ID
VIMEO_VIDEO_ID
INTANGIBLE
CATEGORY_NAMES
TAG_NAMES
HIDE_FROM_SEARCH_ENGINES
HIDE_FROM_SITE_SEARCH
HIDE_FROM_LISTINGS
SUPPLIER_FRIENDLY_ID
SUPPLIER_CODE
GOOGLE_PRODUCT_CATEGORY_ID
PAGE_TITLE
PAGE_DESCRIPTION
CUSTOM_FIELD_[NAME]*
PRICE_[CURRENCY_CODE]_[PRICING_TIER_NAME]**
*The NAME component of the the CUSTOM_FIELD heading should be an existing product type custom field name (e.g. CUSTOM_FIELD_ISBN).
**The CURRENCY_CODE component of the PRICE_ heading should be an existing currency code (USD, NZD, etc) and the PRICING_TIER_NAME component should be an existing pricing tier name (e.g. CURRENCY_USD_RETAIL).
What data do I provide when updating products?
When updating products using the import feature, it is important that the product data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:
PRODUCT_ID must be provided and must reference an existing product (you can get these by doing a product export).
SUB_PRODUCT_ID must reference an existing sub product (you can get these by doing a product export).
TITLE cannot exceed 200 characters.*
NAME - must be unique, only contain letters, numbers and hyphens and cannot exceed 200 characters.*
SUMMARY cannot exceed 600 characters.
DESCRIPTION can only contain valid HTML tags.
SKU cannot exceed 50 characters.
BARCODE cannot exceed 20 characters.
STOCK_CONTROL must be either yes or no.
STOCK_CONTROL - must be either yes or no.
HIDE_IF_NO_STOCK - must be either yes or no.
NOTIFY_IF_LOW_STOCK - must be either yes or no.
NOTIFY_IF_NO_STOCK - must be either yes or no.
ALLOW_ORDER_IF_NO_STOCK - must be either yes or no.
TAX_TYPE - must be no tax, tax exempt, zero-rated, standard rate or reduced rate.
PUBLISH_DATE - must use the following format: dd mmm yyyy hh:mm (e.g. 23 May 2022 15:38).*
EXPIRY_DATE - must use the following format: dd mmm yyyy hh:mm (e.g. 23 May 2022 15:38) and fall after the PUBLISH_DATE.
STOCK - must be a positive number i.e. zero or greater.
LOW_STOCK_THRESHOLD - must be a positive number i.e. zero or greater.
XERO_INVENTORY_ITEM_CODE - Xero must be connected and must reference an existing Xero item code.
XERO_SALES_ACCOUNT_CODE - Xero must be connected and must reference an existing Xero account code.
XERO_TRACKING_CATEGORY_NAME - Xero must be connected and must reference an existing Xero tracking category.
XERO_TRACKING_CATEGORY_OPTION_NAME - Xero must be connected and must reference an existing Xero tracking category option.
WEIGHT - must be a positive number i.e. zero or greater.*
WIDTH - must be a positive number i.e. zero or greater.*
HEIGHT - must be a positive number i.e. zero or greater.*
DEPTH - must be a positive number i.e. zero or greater.*
HS_CODE - cannot exceed 20 characters.
COUNTRY_OF_ORIGIN_TITLE - must reference an existing country.
CUSTOMS_DESCRIPTION - cannot exceed 100 characters.
WEBSITE_STATUS - must be either published or unpublished.*
POS_CHANNEL - must be either yes or no.*
WEBSITE_CHANNEL - must be either yes or no.*
WEIGHTING must be a positive number i.e. zero or greater.
BADGE_LABEL - must reference an existing product badge label.
KEYWORDS - cannot exceed 1000 characters.
MISSING_PRICE_TEXT - cannot exceed 50 characters.
YOUTUBE_VIDEO_ID - cannot exceed 50 characters, if the video ID is unavailable then a valid YouTube video URL can be provided instead.
VIMEO_VIDEO_ID - cannot exceed 30 characters, if the video ID is unavailable then a valid Vimeo video URL can be provided instead.
CATEGORY_NAMES - must reference existing product category names, multiple names must be separated by commas.
TAG_NAMES - must reference existing product tag names, multiple names must be separated by commas.
HIDE_FROM_SEARCH_ENGINES - must be either yes or no.
HIDE_FROM_SITE_SEARCH - must be either yes or no.
HIDE_FROM_LISTINGS - must be either yes or no.
SUPPLIER_FRIENDLY_ID - must reference an existing supplier.
SUPPLIER_CODE - cannot exceed 50 characters.
GOOGLE_PRODUCT_CATEGORY_ID - must reference an existing Google product category.
PAGE_TITLE - cannot exceed 100 characters.
PAGE_DESCRIPTION - cannot exceed 200 characters.
CUSTOM_FIELD_[NAME] - a custom field can only be added if the product uses the correct product type. If the custom field uses the pre defined value type, then the option name must reference an existing option for that custom field.
PRICE_[CURRENCY_CODE]_[PRICING_TIER_NAME] - must be a positive number i.e. zero or greater.
If a supported column is provided without any product data, then that value will be cleared from the relevant products, e.g. if a SUMMARY column was provided without any data then the summary of the relevant products would be cleared.
*Some product values cannot be empty (title, name, website status, etc) so if those supported columns have been provided, then they must contain product data.
Troubleshooting
Despite our best efforts, there are still things that can go wrong while importing products, here are some issues that have previously been encountered:
Product images have not been added - The product import uploads and processes product images after the products have been added, go to Commerce > Products > Products to see how many are still being uploaded and processed. The images will continue to be uploaded and processed with no additional work on your part.
If the imges are not queued then the image URLs that have been provided may not be publicly accessible, a good way to test the accessibility is to see if you can view the image by copying and pasting the image URL into the address bar of your browser.
Category or tag names don't match any existing categories or tags - You may have accidentally provided titles instead of names, for example, a category may have the Title New Arrivals but the Name would actually be new_arrivals. A good way to check is to edit a category or tag and check that the name matches the name you are trying to provide.
Some numeric values are incorrect and contain an 'E+' - This is a behaviour of Microsoft Excel, when you enter a value above a certain length into a cell that is using the number data format, Excel automatically converts that number into a scientific notation with a decimal place after the first digit and an 'E+' near the end To fix the problem do the following:
Open the spreadsheet in Excel and open the Data tab at the top of the page
2. Select the entire column that you are having an issue with by clicking the letter above the column heading (the whole column should be highlighted) then click the Text to Columns button.
3. This will open the Text to Columns wizard, click the Next button to skip through steps one and two. On the third step, select the Text checkbox and click the Finish button.
4. You should then see a small green triangle in all of the cells in the selected column indicating that they are now set to the text data format and can be imported correctly.