Freight Rate Sheets: AI Extracts Carrier Rates From PDFs

A forwarder can extract carrier rates from PDFs and emails with AI by giving the assistant one file at a time, asking for a fixed set of columns (lane, container or weight break, rate, currency, surcharges, validity dates), and reviewing the output against the source before anything reaches a quote. The extraction takes minutes; the checking is what makes it safe.

Most failures are quiet ones: a validity date read in the wrong order, a surcharge that sits in a footnote and never reaches the table, or a rate for a 40-foot box filed under a 20-foot heading. A fixed sheet layout, a strict prompt and a ten-minute check catch those slips before a customer does.

Follow me on Instagram@sagnikteaches

The columns a forwarder's rate sheet needs before any AI touches it

Decide the destination before you extract anything. If the AI is allowed to choose its own columns, every carrier file comes back in a different shape and the merging becomes the new manual job. Fix the layout first, then force every file into it.

Connect on LinkedInSagnik Bhattacharya

A workable set for ocean and road-freight rates is below. Trim it to what your quotes really use, but keep the validity and source columns, because those are the two that protect you later.

Subscribe on YouTube@sagnikteaches

Put that header row into a spreadsheet and keep it as a template. When someone asks "where did this rate come from?", the source file and page columns answer it without a search through shared folders.

Why carrier PDFs trip up a general-purpose assistant

Carrier rate documents are written for a person skimming a page, not for a parser. Some patterns repeat across carriers, and knowing them tells you what to put in the prompt.

• Tables that continue across pages. A lane list runs onto page three with the column headings printed only on page one. The AI may treat the page-three numbers as a different table or misalign the columns.
• Merged cells. One origin spans six destination rows. If the extraction does not repeat the origin on each row, the sheet loses its keys.
• Validity in the header or footer. "Rates valid 01/03 to 31/05" printed once at the top applies to every line, but a sheet-reading tool may not attach it to any of them.
• Surcharges outside the table. Emergency, peak-season or fuel-style charges often appear in a notes block below the rates, sometimes as a percentage of the base rate and sometimes as a flat amount per container.
• Two units in one table. A single page can mix per-container and per-tonne lines with only a small unit label to tell them apart.
• Email rates. A rate quoted in the body of an email, or in a reply three messages down a thread, with "same as last time but plus 50" as the whole instruction.

Your prompt should deal with each of these in plain words. The AI follows an explicit rule far better than it guesses at an implied one.

Step 1: sort the files before you upload them

Spend two minutes on housekeeping. Rename each file carrier_origin-region_valid-from.pdf using the date in year-month-day order, drop old versions into an archive folder, and keep one carrier per upload. Mixed uploads are how a rate from carrier A ends up filed against carrier B.

Next, separate files into three kinds, because each wants a slightly different instruction:

1. Text PDFs exported from the carrier's system. You can select and copy the text. These extract best.
2. Scanned or photographed pages. You cannot select the text. Run them through OCR first (a scanner app, or the OCR feature in a PDF editor), then treat every digit as suspect.
3. Emails and spreadsheets attached to emails. Copy the body into a plain-text file with the sender, date and subject at the top so the AI knows when the quote was made.

If a carrier already sends an Excel file, use that. Extraction exists for the files nobody will give you in a tidier form.

Step 2: the extraction prompt, with a sample of what comes back

Use a business-plan assistant, not a free consumer account, for contract rates; the pricing and data terms differ by plan, so read the vendor's current data-use page before you upload anything commercial. The prompt below works in ChatGPT, Claude or Gemini with the file attached.

You are extracting carrier freight rates into a fixed table. Read the attached file and return a CSV with exactly these columns:
carrier, origin_code, origin_name_as_printed, destination_code, destination_name_as_printed, service, unit, base_rate, currency, surcharge_name, surcharge_amount, surcharge_basis, valid_from, valid_to, conditions, source_page

