Working with Tables in Terminal

Updated Oct 28, 2026

Table operations including querying, validation, statistics, and schema management for tabular data files.

Available Commands

The fairspec table command provides utilities for working with tables:

describe - Get table statistics and summary information
query - Query tables using SQL syntax
validate - Validate table data against a Table Schema
infer-schema - Automatically infer Table Schema from table data
render-schema - Render Table Schema through the render plugins
validate-schema - Validate a Table Schema file
infer-dialect - Infer file dialect
script - Interactive SQL session with loaded table

Describe Tables

Get statistical summary information about a table:

# Describe a CSV file
fairspec table describe data.csv

# Describe a remote table
fairspec table describe https://example.com/data.csv

# Describe from a dataset
fairspec table describe --dataset dataset.json --resource sales

# Output as JSON
fairspec table describe data.csv --json

Output

Returns statistics for each column including:

count - Number of non-null values
null_count - Number of null values
mean - Average value (numeric columns)
std - Standard deviation (numeric columns)
min - Minimum value
25%, 50%, 75% - Quartiles
max - Maximum value

Options

--dataset <path> - Load table from dataset descriptor
--resource <name> - Specify resource name from dataset
--debug - Show debug information
--json - Output as JSON

Format Options

All standard format options are available (see Format Options section below).

Query Tables

Execute SQL queries on tables using Polars SQL engine:

# Basic query
fairspec table query data.csv "SELECT * FROM self WHERE age > 25"

# Aggregate data
fairspec table query sales.csv "SELECT region, SUM(amount) as total FROM self GROUP BY region"

# Filter and sort
fairspec table query users.csv "SELECT name, email FROM self WHERE active = true ORDER BY name"

# Query from dataset resource
fairspec table query --dataset dataset.json --resource users \
  "SELECT * FROM self WHERE created_at > '2024-01-01'"

SQL Syntax

Use self as the table name in queries
Supports SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, JOIN, etc.
Full Polars SQL syntax supported
Results are output as formatted tables

Options

--dataset <path> - Load table from dataset descriptor
--resource <name> - Specify resource name from dataset
--debug - Show debug information
--json - Output as JSON

Validate Tables

Validate table data against a Table Schema:

# Validate with explicit schema
fairspec table validate data.csv --schema schema.json

# Validate with inferred schema
fairspec table validate data.csv

# Validate from dataset (uses embedded schema)
fairspec table validate --dataset dataset.json --resource users

# Output validation report as JSON
fairspec table validate data.csv --schema schema.json --json

# Add named SQL checks every row, or the whole table, must satisfy
fairspec table validate data.csv --check 'end-after-start="end" >= start' --check "not-empty=count(*) > 0"

A check is name=condition, split at the first =. A row fails when its condition is false; null passes. A condition that reduces to one value, such as count(*) > 0, checks the whole table. Failures are reported as row/check or table/check errors, and a condition that cannot run as a check error.

Validation Report

Returns a validation report with:

valid - Boolean indicating if validation passed
errors - Array of validation errors (if any)

Example validation errors:

{
  "valid": false,
  "errors": [
    {
      "type": "cell/maximum",
      "columnName": "age",
      "rowNumber": 2,
      "cell": "200",
      "maximum": "150"
    },
    {
      "type": "cell/type",
      "columnName": "email",
      "rowNumber": 3,
      "cell": "not-an-email",
      "columnType": "email"
    }
  ]
}

Options

--schema <path> - Path to Table Schema file
--check <name=condition> - Named SQL condition to check (repeatable)
--dataset <path> - Load table from dataset descriptor
--resource <name> - Specify resource name from dataset
--debug - Show debug information
--json - Output as JSON

Infer Table Schema

Automatically generate a Table Schema from table data:

# Infer schema from local file
fairspec table infer-schema data.csv

# Infer from remote file
fairspec table infer-schema https://example.com/data.csv

# Save inferred schema to file
fairspec table infer-schema data.csv --json > schema.json

# Infer with custom options
fairspec table infer-schema data.csv --sample-rows 1000 --confidence 0.95

Schema Inference Options

--sample-rows <number> - Number of rows to sample for inference (default: 100)
--confidence <number> - Confidence threshold for type detection (0-1, default: 0.9)
--keep-strings - Keep original string types instead of inferring
--column-types <pairs> - Override types for specific columns (e.g., id:integer,name:string)
--column-properties <json> - Give column properties up front and infer the rest (e.g., {"id":{"type":"string"},"price":{"title":"Price"}}); a property without type keeps the inferred type and adds only title, description or rdfType
--comma-decimal - Treat comma as decimal separator
--month-first - Parse dates as month-first (MM/DD/YYYY)

Generated Schema

The inferred schema automatically detects:

