Uploading Rate Sheets (Excel/CSV): Templates, Required Fields, Mapping, and Validation

Uploading rate sheets into Velocity lets you replace spreadsheets with a centralized rate library your entire team can trust. This guide explains how to prepare your file, map columns correctly, validate the data, and confirm the imported rates are accurate before they feed quoting.

Velocity supports bulk uploads from Excel/CSV with guided field mapping, so you can import both contract and spot rates quickly and consistently.

Before You Upload: Prepare Your File (Cleanliness Checklist)

Use this checklist to prevent 90% of upload failures and pricing mismatches.

File Format and Structure

• Use .xlsx, .xls, or .csv (one file per upload batch).
• Keep one header row (no merged cells).
• Avoid multiple tables in one sheet (one dataset per tab; upload one tab at a time if needed).
• Remove empty rows, subtotal lines, comments, and notes.

If your rate sheet contains lanes priced in multiple currencies, it’s important to understand how Velocity applies currency conversions during pricing and quoting. Review Currency and Exchange Rates to learn how exchange rates are handled, how default currencies are applied, and how to avoid discrepancies between uploaded rate values and calculated prices.

Data Consistency

• Use a single date format
• Use consistent currency codes
• Standardize location identifiers
• Ensure numeric fields are numeric (no embedded text like “approx.” or “tbd”).

Reduce Ambiguity

• Do not mix modes (FCL/LCL/Air/Courier) in the same row unless your template explicitly supports it.
• Do not mix different equipment/unit types in the same column (e.g., “40HC” and “per kg”).
• Put all charges you want quoted in the file, including common surcharges, or plan to manage them separately in your surcharge rules.

Recommended Pre-Upload QA

• Filter for blank cells in required columns.
• Filter validity dates to ensure start < end.
• Sort by lane and provider to catch duplicates.

Required Fields (Minimum Viable Rate)

If you are migrating from Excel, you can start with a “minimum viable template” that supports correct quoting and comparisons. Add additional columns later as your governance matures.

Minimum Template Field List (Must Have)

Lane

• Origin (city/port/airport code or name)
• Destination (city/port/airport code or name)

Service/Mode

• Mode: FCL / LCL / Air / Courier
• Service level (optional but recommended): Standard / Express, Direct / Transshipment, etc.

Validity

• Valid From
• Valid To

Provider

• Carrier / Vendor / Provider Name (who the buy rate comes from)

Equipment / Unit

• For FCL: container type (e.g., 20GP, 40GP, 40HC)
• For LCL: rate unit (e.g., per CBM, minimum charge)
• For Air/Courier: rate unit (e.g., per kg, weight break if applicable)

Charges (Line Items)
At minimum include:

• Base Freight (or your equivalent core charge)
• Any mandatory surcharges included in your sell/buy logic (fuel, security, peak, etc.)

Currency

• Currency Code (USD/EUR/AED…)

Strongly Recommended (Improves Quote Accuracy)

• Transit time (if available)
• Routing (direct vs via)
• Minimum charge (especially for LCL and Air)
• Notes/conditions (hazmat, temperature control, special equipment)

Upload Flow: Choose File → Map Columns → Validate → Import

This is the practical flow you should follow every time you upload rates.

Step 1: Choose Your File

1. Export or save your rate sheet as Excel or CSV.
2. Confirm it includes the minimum viable fields above.
3. If you have multiple tabs, upload the most critical one first (your top lanes and providers).

Step 2: Select Upload Type (Contract vs Spot)

• Contract rates: stable pricing with a defined validity window.
• Spot rates: market pricing that changes frequently; usually shorter validity and more frequent updates.

Best practice: do not mix contract and spot in one file unless your internal process explicitly supports it.

Step 3: Map Columns (Guided Field Mapping)

Velocity will prompt you to map each spreadsheet column to the correct Rate Management field.

How to map effectively

• Map Origin and Destination first (lane integrity matters most).
• Map Mode and Equipment/Unit next (prevents unit mismatch during quoting).
• Then map Validity and Currency.
• Finally, map charge columns (Base Freight and surcharges).

If Your Headers Don’t Match (Field Mapping Guidance)

If your spreadsheet headers are not “clean,” use these mapping rules:

• If your header is vague (e.g., “POL”), map it to Origin (Port of Loading).




• If your header is “POD,” map it to Destination (Port of Discharge).




• If your file uses airport codes, map those columns to Origin Airport and Destination Airport (if available) or to Origin/Destination with a consistent location format.




• If you have “20/40/40HC” as separate columns, map them to the correct equipment-specific rate fields (or import as separate rows if your template expects one equipment per row).




• If you have “All-in rate,” decide whether it should be imported as:

  • Base Freight (if you handle surcharges separately), or
  
  
  • Total Freight (if your quoting expects a single consolidated charge line)
  
  

Rule of thumb: if quoting needs an itemized breakdown, do not import “all-in” as a single number unless you explicitly want non-itemized quotes.

Step 4: Validate Before Import

Run validation checks (either via Velocity’s validation step or your own quick filters) to catch errors early.

Step 5: Import

Once validation passes, import the file. For large datasets, import in batches:

• Start with top lanes and top providers
• Expand coverage after you confirm the model is correct

Validation Rules (What to Check and Why)

Validation ensures rates behave correctly in quoting and comparisons.

1) Date Range Issues

Symptoms

• Rates don’t appear in quotes
• A newer upload overrides active rates unexpectedly

Checks

• Valid From is earlier than Valid To
• No overlapping validity windows for the same lane/provider/equipment unless intended
• Spot rate validity is short and current

2) Duplicates

Symptoms

• Multiple identical rates appear
• Wrong rate wins during quoting

Checks

• Duplicates by: Origin + Destination + Mode + Provider + Equipment/Unit + Validity
• If duplicates exist, decide the rule (latest version wins, or lowest cost wins)

3) Missing Lane Data

Symptoms

• Quote returns “no rates found”
• Only partial lane coverage appears

Checks

• Origin and destination are populated on every row
• Locations use consistent naming/codes (avoid mixing city names and port codes randomly)

4) Currency Problems

Symptoms

• Totals look wrong by a large factor
• Rates compare incorrectly across providers

Checks

• Currency code present and consistent
• Rate values are numeric
• If mixing currencies, confirm your quoting uses the expected conversion approach

5) Equipment/Unit Mismatch

Symptoms

• FCL rates applied to the wrong container type
• LCL rates priced as “per shipment” instead of per CBM/kg

Checks

• FCL container type is explicit
• LCL unit (CBM/kg/minimum) is explicit
• Air/Courier unit and any weight breaks are defined

After Upload: Spot-Check and Reconcile (Sampling Method)

Do not rely on “successful import” alone. Always reconcile.

Recommended Sampling Method (10–15 minutes)

1. Pick 5–10 top lanes (highest-volume or highest-revenue).




2. For each lane, test one quote per mode you imported.




3. Compare:

   • Imported base freight
   
   
   • Key surcharges
   • Currency and totals
   • Validity selection behavior (correct rate appears for today’s date)
   
   

Reconcile Against the Source Sheet

• Match 1:1 for at least 10 rows across different lanes/providers.
• If discrepancies appear, do not patch the quote. Fix the source data or mapping and re-upload.

Best practice: keep a short internal checklist of “golden lanes” you always use for testing.