Creating Templates

So far, all the examples you've seen have been about unflattening - taking a spreadsheet and producing a JSON document.

If you already have a JSON schema, Flatten Tool can automatically create a template spreadsheet with the correct headers that you can start filling in.

Flatten Tool's sub-command for this is flatten-tool create-template.

Generating a spreadsheet template from a JSON Schema

Here's an example command that uses a schema from the cafe example under Sheet shapes and generates a spreadsheet:

$ flatten-tool create-template --use-titles --main-sheet-name=cafe --schema=examples/receipt/cafe.schema -f csv -o examples/create-template/simple/actual

The example uses --use-titles so that the generated spreadsheet has human readable titles and --main-sheet-name=cafe so that the generated spreadsheet have cafe as their first tab and not the default, main.

If you don't specify -o, Flatten Tool will choose a spreadsheet called template in the current working directory.

If you don't specify a format with -f, Flatten Tool will create a template.xlsx file and a set of CSV files under template/.

The schema is the same as the one used in the user guide and looks like this:

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "definitions": {
        "TableObject": {
            "type": "object",
            "properties": {
                "id": {
                    "type": "string",
                    "title": "Identifier"
                },
                "number": {
                    "type": "integer",
                    "title": "Number"
                },
                "dish": {
                    "items": {
                        "$ref": "#/definitions/DishObject"
                    },
                    "type": "array",
                    "title": "Dish"
                }
            }
        },
        "DishObject": {
            "type": "object",
            "properties": {
                "id": {
                    "type": "string",
                    "title": "Identifier"
                },
                "name": {
                    "type": "string",
                    "title": "Name"
                },
                "cost": {
                    "type": "number",
                    "title": "Cost"
                }
            }
        }
    },
    "type": "object",
    "properties": {
        "id": {
            "type": "string",
            "title": "Identifier"
        },
        "name": {
            "type": "string",
            "title": "Name"
        },
        "address": {
            "type": "string",
            "title": "Address"
        },
        "table": {
            "items": {
                "$ref": "#/definitions/TableObject"
            },
            "type": "array",
            "title": "Table"
        }
    }
}

If you run the example above, Flatten Tool will generate the following CSV files for you:

sheet: cafe.csv
Identifier Name Address
sheet: table.csv
Identifier Table:Identifier Table:Number
sheet: tab_dish.csv
Identifier Table:Identifier Table:Dish:Identifier Table:Dish:Name Table:Dish:Cost

As you can see, by default Flatten Tool puts each item with an Identifier in its own sheet.

Rolling up

If you have a JSON schema where objects are modeled as lists of objects but actually represent one to one relationships, you can roll up certain properties.

This means taking the values and rather than having them as a separate sheet, have the values listed on the main sheet.

To enable roll up behaviour you have to:

  • Use the --rollup flag
  • Add the rollUp key to the JSON Schema to the child object with a value that is an array of the fields to roll up

Here are the changes we make to the schema:

--- ../examples/receipt/cafe.schema
+++ ../examples/receipt/cafe-rollup.schema
@@ -58,7 +58,8 @@
                 "$ref": "#/definitions/TableObject"
             },
             "type": "array",
-            "title": "Table"
+            "title": "Table",
+            "rollUp": ["number"]
         }
     }
 }

Here's the command we run:

$ flatten-tool create-template --use-titles --main-sheet-name=cafe --schema=examples/receipt/cafe-rollup.schema --rollup -f csv -o examples/create-template/rollup/actual

Here are the resulting sheets:

sheet: cafe.csv
Identifier Name Address Table:Number
sheet: table.csv
Identifier Table:Identifier Table:Number
sheet: tab_dish.csv
Identifier Table:Identifier Table:Dish:Identifier Table:Dish:Name Table:Dish:Cost

Notice how Table: Number has now been moved into the cafe.csv file.

Caution

If you try to roll up multiple values you'll get a warning like this:

UserWarning: More than one value supplied for "table". Could not provide rollup, so adding a warning to the relevant cell(s) in the spreadsheet.
  warn('More than one value supplied for "{}". Could not provide rollup, so adding a warning to the relevant cell(s) in the spreadsheet.'.format(parent_name+key))

Empty objects

If you have a JSON schema where an object's only property is an array represented by another sheet, Flatten Tool will generate an empty sheet for the object so that you can still add columns at a later date.

All create-template options

$ flatten-tool create-template -h
usage: flatten-tool create-template [-h] -s SCHEMA [-f OUTPUT_FORMAT]
                                    [-m MAIN_SHEET_NAME] [-o OUTPUT_NAME]
                                    [--rollup] [-r ROOT_ID] [--use-titles]

optional arguments:
  -h, --help            show this help message and exit
  -s SCHEMA, --schema SCHEMA
                        Path to the schema file you want to use to create the
                        template
  -f OUTPUT_FORMAT, --output-format OUTPUT_FORMAT
                        Type of template you want to create. Defaults to all
                        available options
  -m MAIN_SHEET_NAME, --main-sheet-name MAIN_SHEET_NAME
                        The name of the main sheet, as seen in the first tab
                        of the spreadsheet for example. Defaults to main
  -o OUTPUT_NAME, --output-name OUTPUT_NAME
                        Name of the outputted file. Will have an extension
                        appended if format is all.
  --rollup              "Roll up" columns from subsheets into the main sheet
                        if they are specified in a rollUp attribute in the
                        schema.
  -r ROOT_ID, --root-id ROOT_ID
                        Root ID of the data format, e.g. ocid for OCDS
  --use-titles          Convert titles. Requires a schema to be specified.