tableschema-to-template

Given a Frictionless Table Schema (like this), generate an Excel template with input validation (like this).

This is a proof of concept.

Usage

pip install tableschema-to-template
wget https://raw.githubusercontent.com/hubmapconsortium/tableschema-to-template/main/tests/fixtures/schema.yaml
ts2xl.py \
  --input_schema schema.yaml \
  --output_dir /tmp
# Open with Excel:
open /tmp/template.xlsx

Development

From a checkout of the repo, run a demo:

pip install -r requirements.txt
tableschema_to_template/ts2xl.py \
  --input_schema tests/fixtures/schema.yaml \
  --output_dir /tmp
# Open with Excel:
open /tmp/template.xlsx

Run the tests:

pip install -r requirements-dev.txt
./test.sh

To build and publish,

• If you haven't already, generate a token on Pypi and create a .pypirc in your checkout.
• Increment the version number in setup.py.
• Finally: ./publish.sh

Next steps

• How, and whether, to validate regex patterns. Possibilities:
  • Don't even try!
  • Add VBA to provide regex support in Excel.
  • Target Google Sheets, which provides a regex function out of the box.
  • Or target OpenOffice, where regexes can be turned on in the settings.

Related work

From the Frictionless community:

• table-schema-resource-template: Generates templates, but doesn't go beyond row headers.
• data-curator: Desktop application for data entry based on Table Schema.
• csv-gg: Web app which serves data entry form, and uses Validata API for validation.

For the biomedical ontologies community:

• CEDAR: Data entry tool based on ontologies.
• Webulous: Google sheets plugin that adds pulldowns based on ontology terms.