JSONGuide
JSON in SQL Server Oracle Snowflake and BigQuery Compared
How SQL Server, Oracle, Snowflake and BigQuery store, read, unpack, build and index JSON, side by side, with the null, type and size traps in each.
19 min read
Library
Everything you need to level up your coding skills.
XML
Opening XML Files in Excel Without Mangled Data or Loss
What happens when you open an XML file in Excel, the choices it offers, why leading zeros and long numbers change, and a safer route that keeps text as text.
5 min read
CSV
Generating a CSV Download From JavaScript in the Browser
Build a CSV string, wrap it in a Blob and trigger a download with an object URL. Covers escaping, the Excel BOM, formula guards and how 1,000,000 rows behave.
9 min read
Excel
Year Over Year Comparison From a Monthly Excel Sheet
Compare each month with the same month last year from an Excel sheet in SQL: LAG, self joins, partial years, missing months and percent change.
7 min read
CSV
Parsing CSV in the Browser With Papa Parse Without Blocking
Parse an uploaded CSV in the browser with Papa Parse: typing traps, error objects, encodings, and chunks and workers measured on 500,000 rows.
11 min read
YAML
Reading YAML Into SQLite for Quick Queries With json_extract
Load a YAML list into an in-memory SQLite table as JSON text and query it with json_extract and json_each, with a json_valid check that rejects bad rows.
6 min read
JSON
Validating Text as JSON With json_valid in SQLite Tables
What json_valid accepts in SQLite with each flag, why a CHECK can pass a NULL, how to find bad rows, add a check to an existing table and check JSONB blobs.
7 min read
Excel
Summarizing a Sales Workbook With GROUP BY in SQL Queries
Summarize a sales workbook with GROUP BY: totals by region and month, averages, rollups and ranking, with checks that make the numbers trustworthy.
7 min read
XML
Exporting Excel Data as XML With a Schema and What Blocks It
Excel exports XML only through an XML map built from a schema. The 65,536 row cap, the constructs that block export, and a script that does the same job.
6 min read
CSV
Using csv parse in Node for Large Files Without Surprises
The csv-parse options that matter on big files in Node: columns, cast, on_record, relax_quotes and max_record_size, timed on 2,000,000 rows.
8 min read
PostgreSQL
Seq Scan Versus Index Scan Versus Bitmap Scan Explained
Seq scan versus index scan versus bitmap scan in PostgreSQL: how each works, when the planner picks it, index only scans and why a seq scan can be right.
7 min read
Excel
Joining Two Excel Sheets With SQL in DuckDB or SQLite
Join two Excel sheets with SQL in DuckDB or SQLite: choose the join type, find rows that match nothing, avoid duplicated keys and check row counts.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.