Skip to main content
Rate Management

Import rates with Excel templates

Proxio's Excel based import templates can be used to import your rates into Proxio.

Written By Johan Vagerstam

Last updated About 2 hours ago

Download the templates from ‘Rates’ > ‘Rate documents’ > ‘Import’. In the left panel, under ‘Import templates’, select ‘Standard template’ or ‘Multileg template’.

About using Excel Rate import templates

The templates can be used to import rates in a structured way, following the instructions and documentation that are available inside the template.

There are two worksheets (tabs) in the Excel template files.

Import - This worksheet is where you add information about the tariff / rates. This is the actual data that will be imported.

Picture of Import worksheet in the Excel template


Documentation - This is a worksheet where you find instructions and examples about the data, if a column is optional or mandatory.

Picture of Documentation worksheet in the Excel template.

Note:
The standard import template is a general template used for many (but not all) rate formats. The documented descriptions gives guidance about how to fill in the template, but there can be situations where a column value is mandatory although documentation say it’s optional.

The standard template cannot be used for distance based pricing logic - e.g. when one of the price axis are distance based. For distance based pricing please use the Import Wizard (see the article Using the Import wizard).

Naming guidelines for Excel templates

The Tariff Name entered in the import template is very important. If you want to add all data in the Excel file into one tariff/rate sheet - you need to specify the exact same Tariff Name on every row. For more information, please review the column description table below.

Standard import template

Use this template when you have prices of a relation/lane based transport. This is often used for A→ B Transports, from a supplier to a factory or from a warehouse to a customer.

Below is a summary of the columns in the import template and description of each column. (The descriptions are also available in the Documentation tab in the Excel template.)

Column

Description

Tariff Name

The name for the created tariff document. One tariff/Rate document will be created for each unique name during automatic multi-tariff import. All rows with the same Tariff Name well be stored in the same tariff.

If you import a rate sheet with the same name, services and legs as an existing one, Proxio updates the existing rate sheet instead of creating a duplicate. See Re-importing rates below.

Tags

Tags to tag the tariff document with. Separate multiple tags with comma (,) or semicolon (;).

Carrier Keys

Used for custom match logic. Used together with RouteKeys or by itself.

Route Keys

Used for custom match logic. Used together with RouteKeys or by itself.

Valid From

The date that the tariff is valid from. If no time is specified, it will be valid from midnight local time.

Must have the same value for all rows belonging to the same tariff-revision.

Valid To

The date that the tariff is valid to.

Must have the same value for all rows belonging to the same tariff-document.

Carrier service

The name or code of the carrier service in Proxio Tariffs. The whole name is not required, as long as the specified name only match one service.

Must have the same value for all rows belonging to the same tariff. You can also use a carrier service that is described in the spreadsheet itself, even if it is not yet available in Proxio. The service keeps its own details when you edit, revise or calculate. In the multileg template, each leg can have its own carrier and service.

Carrier service tags

Tags to tag the carrier service. Separate multiple tags with comma (,) or semicolon (;).

Carrier tags

Tags to tag the carrier. Separate multiple tags with comma (,) or semicolon (;).

Imperial/Metric

Selects the units for weight, volume and conversion factors. A blank cell uses Metric. If an older file lacks this column, the existing carrier service unit system applies. Values are converted to the existing service unit system when needed. An unknown carrier service is an error.

Origin Country

Two-letter country code (ISO 3166-1 alpha-2), or the english name, for the origin country.

Multiple country codes can be specified by separating them with comma (,). When multiple country codes are specified, postal code and city/port must be left blank.

Origin Postal Code

The postal/zip code for the origin. Use semicolon as interval spearator because hyphens are part of some postal codes.

Both complete codes and intervals are supported. Intervals can be entered as prefixes (eg "123" for "12300-12399", if country with five digit codes), as partial intervals (eg "12:14" for "12000-14999") or complete code intervals (eg "12345:23456").

Multiple codes and intervals can be specified by separating them with comma (,).

Origin State/Region/Province

Clear text name or code. When city is also defined the city AND the State/Region/Province need to match.

Origin LocationCode

IATA or UN/LOCODE identifier. Create a separate code definition; do not combine LocationCode with City, Postal code or State/Region/Province in the same definition.

Origin City

City name

Destination Country

Two-letter country code (ISO 3166-1 alpha-2), or the english name, for the destination country. List of country code are supported (see Origin Country)

Destination Postal Code

The postal/zip code for the destination. Lists and intervals are supported (see Origin Postal code above).

Destination State/Region/Province

Clear text name or code. When city is also defined thecity AND the State/Region/Province need to match.

Destination LocationCode

IATA or UN/LOCODE identifier. Create a separate code definition; do not combine LocationCode with City, Postal code or State/Region/Province in the same definition.

Destination City

City name

Currency

Three letter code (ISO 4217) for the freight price currency. May vary per relation.

Price unit

The price unit used for freight prices when a freight amount column does not specify one. Use a unit from the Shared, Metric or Imperial lists to the right. All rows in one rate document must use the same price unit.

Different price units for different relations is not supported.

Supported price units (several alternate spellings and variants are also supported):

