User data — records you want to store in the database — must be in JSON format. But instead of writing JSON files, you can also add data from Excel or CSV spreadsheets. The framework converts them to JSON formatted process files and then inserts the records into the appropriate database table.

The pipeline has two steps:

  1. Translate — run translate_tabular_data to convert a spreadsheet to a JSON process file
  2. Insert — run the generated JSON process file to insert the records into the database

Both steps are handled by the notebook:

./project_example/import_data/load_utility_data.ipynb

Prerequisites

Before loading user data, both of the following must be complete:

  • Setup DB — the database must exist with the target schema and table
  • Setup processes — the translate_tabular_data and manage_territory processes must be registered in the database

The project_example

The repository includes a worked example in project_example/ that imports national territory data into the utility.territory table. The source data is a spreadsheet of ~200 countries with their ISO 3166-1 alpha-2 codes. This example is used throughout this section to illustrate each step.

Project file structure

project_example/
├── scheme_xspatula.json            # scheme file — project path + user credentials
└── import_data/
    └── utility/
        ├── excel/
        │   └── territory.xlsx      # source spreadsheet
        ├── process/
        │   └── territory.json      # process definition for translate step
        └── manage_process/
            └── manage_territory.json   # generated by translate step, used by insert step

Scheme file

The scheme file for the example project points to the project_example directory and uses an ini_cat_5_user login:

{
  "project_path": "../project_example",
  "user_project": {
    "user_name": "ini_cat_5_user",
    "password": "armchair-scouting-windowframe-bicycling"
  },
  "process": [
    {
      "execute": true,
      "verbose": 0,
      "overwrite": false,
      "delete": false
    }
  ]
}

Replace user_name and password with your own credentials. You can also use user_netrc_id to read credentials from a .netrc file.

Input files

File Purpose
scheme_xspatula.json Scheme file for the example project; defines the project path and ini_cat_5_user credentials

Updated: