Sign in
x
☰

Upload large data as streams, and transform (trim, stack, normalize, clean) during upload

Updated at 2026-07-29

Protobi can reshape a data file while it uploads. You describe the reshaping once, as JSON on the data card, and every file you upload to that card is transformed on the way in.

This ranges from trivial cleanups — lowercase the column names, drop columns you don't want, keep only completed interviews — to splitting a wide survey export into a set of stacked tables, one per loop. It's the same mechanism throughout; only the configuration gets longer.

The mental model

A survey export usually arrives flat: one row per respondent, with repeated question blocks spread across hundreds of numbered columns. Q12_1, Q12_2, Q12_3 are the same question asked about three different brands.

Analysis usually wants it stacked: one row per respondent × brand, with a single Q12 column.

The transform's job is that rotation. You tell it which columns belong to which repeating block, and how deeply nested each block is. It produces one table per block.

Everything else — renaming, filtering, selecting columns, adding a row index — is the same machinery with less configuration.

Where the configuration lives

On the data card, under Properties, in a savtools object:

{
  "savtools": {
    "transform": {
      "normalize": {
        "...": "options go here"
      }
    }
  }
}

The nesting is deliberate: it mirrors the underlying command. savtools.transform.normalize means "run the normalize transform", and the innermost object holds that command's options. If a new command is added later, it appears as a new path at the same level — nothing else changes.

Two kinds of key live under savtools:

  • transform.normalize — what to do to the data
  • output, tables, enabled — where the results should land

Level 1 — simple column and row operations

The simplest configurations don't reshape anything. They clean up a file as it loads and write the result back to the same card.

Lowercase every column name:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": true
      }
    }
  }
}

output: "self" means "put the result back in this card" — there's only one output, and it replaces the card's data. Column names are lowercased and any character outside A-Z a-z 0-9 _ becomes _.

Keep only some columns:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "include": "^(respondent_id|q[0-9]+)"
      }
    }
  }
}

Drop some columns:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "exclude": "^(hid|tmp|debug)"
      }
    }
  }
}

include and exclude are regular expressions matched against column names. Use both together to take a broad set and then carve out exceptions.

Keep only some rows:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "filter": { "status": 3 }
      }
    }
  }
}

filter is a MongoDB-style query, so it handles more than equality:

"filter": { "status": 3, "duration": { "$gt": 120 } }

Add a row-index column:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "rowId": "rownum"
      }
    }
  }
}

This adds a sequential column, numbered from 1, as the first column of the output. It's the simplest way to give every row a stable handle when the source file has no respondent key.

Take the first N rows — useful for a quick structural test before committing to a full load:

"limit": 500

These options combine freely. A realistic first-pass cleanup:

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": true,
        "exclude": "^(hid|tmp)",
        "filter": { "status": 3 },
        "rowId": "rownum"
      }
    }
  }
}

Level 2 — collapsing repeated columns into arrays

Multi-select questions often arrive as one column per option: Q11_1, Q11_2, Q11_3. Rather than splitting these into their own table, you can collapse each set into a single array-valued column.

{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": false,
        "ignoreCase": true,
        "loops": {
          "respondent": {
            "dimension": 0,
            "passthrough": true,
            "patterns": {
              "Q11": "Q11_[0-9]+",
              "Q12": "Q12_[0-9]+"
            },
            "transforms": {
              "Q11": "array",
              "Q12": "array"
            }
          }
        }
      }
    }
  }
}

Three new ideas here:

  • loops names the repeating blocks. dimension: 0 means respondent level — one row out per row in, no rotation.
  • passthrough: true carries every other column through untouched, so you keep the rest of the interview alongside the collapsed columns.
  • transforms: {"Q11": "array"} turns the matched set into one array column instead of many.

patterns maps an output column name to a regular expression matching the source columns that feed it. Array transforms only apply at dimension: 0.

ignoreCase: true makes pattern matching case-insensitive — worth setting whenever the source uses different capitalization than your patterns.

Level 3 — rotating case data into a loop

Now the real work. A physician reports on several patients; a shopper rates several retailers. The source has one row per respondent, with each case's answers suffixed by its number.

