Extracting Structured Data From a Material Test Report (Mill Certificate)

A material test report is the steel’s birth certificate. It has two tables with completely different column structures, a scattering of standards citations, and one field — the heat number — that is the only reason the document is attached to any particular piece of metal.

The heat number is the key

A heat is one melt. Everything poured from it shares a chemistry, and the mill assigns that melt an identifier which is then stencilled, stamped or tagged onto the product. The certificate reports test results for the heat, so the heat number is what joins the paper to the bar, plate or coil in front of somebody. On many certificates it appears in the header and as the leftmost column of both tables, and on a multi-heat certificate the tables have several rows with different heat numbers under one header — which is the first structural decision your extraction has to get right.

So the output is not a flat object. It is a document header plus a list keyed by heat, with each heat carrying its own chemistry row, its own mechanical row and often its own item or lot numbers linking it to specific line items on the purchase order. Extracting one chemistry and one strength from a certificate that covers four heats produces data that looks fine and is wrong for three quarters of the material.

Alongside the heat number sit the identifiers that make the certificate findable: mill name and works, certificate number, order and item number, product description and dimensions, the specification the material is claimed against (an ASTM designation with its year, an EN grade, a customer specification), and quantity or weight. The specification citation carries a year for a reason and the year is part of the value: an ASTM designation without its edition is incomplete, because the acceptance limits it points at are edition specific.

The chemical composition table

The chemistry table is the one that breaks generic table extraction, because its column headers are element symbols and element symbols are one or two characters that look like noise. A typical header row reads C Mn P S Si Cu Ni Cr Mo V Cb Ti N B, sometimes with the multiplier folded in as C x100 or with a footnote saying values are in hundredths of a percent.

Specific failures to expect and design against:

• Symbol confusion. Cb is columbium, the older North American name for niobium, and appears on US certificates where European ones write Nb. It is easy for a model to read Cb as Cr or C, and the resulting record has chromium where it should have niobium. Constrain the header vocabulary to a fixed enum of element symbols plus known aliases rather than accepting free text.
• Implicit scaling. A carbon value printed as 0.18 is 0.18 weight percent; the same value printed as 18 under a header marked x100 is the same thing. Nitrogen and boron are frequently in parts per million while everything else is in percent. Record the unit per column, taken from the header or the footnote, not assumed.
• Blank versus zero. An empty cell means the element was not determined. A cell reading <0.005 means it was determined and found below the reporting limit. A cell reading 0.000 means something a metallurgist would question. These are three different values and coercing them all to zero destroys the distinction — the same censored-value problem the certificate of analysis page deals with in its result column.
• Two chemistry rows per heat. Some certificates report both a ladle (heat) analysis and a product (check) analysis. They differ legitimately, standards permit a wider tolerance on the product analysis, and a schema with one chemistry per heat silently keeps whichever came second.

The mechanical property table

The mechanical table reports yield strength, tensile strength, elongation and often reduction of area, hardness, and impact energy from Charpy V-notch testing at a stated temperature. Its own characteristic problems are units and gauge lengths.

Strengths are printed in megapascals, in newtons per square millimetre — which is the same unit under another name — or in thousands of pounds per square inch, and many certificates print two of these side by side. Elongation is a percentage over a gauge length that must travel with it: an elongation of 22% in 2 inches and 22% in 8 inches are different material properties, and a schema with a bare elongation_percent field loses the qualifier that makes it comparable. Store { value, unit, gauge_length, orientation }, where orientation is longitudinal or transverse and matters for plate.

Impact results come as a set of three specimen values plus an average, at a test temperature that is part of the result. Extract the individual values, because acceptance criteria are usually written as a minimum average with a lower minimum for any single specimen, and you cannot evaluate that from the average alone.

Two values you can check by arithmetic

Most extracted fields can only be validated by re-reading the document. A mill certificate offers two that can be validated by computation — the general shape being a rule that checks one field against several others — and they are worth building because they catch misassigned columns rather than mistyped digits.

The first is the carbon equivalent. Many certificates print a CE value alongside the chemistry, and the most widely used definition is the International Institute of Welding formula:

CE = C + Mn/6 + (Cr + Mo + V)/5 + (Ni + Cu)/15

// synthetic heat: C 0.18, Mn 0.90, Cr 0.10, Mo 0.02, V 0.00, Ni 0.09, Cu 0.22
// 0.18 + 0.150 + 0.024 + 0.0207 = 0.3747  ->  printed as 0.37

function checkCE(chem, printedCE, tolerance = 0.02) {
  const g = (el) => chem[el]?.value ?? 0;   // absent element contributes zero
  const ce =
    g("C") + g("Mn") / 6 +
    (g("Cr") + g("Mo") + g("V")) / 5 +
    (g("Ni") + g("Cu")) / 15;
  return { computed: Number(ce.toFixed(4)), printed: printedCE,
           agrees: Math.abs(ce - printedCE) <= tolerance };
}

If the computed value and the printed value disagree by more than rounding, the most likely explanation is not that the mill is wrong. It is that a column was misassigned during extraction — nickel read into the chromium field, or a scaled column taken at face value. The tolerance has to allow for the mill printing to two decimal places and for the certificate possibly using a different formula, since alternate carbon-equivalent definitions exist for low-carbon steels and a certificate that uses one usually names it. Treat a disagreement as a review trigger, not as a rejection, and record which formula you applied.

The second check is the dual-unit one. Where a certificate prints both MPa and ksi for the same strength, they must be consistent: one ksi is approximately 6.895 MPa, so a yield printed as 350 MPa should appear as roughly 50.8 ksi. Disagreement here almost always means the two numbers were read from adjacent rows rather than from one row, which is the single most common table-alignment failure on a certificate whose second page shifted.

Certificate type, and what it asserts

European material certificates carry a type designation from EN 10204, and the type is a field worth extracting because it changes what the document is evidence of. A type 2.2 test report contains results from non-specific inspection, meaning tests on material that is not necessarily the material delivered. A type 3.1 inspection certificate reports results of specific inspection on the delivered product, validated by the manufacturer’s inspection representative independent of the manufacturing department. A type 3.2 certificate is validated additionally by the purchaser’s representative or an inspector designated by regulation.

A purchase order that requires 3.1 and receives 2.2 has a non-conformance regardless of how good the numbers look, and that is a check your extraction can support by simply capturing the designation — it is printed as a short token like EN 10204 3.1, usually near the certificate title or the signature block, and it is easy to miss because it looks like boilerplate.

The last field people forget is the signature and the statement above it. Mill certificates carry a declaration that the material conforms to the stated specification, signed by a named quality representative. For a receiving inspection workflow the presence of that signature is a gate in its own right, and like the signature blocks on a chain of custody form, it is better answered as a presence question about a region than as a transcription task.