CSV
Exporting a SQLite Query Result to CSV From the Shell
Export a SQLite query to CSV with the shell, see exactly how 3.54 quotes values, writes NULL and ends lines, and learn which options change each.
8 min read
Tag
how-to
590 posts
XML
Counting Elements and Attributes in an Unknown XML File
Profile an XML file you have never seen in 0.2 seconds and 21 MB of memory, by counting every element and attribute path with iterparse, and read the profile.
7 min read
YAML
Splitting a Large YAML Config Into Several Files Cleanly
YAML has no include. Two ways to split a config: numbered fragments merged in order, and a custom include tag on a safe loader. Both tested, with traps.
7 min read
CSV
Querying a CSV File in SQLite Without Importing It
Compile SQLite's CSV virtual table, query a file in place, and compare the cost with importing it and with DuckDB, including the typed schema surprises.
6 min read
Excel
Building a Pivot Table From a Database Query in Excel
Connect Excel to a database, load a SQL query as a connection and build a pivot table on it that refreshes. Covers the Data Model, parameters and traps.
7 min read
XMLGuide
Analysis on XML Data When Nobody Has Converted It Yet
A method for questions about an XML file nobody has converted: look, profile, sample, pick a tool, answer, then prove the numbers with a second method.
17 min read
CSV
Importing a CSV Into an Existing SQLite Table Step by Step
Importing into a table that holds data goes wrong four ways: duplicate keys, missing columns, extra columns and a new order. See each and the staging fix.
7 min read
JSON
Aggregating Rows Into JSON With jsonb_agg in PostgreSQL
Build nested JSON documents from relational tables with jsonb_agg, jsonb_build_object and to_jsonb, avoid the join fan-out bug, and see which form is fastest.
8 min read
ExcelGuide
Pivot Tables and Their SQL Equivalents for Excel Users
For people who know Excel pivot tables and need the SQL behind them: GROUP BY, conditional aggregates, ROLLUP, window functions, unpivoting and checks.
19 min read
CSV
Setting Column Types When SQLite Imports a CSV File Safely
sqlite3 .import makes untyped columns. Create the table yourself, choose between INTEGER, TEXT and STRICT, and see what a ZIP code and a bad price do in each.
7 min read
YAMLGuide
Structuring YAML Configuration Files That Last for Years
How to lay out YAML config so it stays correct: base and overlays, secret references, a schema, strict loading, key style, comments and a CI check, tested.
17 min read
CSV
Using the sqlite3 dot import Command With a CSV File
Every option of the sqlite3 .import command that matters for CSV, from --csv and --skip to --schema temp and piped input, with the output of each.
7 min read