Data Transform

Convert CSV, TSV, JSON, NDJSON and YAML (input) into any of CSV, TSV, JSON, NDJSON, SQL, YAML, Markdown tables and TOML (output) with two bundled Python scripts. XML, TOML and Markdown are not accepted as input. Handles encoding detection, type inference, null normalization, nested-object flattening, array explosion, and large-file streaming. Only the standard library is needed, except that reading YAML requires pip install pyyaml.

Quick Start

# Basic conversion
python scripts/transform.py data.csv --to json
python scripts/transform.py data.json --to sql --table users
python scripts/transform.py data.csv --to markdown

# Flatten nested JSON before converting to CSV
python scripts/transform.py nested.json --to csv --flatten

# Explode an array field (one row per element)
python scripts/transform.py orders.json --to csv --explode items

# Understand your data first
python scripts/infer_schema.py data.csv --output summary

# Stream a large file without loading it all into memory
python scripts/transform.py big.csv --to ndjson --stream

# Pipe from stdin
cat records.ndjson | python scripts/transform.py --from ndjson --to csv

Format Support Matrix

Key Options: transform.py

Key Options: infer_schema.py

Recommended Workflow for Unknown Data

Run these three commands in sequence before committing to a conversion:

# 1. Inspect what you have
python scripts/infer_schema.py myfile.csv --output summary

# 2. Preview the conversion
python scripts/transform.py myfile.csv --to markdown | head -20

# 3. Run the real conversion
python scripts/transform.py myfile.csv --to json --pretty -o output.json

Handling Common Edge Cases

Encoding problems — The script auto-detects UTF-8 and Latin-1. For Windows files, pass --encoding cp1252. For UTF-8 with BOM, the auto-detector handles it.

Semicolon-delimited CSV (common in European Excel exports) — pass --delimiter ";".

Custom null values — extend the default list: --null-values "null,NULL,N/A,#N/A,-,unknown".

Numbers stored as strings — type inference is on by default; pass --no-infer to keep everything as string (useful before a SQL COPY load where the schema is already defined).

Nested JSON to SQL — chain --flatten with --to sql to produce a flat table with double-underscore column names (address__city, address__zip).

One row per order item — use --explode items where items is the array field name.

Files > 100 MB — use --stream with NDJSON/CSV input and NDJSON/CSV output. Convert big JSON arrays to NDJSON first, because JSON input is parsed as a whole.

PostgreSQL COPY format — use --dialect postgres with --to csv then import with \COPY tablename FROM 'file.csv' CSV HEADER;.

Type Inference Rules

When --no-infer is not set, string values are parsed in this order:

1. NULL — value is in the --null-values list or is an empty string → None
2. Boolean — case-insensitive true/false/yes/no/on/off → True/False
3. Integer — plain ASCII digits, optional sign, no leading zeros (007 and ZIP codes like 01234 stay strings, as does 1_000) → int
4. Float — decimal or scientific notation (41.5, 1e5, .5) → float. nan and inf stay strings, because they are not valid JSON numbers
5. String — everything else

SQL output promotes types per column: if a column has mixed int and float values, the whole column becomes REAL/DOUBLE.

Additional Resources

Reference Files

• references/format-specs.md — Per-format edge cases: CSV quirks, JSON null handling, SQL dialect differences, YAML boolean gotchas, encoding notes.
• references/transform-patterns.md — Ready-to-run recipes: normalizing API responses, log analysis, database seed data, pivot-style reshaping, joining two CSVs, batch processing a directory of files.

Scripts

• scripts/transform.py — Main conversion engine. Run with --help for full usage.
• scripts/infer_schema.py — Schema inference. Outputs JSON Schema draft-07, SQL DDL, or a human-readable column summary with type, nullability, and sample values.