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
Library
Everything you need to level up your coding skills.
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
XML
Converting XML to JSON and the Attribute Problem Explained
XML has attributes, repeated children and mixed text, and JSON has objects and arrays. Where the mapping is lossy, what xmltodict does, and the fixes.
7 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
XML
Parsing a Large Wikipedia Style XML Dump From bz2 Directly
Stream a compressed MediaWiki style export with iterparse, skip redirects, count links and text, and keep memory near 24 MB while reading 176 MB of XML.
6 min read
CSV
Opening a CSV File That Is Too Large for Excel or Sheets
A worksheet holds 1,048,576 rows. When a CSV has more, filter, aggregate or split it first with DuckDB, split or csvkit, and hand Excel a file that fits.
6 min read
Excel
Array Formulas and Why They Break Imports to Databases
How Excel array formulas and spilled ranges are stored in xlsx files, why loaders see blanks, extra cells or errors, and how to import them correctly.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.