Rules:
1. One row per lane per unit. Repeat the origin on every row if the PDF merges cells.
2. If a surcharge applies to many lanes, give it its own row with the same lane and unit, so the base rate stays clean.
3. Dates as YYYY-MM-DD. If the document prints a date such as 03/04/2027 and you cannot tell day from month, write AMBIGUOUS in the date cell and list it in the notes.
4. Never calculate, round or convert anything. Copy the numbers as printed.
5. If a value is missing or unreadable, write MISSING. Never guess.
6. Anything in a footnote, header or notes block that changes a rate (minimums, subject-to wording, commodity limits) goes in the conditions column with the page number.
7. After the CSV, list every row you are less than certain about and say why.

Here is an illustrative output for a four-line extract (the lanes and figures are made up, and the name-as-printed and service columns are left out to fit the page):

carrier,origin_code,destination_code,unit,base_rate,currency,surcharge_name,surcharge_amount,surcharge_basis,valid_from,valid_to,conditions,source_page
Carrier A,PORT-A,PORT-B,40ft,2150,USD,,,,2027-03-01,2027-05-31,"Subject to space",2
Carrier A,PORT-A,PORT-B,40ft,,USD,Peak season,300,per container,2027-04-15,2027-05-31,"Applies to sailings from 15 April",3
Carrier A,PORT-A,PORT-C,20ft,1480,USD,,,,2027-03-01,2027-05-31,"Max 18 tonnes",2
Carrier A,PORT-A,PORT-C,40ft,MISSING,USD,,,,2027-03-01,2027-05-31,"",2

Uncertain rows:
- Row 4: the 40ft rate cell for PORT-C is blank in the table; page 2 has a smudged figure that could be 2450 or 2150.

That uncertain-rows list is the most valuable part of the reply. You now know exactly where to look first, and the assistant has told you honestly that it did not guess. If your prompt does not ask for it, many assistants will fill a gap with a plausible number.

Validity dates: the field that costs real money

A rate used a day after it expires is a rate you may have to honour out of your own margin. Treat validity as data to extract and to test, not as a heading to remember.

Three date traps

• Day-month order. "03/04/2027" is 3 April in some documents and 4 March in others. Ask for ISO dates and make the AI mark anything it cannot resolve. Then settle the question by looking at the carrier's other dates: a sheet that also prints "31/05" has told you the order.
• Sailing date versus booking date. Some sheets are valid for departures between two dates; others for bookings made between two dates. Add a note column called validity_basis if your carriers mix the two.
• Rolling extensions. A carrier emails "all rates extended to 30 June". The original PDF still says 31 May. Update the valid_to cell, write the email's date in the source column, and keep both files.

Once the dates are clean, add a conditional-format rule to the sheet: a row turns amber when valid_to is less than 14 days away and red when it has passed. With the prompt's column order, valid_to lands in column N, so a formula such as =AND(N2<TODAY()+14, N2>=TODAY()) in an amber rule and =N2<TODAY() in a red one does it. Your quoting team now sees expired rates without anyone having to remember.

Surcharges: keep them in their own rows

If you merge surcharges into one all-in number during extraction, you lose the ability to see which part of a price moved when the carrier re-issues the sheet. Keep base and surcharge lines separate in the extraction, then combine them with a formula on a separate "all-in" tab.

The sort of surcharge basis you will meet falls into a few shapes, and the prompt should tell the AI to capture the basis, not just the number:

• a flat amount per container or per shipment;
• a percentage of the base rate;
• a per-kilogram or per-tonne rate with a minimum;
• an amount that applies only between two dates or only in one direction.

An all-in tab then multiplies out the lot. Take a 40ft box with a base of 2,150 and a peak-season line of 300 per container: the all-in is 2,450. If the same sheet has a fuel line of 6 per cent of base, that line is 129 and the all-in becomes 2,579. Writing that as a formula (=base + SUMIFS(surcharge amounts per container) + base*SUMIFS(percentage lines)) means a changed surcharge updates every affected lane at once.

Worked example: one carrier file, 18 lines, two catches