{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "ignoreCase": true,
        "loops": {
          "case": {
            "dimension": 1,
            "id": "respondent_id",
            "rename": "renumber",
            "dimensions": ["case_id"],
            "passthrough": true,
            "weak": ["screener_flag"],
            "patterns": {
              "A":   "^A([0-9]+)_([0-9]+)$",
              "B1":  "^B1_([0-9]+)$",
              "B2":  "^B2_([0-9]+)c[0-9]+$"
            }
          }
        }
      }
    }
  }
}

What each part does:

  • dimension: 1 — one level of nesting below the respondent. Each respondent produces as many rows as they have cases.
  • id — the respondent-level identifier carried onto every output row, so you can join back.
  • dimensions: ["case_id"] — names the index column holding which case a row represents.
  • rename: "renumber" — keeps the original column name with the captured index replaced by 1, so A3_2 becomes A3_1. The default, "key", uses the pattern's key as the output name instead.
  • weak — columns copied onto each row but not counted as data when deciding whether the row exists at all. Use it for hidden or administrative fields that would otherwise keep empty cases alive.
  • table: "data_{loop}" — names the output card; {loop} becomes the loop name, so this produces data_case.

Note there's no output: "self" here. Once a transform produces named tables, each one becomes its own card and the upload card stays as the source.

Level 4 — splitting into several tables

Most real surveys have more than one level of nesting: the respondent, then each brand they rated, then each attribute they rated it on. Each level becomes its own loop, at its own depth.

{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "ignoreCase": true,
        "loops": [
          {
            "name": "respondent",
            "dimension": 0,
            "retain": ["respondent_id"],
            "include": [".*"],
            "exclude": ["^q1[0-9]r[0-9]+c[0-9]+$"]
          },
          {
            "name": "brand",
            "dimension": 1,
            "retain": ["respondent_id"],
            "dimensions": ["brand_id"],
            "patterns": {
              "q10": "^q10r([0-9]+)$",
              "q11": "^q11r([0-9]+)$"
            }
          },
          {
            "name": "attribute",
            "dimension": 2,
            "retain": ["respondent_id"],
            "dimensions": ["brand_id", "attribute_id"],
            "patterns": {
              "q12": "^q12_([0-9]+)_([0-9]+)$"
            }
          }
        ]
      }
    }
  }
}

This yields three cards: data_respondent (one row per respondent), data_brand (respondent × brand), and data_attribute (respondent × brand × attribute).

Points worth noting:

  • loops can be a list or an object. As a list, each entry carries its own name. As an object, the key is the name. Use whichever reads better; lists are easier when order matters to you.
  • dimension is the nesting depth, and dimensions names the index columns at each level. At dimension: 2 you name two: the outer index and the inner one. Each pattern needs a capture group per level, in the same order.
  • retain copies respondent-level columns onto every row of a deeper loop, so each table can stand alone. It accepts a single name or a list.
  • The respondent loop usually needs an exclude for the columns the deeper loops consume, otherwise every brand column also shows up, unstacked, in the respondent table. include: [".*"] with a list of exclusions is the usual idiom. At loop level, include/exclude take lists of patterns rather than a single expression.
  • omitted is a labelled form of exclude — a map of name to pattern, appended to the loop's exclusions. Use it when you want the config to record why something is dropped.

Level 5 — files that need a companion metadata file

Some formats can't be read on their own. Fixed-width hdata needs its .spx spec; some CSV exports come with a Triple-S .sss file describing the variables.

Protobi handles this with a second card. Upload the metadata file to its own document card, then name that card in the transform.

Step 1. Create a document card named def.spx and upload the spec to it.

Step 2. Create the data card and reference it:

{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "spec": "def.spx",
        "ignoreCase": true,
        "loops": [
          { "name": "respondent", "dimension": 0, "retain": ["respondent_id"],
            "patterns": { "q2": "Q2", "q6": "Q6" } },
          { "name": "brand", "dimension": 1, "retain": ["respondent_id"],
            "dimensions": ["brand_id"],
            "patterns": { "q8": "Q8_([0-9]+)" } }
        ]
      }
    }
  }
}

