CSV
Sampling Random Rows From a Huge CSV Without Loading It
Take a random sample of a 1.3 GB CSV with shuf, awk, DuckDB or a seek: all run in seconds, but line-based samples break rows that contain a newline.
6 min read
Tag
how-to
590 posts
XML
Converting JSON to XML With Sensible Element Names
JSON keys can be any string and XML names cannot. Make legal names from spaces and leading digits, write arrays as repeated elements, and mark null.
7 min read
YAML
Generating Table Schemas From YAML Definitions Safely
Generate CREATE TABLE statements from a YAML definition, quote identifiers so order and group work, and run the same DDL on PostgreSQL, SQLite and DuckDB.
6 min read
Excel
Joining Two Sheets on Two Keys in Excel and in SQL
Join two sheets on two keys in Excel with XLOOKUP, SUMIFS or Power Query, and in SQL with a two-column join, handling duplicates, NULLs and missing matches.
6 min read
CSV
Counting Lines in a Multi Gigabyte CSV Quickly and Correctly
wc -l counts a 1.3 GB CSV in under a second and is 9,856 rows off, because quoted newlines add lines. DuckDB counts records in 1 s; csv.reader takes 15 s.
6 min read
JSON
Generating a JSON Schema From Sample Data and Fixing It
Draft a JSON Schema from sample documents with genson, see what it infers and what it misses, and turn the draft into a contract with DuckDB as a cross-check.
7 min read
Excel
Finding the Second Highest Value in Excel and in SQL
Find the second highest value in Excel with LARGE and MAXIFS and in SQL with OFFSET, DENSE_RANK and subqueries, and decide how ties and NULLs should count.
6 min read
CSV
Reading a Large CSV in Chunks With pandas Without Surprises
Read a 20-million-row CSV in chunks with pandas: 150 MB peak instead of 3.9 GB, the same answer as DuckDB, and the traps: zip as int64, mixed types, pyarrow.
9 min read
CSV
Splitting a Large CSV Into Smaller Files Without Losing Rows
split -l cut a 1.3 GB CSV into 21 parts in 3.6 s with the header in one. awk, qsv, Miller and DuckDB repeat it; a cut inside a quoted newline breaks a part.
10 min read
YAML
Defining Data Contracts in YAML That Fail the Build
Write a data contract as a YAML file, check a table against it with DuckDB, and fail the job on nulls, duplicates, bad values and drift, with tested output.
7 min read
ExcelGuide
Worked Examples Moving Between Excel and SQL Side by Side
Worked examples of one task in Excel and SQL: ranking, two-key joins, running totals, deduplication, distinct counts and percentiles, with the differences.
17 min read
JSON
Reusing Definitions With ref in JSON Schema Documents
Reuse schema pieces with $ref and $defs: local pointers, references between files by $id, recursive trees, and resolver and draft traps in Python and Node.
7 min read