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 has cafe as its 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, creating column headings for them 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:

--- /home/docs/checkouts/readthedocs.org/user_builds/flatten-tool/checkouts/stable/examples/receipt/cafe.schema
+++ /home/docs/checkouts/readthedocs.org/user_builds/flatten-tool/checkouts/stable/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 now appears in both the Cafe.csv and Table.csv files.

rollup can also be used to rollup actual data with flatten, with or without a schema: Flattening

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.

Disable local refs

You can pass a --disable-local-refs flag for a special mode that will disable local refs in JSON Schema files.

$ flatten-tool create-template --disable-local-refs --schema=examples/create-template/refs-disable-local-refs/main.json -f csv -o examples/create-template/refs-disable-local-refs/actual

You may want to do this if running the command against JSON Schema files you don’t trust.

Deprecated Fields

Fields which are deprecated can be marked in the JSON schema by setting the “deprecated” key to a value.

{
    "$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"
        },
        "formalname": {
            "type": "string",
            "title": "Formal Name",
            "deprecated": {
                "description": "We found people prefer to be addressed by their nick names",
                "deprecatedVersion": "v17"
            }
        },
        "address": {
            "type": "string",
            "title": "Address"
        },
        "table": {
            "items": {
                "$ref": "#/definitions/TableObject"
            },
            "type": "array",
            "title": "Table"
        },
        "coats": {
            "deprecated": true,
            "items": {
                "type": "object",
                "properties": {
                    "description": {
                        "type": "string",
                        "title": "Description"
                    }
                }
            },
            "type": "array",
            "title": "Coats"
        }
    }
}

You can then choose whether deprecated fields appear in the output by passing the --no-deprecated-fields option. This is off by default.

$ flatten-tool create-template --use-titles --main-sheet-name=cafe --schema=examples/create-template/deprecated.schema -f csv -o examples/create-template/deprecated-yes/actual
$ flatten-tool create-template --no-deprecated-fields --use-titles --main-sheet-name=cafe --schema=examples/create-template/deprecated.schema -f csv -o examples/create-template/deprecated-no/actual

All create-template options

$ flatten-tool create-template -h
usage: flatten-tool create-template [-h] -s SCHEMA [-f {csv,ods,xlsx,all}]
                                    [-m MAIN_SHEET_NAME] [-o OUTPUT_NAME]
                                    [--rollup] [-r ROOT_ID] [--use-titles]
                                    [--disable-local-refs]
                                    [--no-deprecated-fields]
                                    [--truncation-length TRUNCATION_LENGTH]

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 {csv,ods,xlsx,all}, --output-format {csv,ods,xlsx,all}
                        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.
  --disable-local-refs  Disable local refs when parsing JSON Schema.
  --no-deprecated-fields
                        Exclude Fields marked as deprecated in the JSON
                        Schema.
  --truncation-length TRUNCATION_LENGTH
                        The length of components of sub-sheet names (default
                        3).