Historical Transcription to CSV and JSON

Moving from historical transcription to CSV — or to JSON — is a modelling decision before it is a file-format one. This piece sets out a method that keeps the faithful transcription as the master record and derives analytical tables from it, so the evidence behind every value stays recoverable. It is written for researchers who will have to defend their numbers in print.

Converting a historical transcription into CSV or JSON is a modelling problem before it is a technical one. The reliable method is to keep the faithful transcription (and, where you use it, TEI XML) as the master record, then derive analytical tables from it through documented extraction — never to type your readings straight into a spreadsheet. Decide your observational unit first, keep the verbatim source string alongside every normalised value, use CSV for flat units and JSON for ragged parent–child records, and treat every normalisation, date conversion, and authority match as a documented editorial claim rather than a cleanup step.

That is the whole argument. The rest works through how to do it without destroying the evidence you spent months reading.

Why the spreadsheet is the wrong first move

The temptation, once a body of wills or notarial acts or parish entries is finally readable, is to open a spreadsheet and start typing: name, date, place, sum. It feels like progress. It is also the point at which most projects quietly lose the ability to answer the question a reviewer will eventually ask — what did the document actually say?

Three things go wrong at once. The verbatim reading disappears, replaced by a tidied version of itself. The editorial decision that produced the tidied version goes unrecorded. And the spreadsheet itself begins coercing values — dates, identifiers, leading zeros — silently. One well-documented illustration of that last hazard comes from genomics rather than history: Ziemann and colleagues found that gene name errors caused by default spreadsheet settings appear in a substantial share of published papers with Excel gene lists — 19.6% and 13.8% in their two measured groups. That is an adjacent-domain warning, not a measurement of anything in historical research, and it should not be cited as one. But the mechanism is identical: software applying a default type conversion to a value it does not understand, without telling you.

The alternative is a layered pipeline. A faithful transcription is the source-oriented layer, retained and citable. Structured CSV or JSON is a derived analytical layer, regenerable from the master if your model changes — and it will change. The TEI Consortium's XSLT workshop demonstrates exactly this pull-processing approach: reusable templates that extract data from TEI into simple TSV/CSV tables, so the table is an output of the encoding rather than a replacement for it. If you are not encoding in TEI, the same discipline applies with plain-text transcriptions plus a documented extraction script.

This layering is a specific instance of the broader principle that governs the whole archive-photo-to-citable-source research workflow: each stage constrains the next, and fidelity lost early cannot be recovered later.

Step one: declare the observational unit

Before choosing a format, decide what one row represents. This is the decision everything else inherits.

Wickham's tidy data formulation gives three rules: one variable per column, one observation per row, and — the rule historians most often skip — one type of observational unit per table. A corpus of wills does not have a single observational unit. It has at least three: the will (one row per document), the person named (one row per named individual), and the bequest (one row per legacy). Flatten those into one file and you get repeated testator fields across every bequest row, an unstable row count, and no clean way to count either wills or legatees.

So: a `wills` table keyed by document ID, a `persons` table keyed by person ID with a foreign key back to the will, a `bequests` table with keys to both. Linked tables are not more complicated than the giant denormalised file. They are the same information, made countable.

Write down the units and their keys in a data dictionary before you extract anything. Fields, types, permitted values, and what a null actually means in each column (not recorded? illegible? absent from this record type?). This document is the cheapest insurance in the whole pipeline.

The stable-key rule

Every derived row needs a key that points back to a specific spot in a specific image: document identifier, folio or page, line or entry number. Without it, verification becomes a search problem. With it, any anomalous value in your analysis is one click from the manuscript that produced it. Archives-facing projects will recognise this as the same logic that underpins archival metadata standards for transcribed documents — description layered on the record, anchored to it.

Step two: choose CSV or JSON on the shape of the record

The choice is not about which format is "better." It is about whether your unit is rectangular.

CSV is defined by RFC 4180: records on separate lines, the same number of fields on every line, fields containing commas, quotes, or line breaks enclosed in double quotes. The W3C's model for tabular data generalises this and — importantly for scholarly work — adds a separate metadata file that can describe tables, columns, cells, units, nulls, and annotations. UTF-8 throughout, without exception. If your observational unit is genuinely flat (one baptism, one land transaction, one meteorological observation), CSV plus a CSVW metadata descriptor is the right answer, and it will open in every tool your collaborators use.

JSON (RFC 8259) offers four primitive types and two structured ones: unordered name/value objects and ordered arrays, each of which can be the value inside another. That nesting is the point. A household with an arbitrary number of members, a will with N bequests, a notarial act with a variable cast of parties — these are ragged records, and JSON represents them without you having to invent `child_1` through `child_12` columns and guess at the maximum. RFC 8259 also requires UTF-8 for interoperable exchange.

Two corrections worth stating plainly. JSON is a syntax, not a schema and not a database — JSON Schema supplies the validation layer, and JSON-LD adds identifier and linked-data semantics if you need them. And the ragged case does not force JSON: long child tables with stable parent IDs handle the same structure in CSV perfectly well, and are easier to hand to a colleague who works in R.

