CSV rates import
Rates are imported into and exported from eCargo in a CSV file format. This document describes the exact specifications of the format and how to express different rating scenarios using the provided format.
- The format is designed to be round-trippped, meaning that any valid export file will also be a valid import file.
- The format is designed to be flexible enough to accommodate the needs of as many of our customers as possible. So there may be columns and scenarios that are not applicable to everyone but are presented here for completeness.
File extensions and content
Rates are only imported and exported as comma separated value (CSV) files, which have the file extension
.csv. You should be careful not to save an exported CSV file as anything other than a CSV file if you're working in Excel. Excel spreadsheet or workbook files, with file extensions such as
.xlsx are not supported.
Blank lines in the CSV are allowed and will be ignored. Make sure that there are no extra spaces in your fields before or after the comma, because we will treat these as part of the value.
The table below shows the columns that you must include in an eCargo rates CSV file. The first line in the CSV file must be the column headers.
|Rate table type||Rate table type name||Either 'Customer' or 'Supplier'.|
|Customer||Business name||The consignment owner.|
|Carrier||Business name||The name of a carrier as seen when viewing rates in eCargo. Optional.|
|Supplier||Business name||The name of a supplier as seen when viewing rates in eCargo.|
|Charge type||Charge type name||Charge type as seen when viewing rates. For example 'Road Charges - New Zealand'.|
|From zone||Zone name||The name as it appears in the 'Zone mappings' page in eCargo.|
|To zone||Zone name||The name as it appears in the 'Zone mappings' page in eCargo.|
|Rate product||Product name||The name of the product as seen when viewing rates in eCargo. Related to the consignment item.|
|Unit of measure||Unit of measure code||The unit of measure as seen when viewing rates in eCargo. For example: KG.|
|Break from (inclusive)||Decimal (4 d.p.)||The lower limit of this tier. For example, enter 3.5 if the break applies from 3.5 kg.|
|Break to (exclusive)||Decimal (4 d.p.)||The upper limit of this tier. The break will apply if the quantity is less than this.|
|Effective date||Date (day/month/year)||The rates will take effect at midnight in the morning of this date.|
|Expiry date||Date (day/month/year)||The rates will not apply after midnight at the end of this date. Leave blank to never expire.|
|Charge per||Number (no decimal places)||Will be 1 (one) in most scenarios.|
|Rate||Decimal (4 d.p.)||The charge per unit for quantities in this break.|
|Currency||Currency code||The currency the rates are expressed in. For example: NZD.|
|Minimum||Decimal (4 d.p.)||The customer will be charged at least this value for the entire load.|
|Maximum||Decimal (4 d.p.)||The customer will be charged no more than this value for the entire load.|
|Sold to||Business name||Used by specific customers only. Refers to the sold-to business on the consignment.|
|Order line rate product||Order line rate product name||Used by specific customers only. Refers to the product type of the order line items.|
Further details on column formats
Dates are either in the format 'DD/MM/YYYY' where leading '0's can be dropped or in the format 'YYYY-MM-DD' where leading '0's cannot be dropped.
Valid dates may look like:
All decimal values can have up to 4 decimal places.
- 'Rate', 'Minimum' and 'Maximum' all denote monetary values.
- 'Break from', 'Break to' and 'Charge per' all denote quantities.
Valid decimal values may look like:
A three letter ISO-4217 standard currency code, e.g. NZD, USD, AUD, etc.
How to represent different types of rates in the CSV file
This section illustrates various scenarios and how they can be represented in the CSV format.
A break, sometimes called a 'tier', specifies a rate that is charged for quantities of a unit of measure that fall within a range specified by 'Break from (inclusive)' and an 'Break to (exclusive)'.
In the example above $8.00 is charged per unit for quantities of kilograms that are greater than or equal to 20 kg but less than 40 kg.
Sets of breaks
For each set of rates grouped by 'Rate table type', 'Charge type', 'Customer', 'Supplier', 'From zone', 'To zone', 'Order line rate product', 'Carrier', 'Sold to' and 'Unit of measure' there can be only one set of breaks.
A set of breaks can be just one row or can be multiple rows. A set of breaks should try to cover all possible quantities that a charge should sensibly be calculated for. They must start from 0 (zero) and there can be no overlaps between breaks and no gaps between them. Note that a blank "Break from (inclusive)" is the same as 0 (zero), while a blank "Break to (exclusive)" represents no upper limit. See the section below about specifying an upper limit on a set of breaks.
If you only have a fixed charge regardless of quantity (e.g. moving 10 kg costs the same as moving 11 kg) use a 'flat rate'. See section below.
This is an example of a basic set of rates:
The example above shows a set of breaks for goods measured in kilograms (unit of measure code KG).
- The first break is for goods weighing less than 40 kg.
- The second break is for goods weighing 40 kg or more but less than 80 kg.
- The last break is for goods weighing 80 kg or more.
In the example above all goods measured in kilograms (KG) have the same rate per kilogram. This rate applies for quantities from 0 kg up to any number of kg.
Specifying a break with a lower limit of zero
If a break has a lower limit of zero you can simply leave the 'Break from (inclusive)' column blank.
The break shown in this example will be for goods from 0 kg up to (but not including) 40 kg.
Specifying an upper limit for breaks
A set of breaks always has a lower limit of 0 (zero) but you have the choice of an upper limit that is either infinite or a value you specify.
If you specify an upper limit, then consignments with quantities greater than or equal to this upper limit will fail to calculate and will be shown as errors in the invoicing dashboard. This can be a useful safeguard against users entering quantities that are nonsensical.
Otherwise you can simply set the upper limit to be effectively infinite by leaving the 'Break to (exclusive)' column blank.
In the example above the upper limit is 1,000 kg. If a user mistakenly enters a quantity of 10,000 kg on a consignment then it fail to rate and will be shown in the invoicing dashboard as an error.
In the example above any quantity greater than or equal to 100 kg will successfully be calculated at a rate of $8.00 per kilogram.
Specifying a charge per unit of measure
By default the rate is charged for each unit of measure, e.g $25.00 per kg.
It is also possible to specify that the rate is charged in terms of a multiple of the unit of measure instead of just one unit of measure, e.g. $53.75 per 100 kg.
To charge the rate for a multiple of the unit of measure then specify the multiple as a 'Charge per' value.
The example below shows a set of breaks with a charge per 100 kg.
For a consignment with 400 kg of goods the charge would be
400 kg at $53.75 per 100 kg \ = 4 x $53.75 \ = $215.00
It is only possible to set a 'Charge per' value if it is the same for all the breaks in the same group of breaks. Breaks are grouped by 'Rate table type', 'Charge type', 'Customer', 'Supplier', 'From zone', 'To zone', 'Order line rate product', 'Carrier', 'Sold to' and 'Unit of measure'.
Specifying minimums and maximums
A minimum charge is specified in the 'Minimum' column and a maximum charge in the 'Maximum' column.
You can specify a maximum or minimum charge only if it applies to all the rates in the same group. Rates are in the same group if they have the same 'Rate table type', 'Charge type', 'Customer', 'Supplier', 'From zone', 'To zone', 'Order line rate product', 'Carrier' and 'Sold to'.
In the example below both rows belong to the same group of rates, but with different units of measure. As they belong to the same group they must have the same minimum and maximum values.
|Customer||Road charges||Bob's Burgers||Ted's Trucking||Cambridge||Hamilton||...||100||1000||KG|
|Customer||Road charges||Bob's Burgers||Ted's Trucking||Cambridge||Hamilton||...||100||1000||M3|
Specifying a flat rate
Usually, rates are multiplied by the quantity to determine the charge, however in some cases there is a fixed rate for all quantities. For example, if any volume costs $1000 to move, meaning that the total cost to move 13 mÂ³ is the same as the total cost to move 18 mÂ³, this situation applies. This is used for consignments with the same 'Rate table type', 'Charge type', 'Customer', 'Supplier', 'From zone', 'To zone', 'Order line rate product', 'Carrier', 'Sold to' and 'Unit of measure'.
We can specify a 'flat rate' by setting a maximum and minimum with the same values.
|Customer||Road charges||Bob's Burgers||Ted's Trucking||Cambridge||Hamilton||...||100||100||KG|
Specifying a zero rate
In some instances we may want to specify a rate that has no charge. To do this you only need to specify a 'Rate' with a value of 0 (zero).
In this example consignments of pallets (PAL) going from Cambridge to Hamilton have no charge.
|Customer||Road charges||Bob's Burgers||Ted's Trucking||Cambridge||Hamilton||...||0||PAL|