All Collections
Commerce
Settings
Importing shipping rates
Importing shipping rates

How to bulk add/update shipping rates by importing a spreedsheet of shipping rate data.

Callum McFadgen avatar
Written by Callum McFadgen
Updated over a week ago

How does importing shipping rates work?

Our import feature allows you to bulk add or update your shipping rates by uploading a Microsoft Excel spreadsheet that contains a single sheet of shipping rate data structured into specific columns.

How do I bulk add shipping rates?

  1. Go to Commerce > Settings > Shipping rates.

  2. Click the Import button in the top right.

  3. Tick Adding new organisations under Which type of import you are doing?

  4. A help box will appear telling you how to structure your spreadsheet columns correctly, see How do I structure my spreadsheet columns?

  5. Once you've structured your spreadsheet columns, add the shipping rate data , see What data do I provide when adding shipping rates?

  6. Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.

  7. 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 bulk update shipping rates?

  1. Go to Commerce > Settings > Shipping rates..

  2. Click the Import button in the top right.

  3. Tick Updating existing shipping rates under Which type of import you are doing?

  4. A help box will appear telling you how to structure your spreadsheet columns correctly, see How do I structure my spreadsheet columns?

  5. Once you've structured your spreadsheet columns, add the shipping rate data, see What data do I provide when updating shipping rates?

  6. Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.

  7. 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 and 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 shipping rates? and What are the columns for updating shipping rates? for the supported headings. All column headings should be in uppercase.

An easy option for structuring a spreadsheet is to first export your shipping rates, 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 shipping rates?

When adding shipping rates, the TITLE, CURRENCY_CODE, PRICING_TIER_NAME, PRICE, MINIMUM_ORDER_PRICE, MINIMUM_ORDER_WEIGHT, MINIMUM_ORDER_VOLUME, MINIMUM_DELIVERY_DISTANCE and MINIMUM_DELIVERY_DURATION are required, all other columns are optional. The supported columns are as follows:

  • TITLE

  • COUNTRY_TITLE

  • COUNTRY_REGION_TITLE

  • SUPPORTED_POSTAL_CODES

  • UNSUPPORTED_POSTAL_CODES

  • CURRENCY_CODE

  • PRICING_TIER_NAME

  • WEBSITE_STATUS

  • PRICE

  • MINIMUM_ORDER_PRICE

  • MAXIMUM_ORDER_PRICE

  • MINIMUM_ORDER_WEIGHT

  • MAXIMUM_ORDER_WEIGHT

  • MINIMUM_ORDER_VOLUME

  • MAXIMUM_ORDER_VOLUME

  • MINIMUM_DELIVERY_DISTANCE

  • MAXIMUM_DELIVERY_DISTANCE

  • MINIMUM_DELIVERY_DURATION

  • MAXIMUM_DELIVERY_DURATION

  • XERO_INVENTORY_ITEM_CODE

  • XERO_SALES_ACCOUNT_CODE

  • XERO_TRACKING_CATEGORY_NAME

  • XERO_TRACKING_CATEGORY_OPTION_NAME

What data do I provide when adding shipping rates?

When adding shipping rates using the import feature, it is important that the shipping rate data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:

  • Each row of your spreadsheet is read as a single shipping rate.

  • TITLE - must be provided and cannot exceed 100 characters.

  • COUNTRY_TITLE - must reference an existing country.

  • COUNTRY_REGION_TITLE - must reference an existing country.

  • SUPPORTED_POSTAL_CODES - cannot exceed 10 characters, multiple postal codes must be separated by commas.

  • UNSUPPORTED_POSTAL_CODES - cannot exceed 10 characters, multiple postal codes must be separated by commas.

  • CURRENCY_CODE - must be provided and reference an existing currency code (i.e. USD, NZD, etc).

  • PRICING_TIER_NAME - must reference a sellable pricing tier name.

  • WEBSITE_STATUS - must be either published or unpublished.

  • PRICE - must be a positive number (i.e. zero or greater).

  • MINIMUM_ORDER_PRICE - must be a positive number (i.e. zero or greater).

  • MAXIMUM_ORDER_PRICE - must be a positive number (i.e. zero or greater).

  • MINIMUM_ORDER_WEIGHT - must be a positive number (i.e. zero or greater).

  • MAXIMUM_ORDER_WEIGHT - must be a positive number (i.e. zero or greater).

  • MINIMUM_ORDER_VOLUME - must be a positive number (i.e. zero or greater).

  • MAXIMUM_ORDER_VOLUME - must be a positive number (i.e. zero or greater).

  • MINIMUM_DELIVERY_DISTANCE - must be a positive number (i.e. zero or greater).

  • MAXIMUM_DELIVERY_DISTANCE - must be a positive number (i.e. zero or greater).

  • MINIMUM_DELIVERY_DURATION - must be a positive number (i.e. zero or greater).

  • MAXIMUM_DELIVERY_DURATION 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.

