Extracting Test Results From a Certificate of Analysis Table

A certificate of analysis is a table with a strange property: every row has the same columns and no two rows necessarily have the same data type. One row’s specification is an upper bound, the next is a two-sided range, the next is the word “Conforms”, and the next asks only that the result be reported. Any schema that types the spec column as a number is wrong before it starts.

The four columns and what each one is

Whatever the industry, the table has the same skeleton, sometimes with the columns in a different order and sometimes with the method folded into the parameter name:

• Parameter — the test. “Assay”, “Water content”, “Residual solvents: methanol”, “Appearance”, “Total aerobic microbial count”.
• Method — the procedure the result was produced by, usually a citation rather than a description: a USP general chapter in angle brackets, a European Pharmacopoeia section number, an ASTM or ISO designation, or an internal SOP number. This column is why two certificates for the same material can disagree without either being wrong.
• Specification — the acceptance criterion.
• Result — what was measured.

Some certificates add a fifth pass/fail column, which is derived from the other two and should be extracted anyway rather than recomputed and substituted — if the printed conclusion disagrees with your evaluation of spec against result, that disagreement is the single most valuable output the extraction can produce, and you cannot surface it if you overwrote one side.

Above the table sits a header block that is easy to under-extract: product name and grade, batch or lot number, manufacture date, retest or expiry date, quantity, and the signature block of the person releasing it. The lot number is the join key to everything else in your system, including the traveler that consumed the material.

The specification is a predicate

Read a real specification column and you will find at least six grammars in it, frequently on one page:

NMT 0.5 %          upper bound, inclusive        result <= 0.5
NLT 98.0 %         lower bound, inclusive        result >= 98.0
98.0 - 102.0 %     two-sided range               98.0 <= result <= 102.0
<= 10 ppm          upper bound, symbol form      result <= 10
Conforms           qualitative conformance       result in {conforms, complies, pass}
Report result      no criterion                  always satisfied
Absent / Negative  qualitative negative          result in {absent, negative, none detected}
White powder       descriptive match             human judgement

“NMT” and “NLT” — not more than, not less than — are pharmaceutical house style and appear far more often than the symbol forms. A regular expression that only handles <= and >= silently drops most of the table.

The useful move is to parse each specification into a small tagged union and keep the raw string beside it: { kind: "max", value: 0.5, unit: "%" }, { kind: "range", low: 98, high: 102, unit: "%" }, { kind: "qualitative", expected: "conforms" }, { kind: "report_only" }. Once specs are typed, the comparison against results is ordinary code that you can unit-test, which is a much better place for it than inside a prompt. Asking the model to evaluate pass or fail directly means the arithmetic happens somewhere you cannot inspect, and it will get a unit conversion wrong eventually.

Units are the trap inside the trap. The unit can be in the specification cell, in the result cell, in the parameter name, in the column header, or in a footnote — and it is not always the same unit on both sides of a row, so a spec in percent against a result in ppm is a real thing that appears on real certificates. Normalise to a canonical unit per parameter before comparing, refuse to compare when the units are incommensurable, and flag rather than guess.

Results that are not numbers

The result column is at least as heterogeneous as the specification column, and the non-numeric entries carry the most information:

• Censored values. “<0.05”, “ND (LOQ 0.01 %)”, “BQL”. These say the analyte was below the limit of detection or quantitation, which is not the same as zero and must not be coerced to it. Store them as { censored: "below", limit: 0.05 }. A downstream average over a column where half the values were silently turned into 0 is a fabricated number.
• Qualitative passes. “Complies”, “Conforms”, “Pass”, “Meets requirement”. Map to a boolean but keep the word, since some audit contexts want the certificate’s own wording.
• Descriptive results. “White to off-white crystalline powder” against a specification saying much the same thing. There is no arithmetic here; the honest output is both strings and a flag that this row was not machine-evaluated.
• Ranges as results. A particle size row may report d10, d50 and d90 in a single cell. That is three results in one row and it is a modelling decision, not an extraction error — either split into three rows or give the row a structured value.
• References to another document. “See attached microbiological report.” The value is a pointer and should be typed as one.

Layout failures specific to this table

Beyond the general problems that afflict any table in a scanned PDF, three failures are characteristic of certificates of analysis.

The grouped parameter. “Residual solvents” appears as a heading row with no specification and no result, followed by indented rows for methanol, ethanol and acetone. Flatten thoughtlessly and you get a row whose parameter is “methanol” with no indication that it belongs to a group, or worse, a spec value inherited from the wrong parent. Preserve the group as a field on each child row; the indentation that expresses it is horizontal position, so it is available if you kept coordinates.

The continuation page. Certificates run to two or three pages and the header row repeats. If your parser treats every page independently you get duplicated header rows in the data; if it concatenates pages before parsing you get a header row in the middle of the table. Detect the repeated header by content and drop it, and be alert for the version where the header does not repeat and page two is a bare continuation — those columns must be aligned by position against page one.

The merged cell. A specification that applies to three consecutive parameters is printed once in a vertically merged cell. Most extraction paths give that value to the first row and leave the other two empty. A rule that fills a blank specification from the row above is right far more often than it is wrong on this document, but it must be a recorded, reviewable rule rather than a silent one.

The row schema

{
  "lot": "L-260317-04",
  "product": "Sodium citrate dihydrate, USP",
  "released_on": "2026-03-22",
  "rows": [
    {
      "parameter": "Assay (dried basis)",
      "group": null,
      "method": "USP <541> titrimetric",
      "spec_raw": "99.0 - 100.5 %",
      "spec": { "kind": "range", "low": 99.0, "high": 100.5, "unit": "%" },
      "result_raw": "99.7 %",
      "result": { "kind": "numeric", "value": 99.7, "unit": "%" },
      "printed_conclusion": "Pass",
      "evaluated": "pass"
    },
    {
      "parameter": "Methanol",
      "group": "Residual solvents",
      "method": "USP <467>",
      "spec_raw": "NMT 3000 ppm",
      "spec": { "kind": "max", "value": 3000, "unit": "ppm" },
      "result_raw": "< 50 ppm",
      "result": { "kind": "censored", "direction": "below", "limit": 50, "unit": "ppm" },
      "printed_conclusion": "Pass",
      "evaluated": "pass"
    }
  ]
}

Every field appears twice, raw and parsed, and that redundancy is the point. The parsed form is what your rules run on; the raw form is what you show a reviewer and what you keep when the parse fails, which it will on the row nobody anticipated. If you are choosing between a provider’s strict structured output mode and a looser JSON mode for this, the difference between the two matters more here than usual: a tagged union is exactly the kind of schema whose enforcement varies between providers.