Column types (string, integer, number, boolean) and string formats (date, date-time, local-date-time, email, etc.)
Nullable columns, typed as ["integer", "null"]
Missing value indicators such as NA, listed in missingValues

Example

Given this CSV data:

id,name,price,quantity,active,created_at
1,Product A,19.99,100,true,2024-01-15
2,Product B,29.99,50,false,2024-01-20
3,Product C,39.99,75,true,2024-02-01

Infer the schema:

fairspec table infer-schema products.csv --json

Generated schema:

{
  "properties": {
    "id": {
      "type": "integer"
    },
    "name": {
      "type": "string"
    },
    "price": {
      "type": "number"
    },
    "quantity": {
      "type": "integer"
    },
    "active": {
      "type": "boolean"
    },
    "created_at": {
      "type": "string",
      "format": "date"
    }
  }
}

Render Table Schema

render-schema takes the same arguments as in the other CLIs, --to-format and --to-path, and asks the render plugins for the format. No Rust plugin renders yet, so it prints nothing; render with the Python or TypeScript CLI.

Validate Table Schema

Validate that a Table Schema file is valid:

# Validate a schema file
fairspec table validate-schema schema.json

# Validate from remote source
fairspec table validate-schema https://example.com/schema.json

# Output as JSON
fairspec table validate-schema schema.json --json

Schema Validation

This validates that the schema itself is:

Valid JSON
Compliant with Table Schema specification
Has correct property definitions
Uses valid column types and constraints

Validation Report

{
  "valid": true,
  "errors": []
}

Or if invalid:

{
  "valid": false,
  "errors": [
    {
      "type": "metadata",
      "message": "\"x\" is not of type \"integer\"",
      "jsonPointer": "/properties/age/maximum"
    },
    {
      "type": "metadata",
      "message": "\"x\" is not of type \"number\"",
      "jsonPointer": "/properties/age/maximum"
    }
  ]
}

Options

--silent - Suppress output messages
--debug - Show debug information
--json - Output as JSON

Infer File Dialect

Automatically detect the dialect of a table file:

# Infer dialect from file
fairspec table infer-dialect data.csv

# Infer from remote file
fairspec table infer-dialect https://example.com/data.xlsx

# Output as JSON
fairspec table infer-dialect data.parquet --json

Detected Formats

The command can detect:

csv - Comma-separated values
tsv - Tab-separated values
json - JSON format
jsonl - JSON Lines (newline-delimited JSON)
xlsx - Excel spreadsheet
ods - OpenDocument Spreadsheet
parquet - Apache Parquet
arrow - Apache Arrow/Feather
sqlite - SQLite database

Example Output

{
  "format": "csv",
  "delimiter": ",",
  "lineTerminator": "\n",
  "headerRows": [1]
}

Interactive Scripting

Start an interactive session with a loaded table. Each line runs as SQL against the table, registered as self, and an empty line exits:

# Load table and start a session
fairspec table script data.csv

# Script table from dataset
fairspec table script --dataset dataset.json --resource users

The Python and TypeScript CLIs open a language REPL here; a compiled binary cannot, so the Rust session takes SQL only. The table is loaded as load_table returns it, so a CSV column without a schema is text: cast it, or use table query, which infers types first.

Example Session

⚠ Each line runs as SQL against `self`, the table; an empty line exits
> SELECT name, age FROM self LIMIT 2
shape: (2, 2)
...
> SELECT count(*) AS n FROM self WHERE CAST(age AS INT) > 25
shape: (1, 1)
...
>

Format Options

All table commands support these format options for loading data:

CSV/TSV Options