What are the columns for updating shipping rates?

When updating shipping rates, the SHIPPING_RATE_ID is required, all other columns are optional. The supported columns are as follows:

  • SHIPPING_RATE_ID

  • TITLE

  • SUPPORTED_POSTAL_CODES

  • UNSUPPORTED_POSTAL_CODES

  • WEBSITE_STATUS

  • PRICE

  • MINIMUM_ORDER_PRICE

  • MAXIMUM_ORDER_PRICE

  • MINIMUM_ORDER_WEIGHT

  • MAXIMUM_ORDER_WEIGHT

  • MINIMUM_ORDER_VOLUME

  • MAXIMUM_ORDER_VOLUME

  • MINIMUM_DELIVERY_DISTANCE

  • MAXIMUM_DELIVERY_DISTANCE

  • MINIMUM_DELIVERY_DURATION

  • MAXIMUM_DELIVERY_DURATION

  • XERO_INVENTORY_ITEM_CODE

  • XERO_SALES_ACCOUNT_CODE

  • XERO_TRACKING_CATEGORY_NAME

  • XERO_TRACKING_CATEGORY_OPTION_NAME

What data do I provide when updating shipping rates?

When updating shipping rates using the import feature, it is important that the shipping rate data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:

  • SHIPPING_RATE_ID - must be provided and must reference an existing shipping rate (you can get these by exporting shipping rates).

  • TITLE - cannot exceed 100 characters. *

  • SUPPORTED_POSTAL_CODES - cannot exceed 10 characters, multiple postal codes must be separated by commas.

  • UNSUPPORTED_POSTAL_CODES - cannot exceed 10 characters, multiple postal codes must be separated by commas.

  • WEBSITE_STATUS - must be either published or unpublished.

  • PRICE - must be a positive number (i.e. zero or greater).*

  • MINIMUM_ORDER_PRICE - must be a positive number (i.e. zero or greater).*

  • MAXIMUM_ORDER_PRICE - must be a positive number (i.e. zero or greater).

  • MINIMUM_ORDER_WEIGHT - must be a positive number (i.e. zero or greater).*

  • MAXIMUM_ORDER_WEIGHT - must be a positive number (i.e. zero or greater).

  • MINIMUM_ORDER_VOLUME - must be a positive number (i.e. zero or greater).*

  • MAXIMUM_ORDER_VOLUME - must be a positive number (i.e. zero or greater).

  • MINIMUM_DELIVERY_DISTANCE - must be a positive number (i.e. zero or greater).*

  • MAXIMUM_DELIVERY_DISTANCE - must be a positive number (i.e. zero or greater).

  • MINIMUM_DELIVERY_DURATION - must be a positive number (i.e. zero or greater).*

  • MAXIMUM_DELIVERY_DURATION - 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.

If a supported column is provided without any shipping rate data, then that value will be cleared from the relevant shipping rate, e.g if a SUPPORTED_POSTAL_CODES column was provided without any data then the supported postal codes of the relevant shipping rates would be cleared.

*Some shipping rate values cannot be empty (title, price, etc) so if those supported columns have been provided, then they must contain data.

Did this answer your question?