Step 3. Upload the data file to the data card.

Three companion options are available, and all three name a card, not a file path:

Option For
spec SPX spec, and other metadata formats, e.g. paired with hdata
sss Triple-S metadata, e.g. paired with a CSV export
mapping Source mapping files

Upload the metadata file first. The transform looks the card up by name and needs a file already on it.

Once a spec is configured, the data file's own extension stops mattering — the spec determines how the data is read.

Parameter reference

Output routing

Directly under savtools:

Option Default Effect
output "tables" "tables" creates one card per output. "self" writes a single output back into this card.
tables — Per-output overrides, keyed by loop name.
reuseCurrentTable false Alias for output: "self".
enabled true Set false to disable the transform without deleting it.

An output also lands on the upload card if its name matches that card's key — so naming a loop after the source card updates it in place. Only one output may target the card.

Per-output overrides in tables:

Field Effect
key Card key to write to, instead of the name from table
name Display name
mode Storage mode, e.g. csv or sql
type Card type; defaults to data
loadMode Load behaviour, e.g. replace
useStreaming Whether this output imports via streaming
properties Properties to set on the output card
self true routes this output into the upload card

Transform options

Inside transform.normalize:

Option Default Effect
table — Output card naming pattern; {loop} becomes the loop name
loops — The repeating blocks to extract; list or object
patterns — Column patterns, for a single implicit loop
lowercasekeys false Lowercase and sanitize all column names
ignoreCase false Case-insensitive pattern matching
include — Regex of columns to keep
exclude — Regex of columns to drop
filter — MongoDB-style row filter
limit — Maximum rows to read
rowId — Add a sequential index column with this name
columns — Explicit list of columns
spec / sss / mapping — Companion metadata card
encoding — Override file encoding, e.g. windows1252
format csv Output format
verbose false Detailed progress in the server log
failOnEmpty true Fail if any loop matches no columns

Loop options

Inside each entry of loops:

Option Effect
name Loop name; substituted into table. Implied when loops is an object.
dimension Nesting depth: 0 respondent, 1 one level down, 2 two levels down
patterns Map of output column name to a regex with one capture group per level
dimensions Names for the index columns, outermost first
retain Respondent-level columns copied onto every row; name or list
passthrough Carry all other columns through
transforms Per-column transform, e.g. {"Q11": "array"}; arrays require dimension: 0
weak Columns copied but not counted when deciding whether a row exists
rename "key" (default) names output by pattern key; "renumber" keeps the source name with indexes reset to 1
id Respondent-level identifier carried onto every row
include / exclude Lists of patterns to keep or drop for this loop
omitted Labelled exclusions: a map of name to pattern

Troubleshooting

savtools transform produced no rows for loop(s): … Those loops' patterns matched nothing. Usually a capitalization mismatch — try ignoreCase: true — or a pattern that doesn't match the real column names. If a loop legitimately has no data this wave, set failOnEmpty: false to skip it and import the rest.

card "def.spx" has no uploaded file yet The companion card exists but is empty. Upload the metadata file to it first.

references card "def.spx", but no card with that filename or key exists Nothing matches that name. Check the spelling, and that the card is in the same project.

output:"self" expects a single transform output; got N output: "self" only works with one output. Use the default output: "tables" for multiple loops.

Brand columns appear in the respondent table, unstacked The respondent loop needs to exclude the columns the deeper loops consume. Add them to its exclude or omitted list.

A deeper loop produces rows for cases that don't exist Some administrative column is keeping empty rows alive. Add it to that loop's weak list.

Notes

  • Outputs are all checked before any is imported, so a bad configuration can't leave you with a half-loaded set of tables.
  • Large files take time. Several million output rows across a handful of loops can run twenty minutes or more. The progress panel names each table as it imports.
  • Set verbose: true while developing a configuration. The server log then shows which columns each loop matched and a running record count.
  • Build up in stages. Start with limit: 500 and one loop, confirm the shape is right, then add loops and remove the limit.