Format a CSV File for LTL Tiers

Overview

Contract Manager expects a specific format for the CSV files it uses to look up discounts and minimums for LTL shipments. These CSV files are created in Microsoft Excel and saved as CSV (Comma delimited) files.

Header Row

The first row of the file must contain the column labels. The name of the label is not important, but the order of the data cannot change.

1. Origin Warehouse Code (Wcode) - the Contract Manager ID code for a specific origin location
2. Origin City (OrgCity) - a specific origin city
3. Origin State (OrgState) - a specific origin state
4. Origin ZIP From (OrgZipFrom) - the low value of a range of ZIP codes for the origin
5. Origin ZIP To (OrgZipTo) - the high value of a range of ZIP codes for the origin
6. Destination Warehouse Code (Wcode) - the Contract Manager ID code for a specific destination location
7. Destination City (DstCity) - a specific destination city
8. Destination State (DstState) - a specific destination state
9. Destination ZIP From (DstZipFrom) - the low value of a range of ZIP codes for the destination
10. Destination ZIP To (DstZipTo) - the high value of a range of ZIP codes for the destination
11. Weight From (WtFrom) - the lowest valid weight for a shipment
12. Weight To (WtTo) - the highest valid weight for a shipment
13. Pallet Positions From (PalFrom) - the lowest valid number of pallet positions for a shipment
14. Pallet Positions To (PalTo) - the highest valid number of pallet positions for a shipment
15. Cube From (CubeFrom) - the lowest valid cubic capacity for a shipment
16. Cube To (CubeTo) - the highest valid cubic capacity for a shipment
17. Feet From (LinFtFrom) - the lowest valid lineal footage for a shipment
18. Feet To (LinFtTo) - the highest valid lineal footage for a shipment
19. Miles From (MilesFrom) - the lowest valid mileage for a shipment
20. Miles To (MilesTo) - the highest valid mileage for a shipment
21. Direct Discount (DirDisc) - the discount if the carrier moves the freight direct
22. Direct Floor Minimum (DirMin) - the minimum cost if the carrier move the freight direct
23. Origin Partner Discount (OrgIntDisc) - the discount if an advance interline partner is used
24. Origin Partner Floor Minimum (OrgIntMin) - the minimum cost if an advance interline partner is used
25. Destination Partner Discount (DstIntDisc) - the discount if a beyond interline partner is used
26. Destination Partner Floor Minimum (DstIntMin) - the minimum cost if a beyond interline partner is used
27. Origin and Destination Partner Discount (Org&DstIntDisc) - the discount if two interline partners are used
28. Origin and Destination Partner Floor Minimum (Org&DstIntMin) - the minimum cost if two interline partners are used

Data Rows

Every origin and destination pair from the contract must be entered as a separate line in the CSV file. For a typical LTL rate matrix, which includes rates for each state to every other state, this can result in files of over 2,400 lines. For an easy way to convert a standard LTL rate matrix to a list that can be used in a Contract Manager CSV file, see Convert an LTL Rate Matrix to a List.

Some important considerations in entering data:

• Columns A through J can be left blank if the field is not being used.
• Columns K through AB must include a 0 if the field is not being used. Contract Manager will treat a 0 as "ANY."
• All values are inclusive. For example, if a shipment is considered a TL shipment instead of LTL at 20,000 lbs, the Weight To column should list 19,999, not 20,000, since the maximum valid weight is 19,999 pounds.
• Consider leaving columns M through T as 0. Contract Manager does not always return correct rates to Optimiz if the expected variables are not used during rating calls when limits exist on these values.
• Even if interline partners are not being used, copy the direct discount and minimum rates to all interline options to ensure that rates are always returned.
• If a discount exists, it must be entered as text equivalent of its value. Values recorded as percentages are often transferred to the CSV file as decimals. That is, 75.3% may be recorded as 0.753, which Contract Manager will treat as a discount of 0.753%.

Saving the File

Once the headers have been defined and the data has been entered, the file needs to be saved in a format Contract Manager can read, namely CSV, or comma-separated values. Microsoft Excel has the ability to create a CSV file.

It is important to follow a consistent naming format for these CSV files, since the file name must be accurately typed into Contract Manager; it is not possible to select available files from a list. The standard naming convention for these files is CustomerName-TransportationMode-ShipmentType-CarrierSCAC-EffectiveDate.csv. For example, a CSV file detailing Robert Bosch's LTL direct rates with Con-Way Freight effective January 1, 2015, would be named Bosch-LTL-Direct-CNWY-01012015.csv.

To Save an Excel File as a CSV File:

1. Open the Microsoft Excel spreadsheet.
2. Click File.
3. Click Save As.
4. Enter the File name according to the naming convention described above.
5. Select CSV (Comma delimited) (*.csv) from the Save as type dropdown.
6. Click Save.

If the original spreadsheet has more than one tab, a warning message will appear, explaining that a CSV file cannot have multiple tabs and only the current tab will be saved.

7. Click OK.

A warning message will appear, explaining that special formatting will be lost in the conversion to CSV.

8. Click Yes.

Result

The rate template is saved in a format that can be read by Contract Manager. It can now be sent to Help Desk to upload to the Contract Manager server so it can be used in contracts.