Command-Line Interface

To see all options available, run:

spoonbill --help

To flatten a file, run:

spoonbill filename.json

The inputs can be concatenated JSON or an OCDS release or record package.

Choose output formats

Spoonbill creates a result.xlsx file in the current path by default.

To change the name or location of the Excel file, use the --xlsx option. For example:

spoonbill --xlsx path/to/file.xlsx filename.json

To also write CSV files to an existing directory, use the --csv option. For example:

spoonbill --csv directory/ filename.json

To only write CSV files, set --xlsx "", for example:

spoonbill --csv directory/ --xlsx "" filename.json

Change column headings

The default headings are JSON paths, like /tender/procurementMethod.

To change the headings to human-readable English text, run:

spoonbill --human filename.json

To change the headings to human-readable Spanish text, run:

spoonbill --human --language es filename.json

Warning

Please note, -- language would not do any changes unless used in combination with -- human.

Combine related tables

During analysis, each array within the JSON file is assigned to a separate table. However, if an array always contains a small number of entries, it is often preferable to combine its corresponding table with its parent table.

For example, let’s say there are at most two identifiers per organization. Separated tables might look like this (omitting the ocid, id and parentTable columns for simplicity):

parties

rowID	parentID	/parties/name	/parties/id
ocds-213czf-1/tender/parties:1	ocds-213czf-1/tender	Acme Inc.	1

parties_ids

rowID	parentID	/parties/additionalIdentifiers/scheme	/parties/additionalIdentifiers/id
ocds-213czf-1/tender/additionalIdentifiers:254900UIZS15MTA7H075	ocds-213czf-1/tender/parties:1	XI-LEI	254900UIZS15MTA7H075
ocds-213czf-1/tender/additionalIdentifiers:example	ocds-213czf-1/tender/parties:1	XE-EXAMPLE	example

Instead of creating separate tables, the two tables can be combined. That way, you can read an organization’s identifiers without performing a lookup across the two tables.

rowID	parentID	/parties/name	/parties/id	/parties/additionalIdentifiers/0/scheme	/parties/additionalIdentifiers/0/id	/parties/additionalIdentifiers/1/scheme	/parties/additionalIdentifiers/1/id
ocds-213czf-1/tender/parties:1	ocds-213czf-1/tender	Acme Inc.	1	XI-LEI	254900UIZS15MTA7H075	XE-EXAMPLE	example

You will notice that the additionalIdentifiers columns now contain indexes – 0 and 1 – to group the columns for each identifier.

By default, tables are combined if the child table has less than 5 entries for any given row in the parent table. This threshold can be changed with the --threshold option. For example:

spoonbill --threshold 3 filename.json

To disable this feature, set the threshold to 1. For example:

spoonbill --threshold 1 filename.json

Storing objects that follow the same schema in the same table

OCDS JSON format is described using JSON Schema, and reuses the same schema in multiple locations. For example, an array of document objects is allowed under five different objects (Award, Contract, etc.).

spoobill would combine those five document locations into a single table, in cases where the user research indicates this preference.

Currently, spoonbill supports combining following object types:

documents amendments milestones

This behavior can be overitten, by invoking combine command. To combine only document arrays, and omit milestones, amendments, use:

spoonbill --combine documents filename.json

Select which data to output

Choose initial tables

By default, these initial tables are written:

parties planning

tenders awards

contracts

To change which initial tables are written, use the --selection option. For example:

spoonbill --selection parties,tenders filename.json

Exclude child tables

Child tables might be written for the initial tables (see Combine related tables).

To exclude child tables from being written, use the --exclude option. For example:

spoonbill --exclude parties_ids,tenders_items_class filename.json

Choose columns

OCDS data can contain hundreds of columns. If you only need a small number of columns, use the --only option. For example:

spoonbill --only /parties/name,/parties/id filename.json

Instead of writing a long list of columns on the command line, you can provide a file with one column per line, using the --only-file option. For example:

spoonbill --only-file columns.txt filename.json

Copy data between tables

Unnest columns from child tables into parent tables

To copy a few columns from a child table to a parent table, use the --unnest option. For example:

spoonbill --unnest /tender/items/0/id,/tender/items/0/description filename.json

Instead of writing a long list of columns on the command line, you can provide a file with one column per line, using the --unnest-file option. For example:

spoonbill --unnest-file columns.txt filename.json

Repeat columns from parent tables into child tables

To repeat a column from a parent table into a child table, use the --repeat option. For example:

spoonbill --repeat /parties/name,/parties/id filename.json

Instead of writing a long list of columns on the command line, you can provide a file with one column per line, using the --repeat-file option. For example:

spoonbill --repeat-file columns.txt filename.json

Add calculated values

Count the number of child rows

It can be helpful to know the number of related entries in a child table while viewing a parent row. To add columns with these numbers, use the --count option.

This will add, for example, a /tender/itemsCount column to the tenders table, with the number of entries in the /tender/items array that are related to each row.

spoobill --count filename.json

Advanced features

To flatten a file with a local schema instead of the default schema, run:

spoonbill --schema schema.json filename.json

To reuse a state file to flatten another file with the same characteristics, run:

spoonbill --state-file filename.json.state filename.json

Reference

spoonbill

CLI tool to flatten OCDS files

spoonbill [OPTIONS] FILENAME

Options

--schema <schema>

A JSON schema file URI. The URI can be a file path or an HTTP link. Spoonbill uses the schema to analyze the provided JSON file. Defaults to the OCDS 1.1.5 release schema (requires internet connection)

--selection <selection>

A comma-separated list of initial tables to write. The available tables to select are: parties, planning, tenders, awards, contracts

--threshold <threshold>

The maximum number of elements in an array before it is split into a table

Default:

5

--state-file <state_file>

A file path URI to a previously generated state file. If not provided, a new state file is generated

--xlsx <xlsx>

A file path to store the resulting xlsx file. Default to result.xlsx. Set to ‘’ to disable the xlsx file generation

--csv <csv>

An existing directory path. If set also generates CSV files in the given directory. Disabled by default

--combine <combine>

A comma-separated list of tables. Combines same OCDS object types from different locations (tender, awards, etc) into a single table. The available tables are: documents, milestones, and amendments

--exclude <exclude>

A comma-separated list of tables to exclude from export. Disabled by default

--unnest <unnest>

A comma-separated list of column names to copy from child tables into their parent table. Disabled by default

--unnest-file <unnest_file>

A file path directory. Same as –unnest, but read column names from a file with one column per line

--only <only>

A comma-separated list of a subset of columns to output instead of all, in JSON path format, e.g. /parties/name. Defaults to all the available columns

--only-file <only_file>

A file path directory. Same as –only, but read the columns names from a file with one column per line

--repeat <repeat>

A comma-separated list of columns to repeat from a parent table into its child tables, in JSON path format,e.g. /parties/name. Disabled by default

--repeat-file <repeat_file>

A file path directory. Same as –repeat, but read the columns names from a file with one column per line

--count

For each array field, add a count column to its parent table. Disabled by default

--human

Change the tables headings to human-readable format, using the schema’s title properties

--language <language>

Use with –human, the language to use for the human-readable headings

Default:

en

Options:

en | es

-v, --verbosity <LVL>

Either CRITICAL, ERROR, WARNING, INFO or DEBUG.

Arguments

FILENAME

Required argument