Shipment/shp
Unit/Container/Pallet/Package/Parcel/Piece/pcs
CubicMetre/cbm
500g
1kg/kgs
5kg
10kg
25kg
50kg
100kg
1000kg/1t/tonne
UnitAndKg
TonneKilometre/tkm
WMSea
LoadingMetre/ldm
PalletSpaces/PPL

Min price

The minimum total freight price for the shipment. May vary per relation.

Freight amount axis and prices

One or more columns define freight amount intervals and prices.

Put a unit after the interval (for example 1-<100kg or >100lb); the first header may establish the unit for subsequent columns.

Use < or > for exclusive bounds.

Open first or last intervals are allowed; intervals must not overlap or leave gaps.

A single column needs no interval. Alternatively write Weight: or Volume: before the interval.

Metric and Imperial axis units are grouped to the right.

To override the Price unit column, append per <price unit> in the header.

If the Price unit column is blank, every freight-price header needs a price unit.

Max price

The maximum freight price for the shipment, or the FTL price. May vary per relation.

Conversion
volume to weight

Conversion factor for converting volume in cubic metres to chargeable weight in kilograms. May vary per relation.

Conversion
ldm to weight

Conversion factor for converting loading meters to chargeable weight in kilograms. May vary per relation.

Conversion
ps to weight

Conversion factor for converting pallet spaces to chargeable weight in kilograms. May vary per relation.

Minimum
Weight (kg/lb)

Minimum chargeable weight for the whole shipment in kilograms. May vary per relation.

Minimum
Volume (m³/ft³)

Minimum chargeable volume for the whole shipment in cubic meters. May vary per relation.

Surcharges []

For more information about surcharges and pricing information on each transport leg, please review the documentation tab in the Excel template.

As example, here is a standard template with example data for FCL (Full Container Load). As the name for the rows in the example are the same, they would be added into the same Tariff/Rate sheet after the import is made in Proxio.

SEA-FCL-Example filled in.xlsx

40.7 KB• Spreadsheet

Location, units and column names

City, state and location code. The templates have separate columns for city, for state/region/province and for location code, for both origin and destination. Use state/region/province for countries like the US, where a city and postal code are not enough. Use a location code for places such as airports (IATA) and ports (UN/LOCODE).

Older templates still work. Files with the old column headings can still be imported.

Columns are read by name. Proxio reads the columns by their heading, not by their position. Keep the headings exactly as in the template, but you can change the order of the columns.

Metric or imperial units. A tariff can state if it uses metric or imperial units. For Proxio's predefined carrier services, weight and volume limits are converted to the units of the service. For services described in the spreadsheet, the units and values in the file are kept as they are, also in multi-leg tariffs.

US ZIP codes. 12345 and 12345-0000 count as the same place. Proxio still stores the code as you typed it.

Re-importing rates

When you import new rates for a rate sheet that already exists, Proxio reuses the existing rate sheet if the name, services and legs match:

  • Same start date (Valid From): the current revision is updated.

  • New start date: a new revision is added.

If you change the dates of a revision to correct it, the revision keeps its identity. You cannot edit a revision until it has fully loaded.

Exact values and ranges. Freight columns can hold exact values, for example a set number of pallet places, as well as ranges. More pallet place headings and price headings with units are supported. If a column in the freight price section is not recognised, the import stops with an error, so no prices are left out without you knowing.

Standard Multileg template

Use this template when you have pricing of multiple transport legs. This is most often used in Air and Ocean when there are pre-carriage, main carriage and on-carriage.

An example:

  • Pre-Carriage: Inland transport pricing by road from a factory to a sea port.

  • Main-Carriage: Sea port to sea port pricing for on ocean container transport.

  • On-Carriage: Inland transport pricing by road from sea port to a warehouse.

The prices of multi leg transport allows for a combination of multiple pricing sheets into one worksheet, e.g. the Excel based multi leg template.

Note: For the multileg template, there are extra options to add three (3) types of surcharges. You can build up the columns so they fit the price brackets you would like to import.

As example, here is a multi leg template with example data added to pre-carriage, main-carriage and on-carriage. As the name for the 3 rows in the example are the same, they would be added into the same Tariff/Rate sheet after the import is made in Proxio.

[Add attached file with example rows filled with multileg example-template]


Customized import template

If you have needs that goes beyond the standard templates, there is an option to adjust the import templates to your own formats. Consult your Key Account Manager or contact Proxio support team if you are interested in learning more about customised import templates.

How to Import an Excel Rate template

Follow these steps to import your rates in Proxio.

  1. select ‘Rates’ > ‘Rate documents’ > ‘Import’. Select the file you want to import.

  2. When the file is uploaded you can view it in the list view.

  3. Click on the drop-list icon, and select ‘Relation-tariff template, Multiple tariffs‘ if you import rates using the standard template (single leg) or select ´Relation-tariff template, Multiple tariffs´ if you are using the multileg template.

  4. On the following screen, you have some optional fields to add a suffix or prefix. Just click ‘Queue import‘. The file is now pending import on the server. After a few moments the import should start. The time can vary depending on server load at that time.

  5. When processing is done and successful, the rate-sheet(s) create will be available as links in the dialogue. In this example, one bid sheet was created.