No general benchmark settles CSV versus JSON for historical records. Decide on downstream software, query patterns, validation needs, and the cost of carrying provenance.

Step three: normalise beside the source, never over it

This is where source integrity either survives or does not.

Normalisation — mapping a source value to a controlled representation — is an analytical act. It is not permission to erase the reading. The pattern that works is five columns rather than one: the verbatim string as transcribed, the normalised value, the authority identifier if there is one, the method or rule applied, and a confidence indication. `Wm. Ffoster` stays in the file. `William Foster` sits next to it. A reviewer can see both, and so can you in eighteen months when you have forgotten why you made the call.

The TEI Guidelines encode this expectation formally: the header's `<normalization>` element records the extent and principles of regularisation carried out in converting a source to electronic form. Whether or not you use TEI, adopt the requirement.

Dates deserve their own policy

ISO 8601 gives you unambiguous calendar dates and nothing else. Historical dates are frequently not unambiguous calendar dates. The Library of Congress's Extended Date/Time Format supplies the vocabulary the sources actually need: `1984?` for uncertain, `1984~` for approximate, `1984%` for both, `201X` and `2004-XX` for unspecified digits, `[1667,1668,1670..1672]` for a set of candidates, `1964/2008` for an interval, `1985-04-12/..` for an open end.

What EDTF does not do is decide anything for you. Old Style and New Style, dual dating, regnal years, local calendars — each requires an explicit project policy, written down, applied consistently, and stated in your documentation. Keep the source's own date expression in a verbatim column regardless. A regnal-year conversion is a claim, and claims need their evidence attached.

Step four: reconciliation is candidate generation, not truth

Once names and places are in columns, the pull toward automated matching is strong. Treat its output as a queue for review.

OpenRefine draws the distinction cleanly. Clustering finds groups of values that may be alternative representations of the same thing — but it works only at the syntactic level, on the character composition of the cell, which is useful for typos and inconsistencies and insufficient for semantic identity. Reconciliation matches your dataset against an external source and is explicitly semi-automated: the documentation states that human judgment is required to review and approve results, and that string-level matching means typos and whitespace affect outcomes.

The authorities themselves are not interchangeable, and none of them was built for your parish. VIAF aggregates name authority files. GeoNames offers over eleven million downloadable placenames. The Getty Thesaurus of Geographic Names is a structured geographic vocabulary. The World Historical Gazetteer links historical place records across time and language — and works from the principle that a place may carry multiple names and locations, with no record treated as ultimately authoritative. For occupations, HISCO and the HISCLASS social-class scheme provide coding frameworks, in full 12-class or condensed 7-class form. Record which authority you matched against and at what confidence. A match is a claim.

Automated named-entity recognition sits in the same category. The historical NER literature documents archaic language, inconsistent spelling, limited punctuation, digitisation noise, diachronic language change, and scarce expert-annotated training data as standing obstacles; recent work applying large language models to historical NER reports reasonable zero- and few-shot performance while noting it falls short of fully supervised models trained on domain annotations. Useful for proposing spans. Not a substitute for reading. If entity extraction is central to your project, the mechanics are worth working through in detail — see the practical guide to named entity extraction from historical documents.

When the record resists tabulation: the factoid model

Some corpora refuse to become a person table. A source says something about a person; another source contradicts it; a third mentions someone who may or may not be the same individual. Forcing that into one row per person requires you to decide the identity question before you have done the analysis.

The factoid model, developed at King's College London, inverts the order. A factoid is "a spot in a source that says something about a person or persons" — a source-centred assertion that acts as a structural nexus connecting sources, persons and person-assemblages, places, possessions, and classification schemes. Interpretation is assembled from factoids afterwards, and remains revisable. PASE built a relational structure linking Source, Person, Office, and Date, recording information with as little interpretation as reasonably possible so that researchers could draw their own conclusions or follow references back to the sources. PBW extended the model with hierarchical narrative units linked into the factoid structure.

Note what the KCL documentation is candid about: the projects extended the base approach differently according to period, sources, and research interest, and some did not use the factoid model at all. There is no mandatory schema waiting for you. There are well-tested patterns. The same trade-offs run through the fuller treatment of building prosopographical person data from archival records.

Scale is achievable with this discipline. NAPP's linked datasets cover nearly 600,000 people at two points in time — 180,000 in the US, 400,000 in Norway, across seven US year-pairs involving 1880. The Clergy of the Church of England Database holds career details for over 130,000 clerics from more than 50 archives, while stating plainly that parish registers and wills were not routinely incorporated, so precise birth and death dates are often absent. That kind of declared gap is what a good data dictionary looks like.

Where the transcription layer sits in all of this

Everything above assumes a transcription faithful enough to extract from. Structuring inherits transcription quality completely: a misread surname becomes a false person record, a misread digit becomes a data point, and neither announces itself once it is sitting in a well-formed CSV cell. A malformed file you can see. A plausible wrong value in a valid file you cannot.

