ExcelGuide
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
Tag
hub
102 posts
XMLGuide
XSLT and XQuery for Real Transformations Step by Step
A working guide to XSLT 1.0, 3.0 and XQuery on the same files: templates, grouping, enrichment, JSON output, parameters, debugging, speed and which to pick.
19 min read
Editor's pick
CSVGuide
Working With CSV Files in the Shell Without Opening Excel
A practical guide to CSV in the shell: coreutils, awk, csvkit, Miller, qsv and DuckDB compared on 2,000,000 rows, with the quoting rule and a triage script.
22 min read
XMLGuide
Parsing XML in Java C Sharp and Other Typed Languages
One XML file and one task in eight languages, with each output, the defaults that change your data, and how each treats absent, empty and bad values.
17 min read
YAMLGuide
Using YAML With Databases for Seeds Exports and Queries
Five jobs YAML does next to a database: seed data, exports, stored documents, test fixtures and queries, with tested code for PostgreSQL, SQLite and DuckDB.
19 min read
ExcelGuide
A Guide to Excel Files That Are Too Big or Too Slow
Diagnose an Excel workbook that is huge, slow to calculate or too large to read in code, then fix it by size, formulas, the Data Model or a different format.
17 min read
XMLGuide
Handy Command Line Tools for XML Work in Daily Practice
The command-line tools that cover daily XML work: xmllint, xmlstarlet, xsltproc, xq and yq, with tested commands, timings, exit codes and the traps of each.
19 min read
JSONGuide
JSON Functions in SQLite and What You Can Do With Them
A tested guide to SQLite's JSON functions: reading, arrows, editing, json_each, aggregates, JSONB, expression indexes and the type traps, on 200,000 rows.
18 min read
ExcelGuide
A Practical Guide to Automating Excel Work Without Clicking
How to move recurring Excel work into scripts: choosing VBA, Python, Office Scripts or Power Query, scheduling, merging, splitting, protecting and testing.
17 min read
YAMLGuide
Converting YAML to and From JSON CSV XML and Excel
What survives when YAML becomes JSON, CSV, XML or Excel and back, with a tested loss matrix, the type traps in each direction and a conversion checklist.
19 min read
PostgreSQLGuide
Reading EXPLAIN ANALYZE Output in PostgreSQL Step by Step
Read EXPLAIN ANALYZE output in PostgreSQL: plan tree, cost, rows and loops, scan and join nodes, BUFFERS, sorts, estimates and a way to find the slow step.
18 min read
Editor's pick
CSVGuide
Reading and Writing CSV Files in Python the Right Way
A working guide to CSV in Python: csv module, DictReader, pandas, polars and DuckDB measured on 2,000,000 rows, plus the encoding, quoting and type rules.
22 min read