JSON
Oracle JSON_TABLE for Turning Documents Into Rows Explained
How Oracle's JSON_TABLE projects a JSON document into relational rows and columns, with row paths, nested paths, ordinality, ON ERROR and ON EMPTY clauses.
7 min read
Tag
how-to
590 posts
CSV
Reading and Writing CSV in Go With encoding csv and Streams
Read and write CSV in Go with encoding/csv: quoting, field-count errors, LazyQuotes, the BOM it leaves in, ReuseRecord and 2,000,000 rows in 7 MB.
9 min read
Excel
Replacing a Fragile Excel Model With a Query and a View
Move a fragile Excel model into a SQL view: map inputs, calculations and outputs, translate formulas, run both in parallel and keep Excel as the front end.
8 min read
JSON
Building JSON From Query Results With FOR JSON in SQL Server
Turn SQL Server rows into JSON with FOR JSON PATH and AUTO, nest child rows, control nulls and wrappers, and avoid the double-encoding trap with JSON_QUERY.
7 min read
Excel
Checking Totals Between Two Excel Reports With SQL
Reconcile two Excel reports with SQL: compare grand totals, then group totals, then rows with a full outer join, and sort the differences into causes.
7 min read
YAML
Using DuckDB to Query Data Stored as YAML With read_yaml
Install DuckDB's yaml community extension and query YAML files with read_yaml: inferred types, nested structs, unnest, globs, mixed types and broken files.
6 min read
CSV
Writing CSV From an Array of Objects in JavaScript
Turn an array of objects into CSV in JavaScript by hand or with csv-stringify: quoting, dates, booleans, formulas, BOM and a streaming writer.
9 min read
XML
Reading Excel XML Spreadsheet 2003 Files With Python
The old Excel XML Spreadsheet 2003 format is plain XML with Worksheet, Row, Cell and Data elements. Read it with lxml, handle Index gaps and types.
7 min read
Excel
Building Cohort Tables From an Excel Export With SQL
Turn an Excel export of orders into a cohort retention table with SQL: first purchase month, months since, a retention triangle and honest recent cohorts.
7 min read
JSON
Reading JSON in SQL Server With OPENJSON and a Schema
How OPENJSON turns a JSON array or object into rows in SQL Server, with the default key, value and type columns, the WITH clause, nested arrays and the traps.
7 min read
CSV
Streaming a Huge CSV Into a Database From Node the Fast Way
Load 2,000,000 CSV rows into PostgreSQL and SQLite from Node and compare per-row inserts, batches, transactions and COPY, with timings and failure behavior.
10 min read
Excel
Building a Forecast Baseline From Excel History in SQL
Build simple forecast baselines from monthly history in SQL: last value, seasonal naive, moving average and linear trend, and test them on held out months.
7 min read