This is the specific reason a base transcription should preserve what is on the page rather than smooth it. Leo's ATR-1 model is built on that commitment: archaic orthography, strikethroughs, marginal additions, expansions marked as expansions, tables kept as tables — the raw material a normalisation column needs in order to have something to normalise from. It reads Latin-script material, whatever language the page is written in, so English wills, French notarial registers, Dutch parish books and German legal papers sit in the same workflow; non-Latin scripts are out of scope. Transcription and translation stay separate operations, which matters here: the language of analysis should never quietly overwrite the language of the record.

For extraction specifically, the practical detail is the export path and the layering. Finished transcriptions export to TEI XML, Word, HTML, or PDF, so a TEI-plus-XSLT pipeline runs from Leo's output directly. And Transformations — Extract named entities, Classify, Generate glossary — write to a separate tab rather than modifying the base transcription, which is the same architectural principle argued for here at the file level: derived layers on top, source underneath, both retained. What that separation does not do is decide your observational units, your date policy, or your authority files. Those remain yours.

On accuracy figures, be sceptical of everyone including us: on a randomised 97-image sample of early-modern English manuscripts from the Folger Shakespeare Library at ATR-1's release, Leo recorded roughly 5% character error rate against Transkribus/Text Titan I at ~13%, Claude Opus ~23.3%, Gemini 2.5 Pro ~24.8%, and GPT-4.1 ~56.7% — the full comparison is published here. One corpus, one language, one moment. Your hands are not that sample, and a sensible next step before committing a pipeline is testing a tool on your own manuscripts — then verifying names, numbers, and dates against the image, since those are the tokens your CSV will treat as facts.

Publishing the dataset

The final layer is documentation. FAIR practice — Findability, Accessibility, Interoperability, Reusability — translates concretely into: machine-readable metadata, a README with the data dictionary and codebook, recorded provenance for each transformation step, versioning, an explicit licence, and deposit in a repository such as Dataverse or Zenodo with a persistent identifier.

Include the extraction scripts. A dataset that can be regenerated from its master transcriptions is a dataset a reader can check, and one you can rebuild when your model of the corpus changes.

The habit worth keeping

At every step, ask what you are throwing away and whether you could get it back. A column that holds only a cleaned value has discarded evidence. A date that has lost its regnal-year original has lost the ability to be re-converted under a better policy. A person record that has absorbed an identity judgment cannot be un-judged. Structure is how a corpus becomes countable — but the count is only defensible while the path back to the page stays open, one row at a time.

Frequently Asked Questions

How do I convert a historical transcription to CSV without losing the original reading?

Keep the faithful transcription as the master record and derive the CSV from it through a documented extraction step, rather than typing readings straight into a spreadsheet. Decide first what one row represents — one will, one person, one bequest — and give every row a stable key pointing back to document, folio, and line. Store the verbatim source string in its own column beside any normalised value, plus the authority identifier, the rule applied, and a confidence indication. Written that way, the CSV is regenerable when your model of the corpus changes, and every value stays traceable to the image that produced it.

Should I use CSV or JSON for structured historical data?

Choose on the shape of the record, not on which format seems more capable. CSV suits genuinely flat observational units — one baptism, one land transaction, one meteorological observation — and pairs well with a CSVW metadata descriptor that documents columns, units, and nulls. JSON suits ragged parent–child records: a household with any number of members, a will with N bequests, a notarial act with a variable cast of parties. But ragged data does not force JSON; long child tables with stable parent IDs handle the same structure in CSV and are easier to pass to a collaborator working in R. Use UTF-8 either way.

How should I handle uncertain or non-standard historical dates in a dataset?

Use the Library of Congress Extended Date/Time Format and keep the source's own date expression in a verbatim column. EDTF supplies the notation historical sources actually need: a trailing question mark for uncertain, a tilde for approximate, X for unspecified digits, bracketed sets for candidate years, and slash notation for intervals and open ends. What it will not do is decide anything for you. Old Style and New Style, dual dating, regnal years, and local calendars each need an explicit project policy, written down and applied consistently. A regnal-year conversion is an editorial claim, so the evidence for it should stay attached.

Can I trust automated tools to match historical names and places to authority files?

Treat automated matching as candidate generation, not truth. OpenRefine's clustering works at the syntactic level, on the character composition of cells, which catches typos and inconsistencies but cannot establish semantic identity. Its reconciliation function is explicitly semi-automated: human judgment is required to review and approve results, and typos or whitespace affect outcomes. The authorities differ in scope too — name authority aggregation, gazetteers of millions of placenames, structured geographic vocabularies, and historical gazetteers that accept multiple names and locations for a single place. Record which authority you matched against and at what confidence, because a match is a claim.

What is the factoid model, and when should I use it instead of a person table?

The factoid model, developed at King's College London, records a spot in a source that says something about a person or persons, and treats that assertion as the structural nexus linking sources, persons, places, possessions, and classification schemes. Use it when your corpus resists one row per person — when sources contradict each other, or when identity itself is the research question rather than a precondition. Interpretation is assembled from factoids afterwards and remains revisable. There is no mandatory schema: the KCL projects extended the approach differently by period and sources, and some did not use it at all.