This is an illustration with invented figures. A small forwarder receives a five-page carrier PDF covering one origin region. It holds 18 rate lines across 20ft and 40ft equipment, a notes block on page five with two surcharges, and a header saying "Valid 01/06/2027 to 31/08/2027".

1. Prep (3 minutes). The file is a text PDF. It is renamed with a year-month-day date and uploaded alone to a business-plan assistant, with the prompt above and the header row of the master template.
2. Extraction (about 2 minutes). The assistant returns 21 rows: 18 base lines plus three surcharge rows. It lists two rows as uncertain.
3. Uncertain rows (5 minutes). The first is a 40ft figure of 2,780 where the table cell is partly overprinted by a stamp; the reviewer opens page two and confirms 2,730. The second is a start date marked AMBIGUOUS, because the assistant read the two header dates separately: "01/06/2027" could be 1 June or 6 January. The end date on the same line, "31/08/2027", settles it, since 31 cannot be a month, so the order is day-month and the dates become 2027-06-01 to 2027-08-31.
4. Spot-check (10 minutes). All three surcharge rows are checked against the notes block, and 4 of the remaining 18 base lines are chosen at random. One of them reads 1,985 in the sheet and 1,895 in the PDF: two digits transposed.
5. Response to the error. Because a random line was wrong, the reviewer reads all 18 against the PDF. No further mistakes appear, and the transposition is logged in the notes column.

Total elapsed time is about 30 minutes, including the full re-read, for a file that would have taken most of an hour to retype, and two real errors were caught before they reached a quote. The same file extracted with no checking would have gone live with a wrong 40ft rate, a transposed base rate and a start date nobody had settled.

A spot-check routine you can write on a card

Checking has to be a fixed routine, or it gets skipped on busy weeks. This one takes about ten minutes per carrier file and does not depend on who is doing it.

1. Row count. Count the rate lines in the PDF (or the lines you expect) and compare with the rows in the sheet. A mismatch means a missed or duplicated line.
2. Every uncertain row the AI flagged, against the source page.
3. Every surcharge row, against the notes block or the email, including basis and dates.
4. Validity. Read the header date yourself and compare with the first and last rows' dates.
5. Random sample. Pick 15 to 20 per cent of the base lines, at least 10, using a random-number column so you do not unconsciously choose the easy ones. Compare each with the PDF.
6. Outliers. Sort by rate within each unit. A 20ft rate higher than the 40ft rate on the same lane, or one rate far outside its neighbours, is more often a unit or column error than a real price.
7. Escalation rule. One wrong figure in the random sample means checking the whole sheet. Two means you stop using that prompt on that carrier's layout until you have rewritten it.
8. Sign-off. Initials and date in the checked-by column.

The outlier check in step 6 is cheap and catches problems a line-by-line read tends to miss, because the eye follows each row on its own and never compares one against its neighbours.

Rates that arrive in emails

Email quotes are the messiest source and the one most often left out of the master sheet. A reply such as "Port A to Port B, 40HC, 2,300 all-in, valid till end of next month, subject to equipment" holds a rate, a unit, a validity and a condition in one sentence.

Paste the whole message, with sender and date, into the assistant using this adaptation of the prompt:

This is an email from a carrier or agent, dated [date of email]. Extract any rate quoted into the same columns as before. Work out "end of next month" from the email date and show your working in the notes. If a figure is described as "all-in", put it in base_rate and write ALL-IN in conditions. If the email says "same as last time", do not look anything up; write NEEDS-PREVIOUS and name the lane.

Two rules keep this safe. First, relative dates ("next Friday", "end of next month") are resolved against the date the email was sent, which is why the date goes into the prompt. Second, a phrase like "same as last time" is never resolved by the AI from memory; it is flagged for a person to look up in the history.

Groupage, project and road forwarders need different columns

A groupage forwarder, quoting per cubic metre or per 100 kg with minimum charges, will mostly extract weight-break tables. The prompt needs an extra column for the break (for example, up to 100 kg, 101 to 500 kg) and a check that each break's rate is lower than the one before it per kilogram. A project forwarder handling a few big shipments a year will extract less but needs the conditions column to carry far more text. A small road-freight forwarder buying from five hauliers will find the email route matters more than the PDFs, because most quotes arrive as messages.

