How do you turn measurement CSV files into Cpk automatically? We replaced our Excel routine with an open-source tool

The Cpk formula is not difficult. Getting the instrument's output into a state where you can compute it is. Our automated vision measurement system produces a CSV at the press of a button, sometimes several hundred readings at a time. What the customer wants is a process capability report. For years, the step in between was this: open Excel, copy, paste, drag the formula down.

The cost is not only time. It is a process with no record, no version history, redone by hand every time — give the same raw data to a different person and you may get a different answer.

What finally pushed us to act was discovering that someone had pasted --- (the instrument's marker for "this dimension was not measured") into a spreadsheet as 0 and computed a mean from it. The Cpk on that report was wrong, and wrong in a very convincing way — the number looked entirely normal, and nothing anywhere would have told you otherwise.

Our ERP is .NET, but this tool deliberately uses Python — and no pandas, no numpy, no third-party packages at all, standard library only.

The reason is practical: the tool has to run on the PC in the QC room. Shop-floor machines often cannot install arbitrary software, may not have outbound internet, and nobody is going to debug a dependency tree to produce one report. "Copy the folder over and it runs" is the only installation method that reliably works in this environment.

The structure is split four ways, because the parts that change and the parts that do not are different things: reader handles file format (may need adjusting per machine), spec handles dimension definitions (different per part number), stats is pure statistics (never changes), and report handles output. The statistics layer touches no files and no I/O, so it can be fully unit-tested.

cpk_report/
  reader.py   # encoding detection, data-row detection, value parsing
  spec.py     # dimension spec (which column, what tolerance)
  stats.py    # Cp / Cpk / Pp / Ppk / Ca — pure functions, fully testable
  report.py   # plain text + self-contained HTML

The first version opened the file as UTF-8 and hit UnicodeDecodeError immediately. Measurement software in Taiwan generally exports Big5 / cp950.

But the dangerous case is not that error — it is the cases that do not raise one. When Big5 is misread as another encoding, nothing stops; the program quietly turns the Chinese columns into garbage and keeps going. So the final approach tries candidate encodings in order and prints the one it actually used on the report:

DEFAULT_ENCODINGS = ("utf-8-sig", "big5", "cp950", "utf-8", "latin-1")

latin-1 sits last as a backstop — it never fails on any byte sequence. That means the tool never stops dead on an encoding it has not seen. Better that someone sees odd characters on a report and passes --encoding, than that a scheduled job fails silently overnight.

The first few lines of the export are machine headers, so "skip the first 4 lines" is the natural instinct. Real files are less obliging: the header row count varies with machine settings, blank lines appear mid-file, and there is a footer note at the end. Any fixed-row-number approach breaks on some future file.

So the check became "does this row look like a data row" — enough columns, and a timestamp field that actually looks like a timestamp:

def looks_like_data_row(parts, min_columns, timestamp_column, timestamp_prefix):
    if len(parts) < min_columns:
        return False
    cell = parts[timestamp_column].strip()
    return len(cell) > 8 and cell.startswith(timestamp_prefix)

It deliberately does not check whether the numeric fields parse — because "verdict NG, value ---" is a perfectly legitimate row, and filtering it out would understate the reject rate.

This is the reason the tool exists, and the one thing Excel cannot do for you.

Measurement software writes --- when a dimension was not captured. Paste that into Excel and it becomes 0. And a zero does two things at once: it pulls the mean off-centre and it inflates the standard deviation. The resulting Cpk is a plausible-looking wrong answer.

The tool excludes these values from the statistics — and reports the excluded count as its own column:

NOT_MEASURED = {"", "-", "--", "---", "----", "n/a", "na", "null", "none"}

def parse_number(cell):
    text = cell.strip()
    if text.lower() in NOT_MEASURED:
        return None      # the key decision: None, not 0.0
    ...

That "excluded" column exists for exactly one reason: so the person reading the report knows how many of these readings were real. Two missing out of sixty is a different situation from sixty complete readings — but if the report does not say so, nobody finds out.

If all sixty readings are identical, the standard deviation is zero and Cpk is mathematically infinite.

On the shop floor this almost always means the gauge resolution is insufficient, not that the process is perfect. So the tool prints — rather than an impressive number. It is the same principle throughout: the report's job is to reflect reality, not to produce a good-looking result.

The same thinking drives the output format — Cpk and Ppk are always shown together. Close together means a stable process. Cpk clearly above Ppk means the machine itself is capable but there is drift between batches (tool wear, material changes, shift changes), and what needs attention is process control rather than machine accuracy. A single number cannot tell you which of these you are looking at.

The tool never modifies, moves or deletes the source CSV. There is no write path in the code at all.

That sounds obvious, but it is worth stating why. Our own ERP contains a routine that deletes processed rows from a CSV and uses "rows remaining" as the progress marker. In that context it was a deliberate trade-off — manual and automatic import needed to share one source of truth for progress. The cost is that if the delete fails, the original data is gone.

An analysis tool has no reason to carry that risk. A test enforces the rule: after reading, the source file's bytes must be identical to what they were before.

The exit codes are deliberately split three ways: 0 normal, 1 a characteristic missed the threshold or was out of specification, 2 execution error. "Quality below target" and "the program broke" are different problems, and separating them tells you whether to call the process engineer or the developer.

$ cpk-report data.csv --spec spec.json

Item        n  excl        mean     σ within    Cp    Cpk    Ppk   OOS  verdict
--------------------------------------------------------------------------------
Length     60     0     42.4993      0.0117   1.42   1.40   1.38     0  Pass
Thickness  60     0      0.6004      0.0043   1.55   1.52   1.48     0  Pass
Flatness   58     2      0.0072      0.0046      —   0.94   1.06     0  Inadequate

The tool contains no commercial secrets. It does not know what parts we make or who our customers are — dimensions and tolerances come entirely from the user's own configuration file. It solves a problem every precision machining shop has.

For us, open-sourcing it means putting an internal tool in the open: the code, the tests, and the reasoning behind each of the pitfalls above are all written down, so anyone — including customers auditing us — can judge for themselves whether the logic holds up. That is worth considerably more than a slide claiming "digitalised quality control".

If you work with measurement data, take it and adapt it. If you hit a different pitfall, open an issue.

Source: github.com/jet113102/cpk-report (MIT licence, Python 3.8+, no dependencies)