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:
| Identifier | Name | Address |
| Identifier | Table:Identifier | Table:Number |
| 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
--rollupflag - Add the
rollUpkey 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:
| Identifier | Name | Address | Table:Number |
| Identifier | Table:Identifier | Table:Number |
| 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]
[--line-terminator LINE_TERMINATOR]
[--convert-wkt]
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).
--line-terminator LINE_TERMINATOR
The line terminator to use when writing CSV files:
CRLF or LF
--convert-wkt Enable conversion of WKT to geojson