Convert OCDS JSON to CSV and keep the relationships.

To convert OCDS JSON to CSV without losing lots, awards or suppliers, put repeating objects into separate tables and keep the identifiers that connect them. Then compare the output with the original JSON before you build a dashboard or import it into a database.

This tutorial converts a real Find a Tender release containing four lots, four awards, four contracts and 55 supplier references. The example uses Flatten Tool, produces 11 CSV tables, and includes a Python audit that verifies the counts and selected relationships. You can download the original package, converted files and code together.

Why a single CSV can change the answer

A procurement notice can contain several lots. An award can name several suppliers. The same supplier can appear under several awards. These are separate relationships, and a flat export needs a deliberate way to represent them.

Taking only the first supplier drops the others. Expanding lots and awards independently and joining only on the procurement identifier creates combinations the source never stated. Repeating an award or contract amount on every supplier row can inflate a total when someone sums the resulting column.

The OCDS serialization guidance describes separate tables for repeating objects. That is the approach here: retain a release table, child tables and the keys needed to link them. Keep the original package alongside the CSV files because those tables are a working representation of the source.

This page starts after you have obtained a release package. The Find a Tender API guide covers collection, pagination and source-update windows.

Start with one pinned UK notice

The example is Holiday Activities and Food (HAF) Programme 2026, notice 082826-2026, under procurement identifier ocds-h6vhtk-05f10f. Its package reports publication on September 1, 2026 at 16:24:22 +01:00. We retrieved the official release-package endpoint on September 8, 2026 and saved the response bytes.

The saved package contains one release. Its arrays include:

• Four lots, identified as Lot 1 through Lot 4.
• Four awards, each with an explicit relatedLots reference to one lot.
• 55 supplier references across those awards, representing 35 distinct supplier IDs.
• 36 party objects, including the buyer and supplier parties.
• Four contracts, each referring to an award through awardID.

The saved input is 64,188 bytes, with SHA-256:

31bc324e54305573845915e050556de7cd19e59b293e4852eb8b358130185803

Use the bundled source.json to reproduce this example. A later request to the same endpoint can return different package metadata or content, so an endpoint URL alone is not a pinned input. The original package's publisher, licence, extensions and publication metadata remain in the download.

These observations describe this saved notice. They are not a claim about current availability, actual spending, or all UK procurement releases.

Convert the JSON with Flatten Tool

The tested package is flattentool version 0.28.0, using Python 3.12. Its command is named flatten-tool; the Python package is named flattentool. Version 0.28.0 requires Python 3.10 or later.

Extract the example ZIP into a working folder and open a terminal there. Create a virtual environment:

python -m venv .venv

Activate it with source .venv/bin/activate on macOS or Linux, or .venv\Scripts\Activate.ps1 in Windows PowerShell. Then install the pinned dependency and run the checked example:

python -m pip install -r requirements.txt
python convert.py --input source.json --output-dir reproduced

Choose a new output directory. The wrapper rejects an existing directory so that old tables cannot be mistaken for files from the current conversion. It also rejects an input whose hash differs from the pinned example.

The underlying conversion follows the documented OCDS command, with CSV output selected explicitly:

flatten-tool flatten source.json --root-id=ocid --main-sheet-name=releases --root-list-path=releases --output-format=csv --output-name=raw-csv

--root-list-path=releases points to the package's release array. --root-id=ocid keeps the procurement identifier in the tables. --main-sheet-name=releases names the parent table. Selecting CSV avoids creating workbook files you do not need for a database import.

The wrapper then checks the CSVs, writes a relationship table, saves package metadata separately and records output hashes in verification.json. It performs no live collection after you have downloaded the example.

Read the tables and their keys

For this input and tool version, the six main files are:

Five additional tables retain award items, their additional classifications and delivery addresses, contract documents, and party classifications. The verification report lists all 11 files and their row counts.

Column names describe paths through the original structure. In awa_suppliers.csv, the important columns are:

ocid
id
awards/0/id
awards/0/suppliers/0/id
awards/0/suppliers/0/name

Here, the plain id column is the release ID. awards/0/id is the award ID. The 0 in a header is part of the tool's repeated-object path notation; it does not mean the file contains only the first award or supplier. The file has 55 supplier-reference rows.

Use (ocid, release_id) to identify a release in your working database. Add the relevant child ID for an award, lot, party or contract. In the raw CSVs, release_id is still named id; the derived relationship table renames it for clarity.

The OCDS identifier guidance distinguishes process identifiers from locally scoped IDs. When you retain several releases of the same procurement, the same award ID can recur as that award changes. Keep the release key in an observation table so those versions do not overwrite each other accidentally.