A misread that looked fine until the invoice

One failure is worth describing because it passes every visual check. In an illustrative case, a carrier sheet printed 20ft and 40ft columns, and under each destination a third line headed "40HC". The extraction merged the third line into the 40ft column, so two different rates appeared for the same lane and unit, and the lookup in the quoting sheet picked the first one it found, which was the cheaper standard box.

The rows looked plausible. Nobody flagged them because each number was real and present in the PDF. The mistake showed up only when a quote went out for a high-cube box at the standard price and the carrier's invoice came back higher.

Two habits would have caught it. The first is a duplicate check: a pivot table or a COUNTIFS on carrier, origin, destination, unit, surcharge name and validity should return 1 for every row, and any 2 is a collision. The second is putting the exact equipment text as printed ("40HC") in a column next to the clean unit, so a reviewer sees the original label beside the normalised one.

Merging carriers into one master sheet

With each file checked, paste the rows into one master tab. A few small formulas keep it usable:

• =COUNTIFS(A:A,A2,B:B,B2,D:D,D2,G:G,G2,J:J,J2,M:M,M2) in a helper column (carrier, origin, destination, unit, surcharge name and valid-from, in the prompt's column order). Any result above 1 is a collision to check.
• A MINIFS on the all-in tab to show the cheapest valid rate per lane and unit, with an INDEX/MATCH to name the carrier. If your spreadsheet lacks MINIFS, a pivot table does the same.
• A data-validation list on the carrier, unit and currency columns, so a typing slip cannot create a new unit called "40 ft".

Once the master exists, the AI can do more with it than it could with a folder of PDFs. Ask it, for example, "which lanes have only one valid carrier rate?" or "which rates expire in the next 21 days?" and have it show the filter or formula it used, so you can verify the answer. For turning such rows into customer quotes, the sibling tutorial on drafting freight quotes from customer emails picks up where this one stops.

Keeping buy rates and sell rates apart

Resist the urge to add a margin column to the extracted rows. The carrier tab should be a faithful copy of what the carrier said, nothing more. Your sell-rate tab then pulls from it and applies your rules: a percentage on ocean freight, a fixed handling fee per shipment, a minimum margin per lane.

The separation pays off when a carrier re-issues a sheet. You replace the carrier rows, and every sell rate updates by formula. If margin had been typed into the carrier tab, you would be unpicking it row by row. Understanding where your margin actually lands on each lane is its own exercise, covered in margin by lane from a spreadsheet.

What it costs and how long it takes

For a small forwarder, the main cost is the person's time, not the tool. As of November 2026, ChatGPT Business and Claude Team both list a standard seat at $20 per user per month billed annually or $25 billed monthly, and both start at two seats, so the smallest team plan costs about $40 to $50 a month. Check the vendor's pricing page before you buy, because plans and seat rules change.

A rough budget per carrier file once the template and prompt are in place: 3 minutes to prepare, 2 to 5 minutes to extract, 5 to 10 for the uncertain rows and the surcharges, and 10 for the sample check. Call it 20 to 30 minutes against 60 to 90 for retyping, with the benefit that the checking is now written down. A forwarder who receives 12 carrier files a quarter spends perhaps five hours on rate maintenance instead of fifteen, and gets an audit trail as a by-product.

The first file takes longer, because you will adjust the prompt after seeing what that carrier's layout does to it. Keep a "layout notes" tab with one line per carrier ("page 5 notes block holds surcharges"; "validity printed in footer only") and paste the relevant line into the prompt next time.

Confidentiality and what to leave out

Contract rates are a commercial secret on both sides. Before uploading, remove anything the extraction does not need: customer names, your contract number, internal margin notes. Use a business or team plan, where the vendor states it does not train on business content by default, and read your carrier agreement for confidentiality wording. If a carrier's terms forbid sharing rate sheets with third parties, a cloud assistant may count as one, and that is a conversation to have with the account manager before, not after.

Keep the human in the loop on anything customer-facing too. An assistant that extracts well can still draft a quote that claims a rate is "guaranteed" when the carrier only said "subject to space". The conditions column exists so quote wording follows the carrier's own caveats.

Making the routine stick

Three habits turn a one-off exercise into something the office keeps doing. Put the prompt and the header row in a shared document called "Rate sheet routine" so nobody works from memory. Give one person the job of loading new files on the first working day of each week, so rates do not drift stale. And review the layout-notes tab each quarter, because carriers change their formats more often than their prices.

If you would like help setting this up around your real carrier files, that is the kind of job my AI implementation consultation covers. For the next piece of the chain, reading bills of lading with AI uses the same extract-flag-check approach on shipping documents.

Questions forwarders ask before trusting extracted rates

Can AI read a carrier rate PDF that is a scanned image?

Usually yes, but accuracy drops. Scanned pages with small print, stamps or skewed tables produce more misread digits. Run the scan through OCR first, ask the AI to flag any figure it is unsure of, and check every number on a scanned sheet, not a sample.

How many lines should I spot-check on each extraction?

Check every total and every line the AI marked uncertain, then a random 15 to 20 per cent of the rest, never fewer than 10 lines. If you find even one wrong figure in the random sample, check the whole sheet before it goes live.

Should the sheet hold buy rates or sell rates?

Keep them in separate tabs. The extracted carrier sheet holds buy rates only. Your margin rules turn them into sell rates in a second tab, so a change in a carrier rate flows through without anyone retyping a quote.

Is it safe to upload carrier contract rates to a chat assistant?

Contract rates are commercially sensitive. Use a business plan that does not train on your content, read the carrier agreement for confidentiality wording, and strip customer names from anything you upload. If in doubt, ask the carrier's account manager.

Further reads

• Negotiate Freight Rates: Prepare Counter-Offers With AI and Data — Take your rate history into the next carrier negotiation.
• Landed Cost Calculator in Excel: Test Tariff Changes With AI — Feed freight figures into a full landed-cost sum.
• Demurrage and Detention: AI Tracks Free Days and Drafts Disputes — Free-days terms often sit in the same carrier documents.
• Freight Forwarder Lead Generation: AI Finds Shippers, Drafts Emails — Fresh rates are easier to sell when you know who to call.
• AI for Freight Brokers: Triage Load Emails and Rate Confirmations — Triage the rate emails that arrive between sheets.
• AI Tools for Shipping Documents: Commercial Invoices and Packing Lists — Extraction habits applied to invoices and packing lists.
• How to Use AI in a Trucking Company: Load Paperwork Without Retyping — A step-by-step way to turn rate confirmation PDFs and broker emails into reviewed load records with ChatGPT or Claude and a two-tab sheet.
• AI for Freight Business Sustainability: Emissions Figures for Tenders — A haulier's tender sheet separates measured fuel, allocation, factor source and the emissions figure a customer requested.
• Carrier Packet Onboarding: AI Collects and Checks the Documents — A document list, a tracker with expiry dates, mismatch flags and reminder emails for onboarding carriers without chasing by hand.
• Freight Damage Claim: AI Builds the Evidence Pack and Letter — An evidence checklist, a liability-limit sum, a deadline tracker and a claim letter drafted with AI, with a person checking and filing each claim.
• Freight Invoice Audit: AI Checks Carrier Bills Against Quotes — Turn a carrier quote and invoice into two tables, let AI list every difference, sort the accessorial charges and send the dispute from a weekly routine.
• AI Agents for Logistics Companies: Which Tasks They Handle Today — Which logistics tasks an AI agent can take on today, which need a person to approve, and which to leave alone.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: carrier rate-sheet layouts described generically; assistant vendors' business-plan data terms as listed in the tools' own documentation. No carrier is named because layouts differ by carrier and change without notice.