--format <name> - Format name (csv, tsv, etc.)
--delimiter <char> - Column delimiter (default: ,)
--line-terminator <chars> - Row terminator (default: \n)
--quote-char <char> - Quote character (default: ")
--null-sequence <string> - Null value indicator
--header-rows <numbers> - Header row indices (e.g., [1,2])
--header-join <char> - Character to join multi-row headers
--comment-rows <numbers> - Comment row indices to skip
--comment-prefix <char> - Comment line prefix (e.g., #)
--column-names <names> - Override column names (JSON array)

JSON Options

--json-pointer <pointer> - JSON pointer to data array (e.g., /data/users)
--row-type <type> - Row format: object or array

Excel/ODS Options

--sheet-number <number> - Sheet index (0-based)
--sheet-name <name> - Sheet name

SQLite Options

--table-name <name> - Table name in database

Table Schema Options

All table commands support these schema-related options:

Type Inference

--sample-rows <number> - Sample size for type inference
--confidence <number> - Confidence threshold (0-1)
--keep-strings - Don't infer types, keep as strings
--column-types <pairs> - Override types (e.g., age:integer)
--column-properties <json> - Override column properties (e.g., {"age":{"type":"integer","maximum":150}})

Value Parsing

--missing-values <values> - Missing value indicators (JSON array)
--decimal-char <char> - Decimal separator (default: .)
--group-char <char> - Thousands separator (default: ,)
--comma-decimal - Use comma as decimal (shorthand)
--true-values <values> - Custom true values (JSON array)
--false-values <values> - Custom false values (JSON array)

Date/Time Parsing

--datetime-format <format> - Datetime format string
--date-format <format> - Date format string
--time-format <format> - Time format string
--month-first - Parse dates as month-first

Array/List Parsing

--array-type <type> - Array item type
--list-delimiter <char> - List delimiter (default: ,)
--list-item-type <type> - List item type

Common Workflows

Explore Unknown Data

# 1. Infer the dialect
fairspec table infer-dialect unknown-data.txt

# 2. Get basic statistics
fairspec table describe unknown-data.txt

# 3. Infer the schema
fairspec table infer-schema unknown-data.txt --json > schema.json

# 4. Query the data
fairspec table query unknown-data.txt "SELECT * FROM self LIMIT 10"

Schema-Driven Validation

# 1. Create schema from sample data
fairspec table infer-schema sample.csv --json > schema.json

# 2. Validate the schema itself
fairspec table validate-schema schema.json

# 3. Validate production data
fairspec table validate production.csv --schema schema.json

Data Quality Checks

# Check for data quality issues
fairspec table validate data.csv --schema schema.json

# Get detailed statistics
fairspec table describe data.csv

# Query for specific issues
fairspec table query data.csv "SELECT * FROM self WHERE email NOT LIKE '%@%'"

# Find duplicates
fairspec table query data.csv "SELECT id, COUNT(*) as cnt FROM self GROUP BY id HAVING cnt > 1"

Interactive Analysis

# Start interactive session
fairspec table script data.csv

# Each line runs as SQL against `self`:
# - Explore: SELECT * FROM self LIMIT 10
# - Filter: SELECT * FROM self WHERE status = 'active'
# - Aggregate: SELECT category, SUM(CAST(amount AS DOUBLE)) FROM self GROUP BY category

Format Conversion

# Query and output as JSON
fairspec table query data.csv "SELECT * FROM self" --json > output.json

# Get statistics and save
fairspec table describe large-file.parquet --json > stats.json

Output Formats

Text Output (default)

Human-readable output with formatted tables:

fairspec table describe data.csv

Output:

shape: (9, 4)
┌────────────┬──────────┬───────────┬───────────┐
│ #          ┆ id       ┆ price     ┆ quantity  │
│ ---        ┆ ---      ┆ ---       ┆ ---       │
│ str        ┆ f64      ┆ f64       ┆ f64       │
╞════════════╪══════════╪═══════════╪═══════════╡
│ count      ┆ 4.0      ┆ 4.0       ┆ 4.0       │
│ null_count ┆ 0.0      ┆ 0.0       ┆ 0.0       │
│ mean       ┆ 2.5      ┆ 39.99     ┆ 69.0      │
│ std        ┆ 1.290994 ┆ 40.824829 ┆ 62.134263 │
│ min        ┆ 1.0      ┆ 9.99      ┆ 1.0       │
│ 25%        ┆ 2.0      ┆ 19.99     ┆ 50.0      │
│ 50%        ┆ 3.0      ┆ 29.99     ┆ 75.0      │
│ 75%        ┆ 3.0      ┆ 29.99     ┆ 75.0      │
│ max        ┆ 4.0      ┆ 99.99     ┆ 150.0     │
└────────────┴──────────┴───────────┴───────────┘

JSON Output

Machine-readable JSON for automation:

fairspec table describe data.csv --json

Examples

CSV Data Analysis

# Get overview of sales data
fairspec table describe sales.csv

# Find top customers
fairspec table query sales.csv \
  "SELECT customer, SUM(amount) as total FROM self GROUP BY customer ORDER BY total DESC LIMIT 10"

# Validate data quality
fairspec table validate sales.csv --schema sales-schema.json

Multi-Format Pipeline

# Load Excel data
fairspec table describe report.xlsx --sheet-name "Q1 Sales"

# Query specific sheet
fairspec table query report.xlsx --sheet-name "Q1 Sales" \
  "SELECT region, SUM(revenue) FROM self GROUP BY region"

# Validate against schema
fairspec table validate report.xlsx --sheet-name "Q1 Sales" --schema schema.json

Remote Data Validation

# Infer schema from remote data
fairspec table infer-schema https://api.example.com/export.csv --json > remote-schema.json

# Validate local data against remote schema
fairspec table validate local-data.csv --schema remote-schema.json

Database Export Validation

# Validate SQLite export
fairspec table validate export.db --table-name users --schema expected-schema.json

# Get statistics from database
fairspec table describe export.db --table-name users

# Query database table
fairspec table query export.db --table-name users \
  "SELECT status, COUNT(*) FROM self GROUP BY status"