Check that the children and links survived

Opening a CSV successfully proves that it is readable. It does not establish that every supplier survived or that the joins still point to the right parents.

The supplied audit checks the following against the saved source:

1. Counts: all 11 output table counts match the corresponding source arrays.
2. Parent keys: every output row retains the expected OCID and release ID.
3. Entity identities: lot, award, party and contract IDs match the source, without duplicate IDs within this release's corresponding table.
4. Supplier references: award ID, supplier ID and supplier name match as a multiset, so both lost and extra references are detected.
5. Links: each award's lot reference, each supplier's party reference and each contract's award reference resolves in this package.
6. Selected values: lot and award statuses, contract gross amounts and currencies survive the conversion.

The checked result is four lots, four awards, 55 supplier references, 35 distinct supplier IDs and zero unresolved references within this package. The verification report records the exact check scope and output hashes.

These are targeted conversion checks. They do not validate the entire OCDS schema, prove that every extension field retained its JSON type, or establish that the source's financial values are additive. Preserve the original JSON and add checks for any fields your application relies on.

Join through the references the source publishes

This notice explicitly connects each award to a lot. Follow those references:

award supplier reference
  → award, using release key + award ID
  → lot, using award.relatedLots
  → supplier party, using supplier ID within the release

contract
  → award, using contract.awardID within the release

The derived award-supplier-lots.csv contains 55 rows with the following columns:

ocid, release_id, award_id, lot_id, supplier_id, supplier_name

There are 28 supplier references for Award 1, 16 for Award 2, six for Award 3 and five for Award 4. The bridge preserves those relationships. It does not reduce them to one row per supplier.

Joining four lots to four awards on OCID alone would instead create 16 lot–award combinations. That would contradict the four explicit relationships in this source. Array position is not an alternative key: the first award is not automatically the award for the first lot.

For another notice, relatedLots may contain several lot IDs or be absent. Build one relationship per explicit reference, or retain an unresolved relationship when none is supplied. The example wrapper deliberately stops if its assumption of one related lot per award no longer holds.

Keep measures at their original level. Joining this notice's four contract rows through awards to suppliers produces 55 contract–supplier rows. A contract amount copied onto every resulting row is repeated information, not another payment. The source uses the UK extension field value.amountGross here; do not silently rename it to a generic net award amount. For analysis beyond conversion, use the government contract awards Python guide.

Open the CSV files in Excel without changing identifiers

Import each file as a separate table using Excel's text/CSV import workflow. Set OCIDs, release IDs, lot IDs, award IDs and organization identifiers to Text. Inspect how the importer handles dates, blank cells, non-ASCII names and numeric amounts before loading.

Avoid treating a double-click import as verification. Spreadsheet applications can infer types, changing an identifier with leading zeroes or interpreting a date differently from your database. Retain the original CSV bytes and import the columns deliberately.

CSV also cannot preserve every distinction in JSON: an absent field, an explicit null, an empty string, a number and a numeric-looking identifier need an explicit mapping when you convert back. The source JSON and package metadata remain your reference for those decisions.

The example ships CSVs rather than a hand-edited workbook. Each file can become an Excel table, a Power BI input or a staging table in your database using the same identifiers.

Apply the workflow to another OCDS package

Use the standalone flatten-tool command with your own release-package file. The reproduction wrapper is intentionally restricted to this example, while the converter itself supports other inputs.

Before adapting the audit, inspect the new package's schema version and extensions, its number of releases, and which arrays and references it actually supplies. Output file names and columns can differ with the data, schema and tool options. A record package with a records array needs a different extraction strategy; do not point this release-package recipe at it unchanged.

For a larger collection, reconcile counts per release and per parent, not only across the entire directory. Two dropped suppliers in one award and two extra rows in another could leave the overall total unchanged. Preserve composite keys, record unresolved references, and keep a repeatable input manifest.

When a release references a party or lot absent from that release, do not manufacture the missing object. Decide whether the task requires collecting a broader record representation or retaining an explicit unresolved link. The correct choice depends on the source and the question your application needs to answer.

Move from a conversion to maintained tables

A one-off converter is useful for proving that your application can consume the data. Recurring delivery adds collection across sources, schema changes, source failures, history and update handling.

Try the example first. Then inspect WebTruffle's free tender tables and EU and UK award tables. Those products have their own documented schemas and coverage; their columns are not identical to the raw Flatten Tool output here.

Public CSV access is already free. The paid service concerns agreed source coverage, custom tables, validation, delivery and maintenance. Use the columns and checks your application needs to define that scope. The tender API comparison helps assess alternative providers against the same requirements.

Frequently asked questions