JSON
Indexing a MySQL JSON Field With a Generated Column
MySQL cannot index a JSON column directly. Add a generated column, index it, and learn the collation, NULL and cast traps that change query results.
8 min read
Library
Everything you need to level up your coding skills.
CSV
Fixing DuckDB CSV Type Detection That Guessed Wrong
When the DuckDB CSV sniffer picks the wrong type, override it by column, read as text and cast, or narrow the candidate types. A test file shows each guess.
8 min read
Excel
Pivot Table Refresh Errors and Missing Fields in Excel
Fix field name is not valid, reference is not valid, fields that vanish after refresh, old items that stay, lost formatting and new rows that never appear.
6 min read
CSV
Querying a Folder of CSV Files With a Glob in DuckDB
Read every CSV in a folder as one table in DuckDB with a glob, add the file name as a column, and handle files whose columns differ in count or order.
8 min read
XML
Reading Office Open XML From Docx and Xlsx Files in Python
A docx or xlsx is a zip of XML files. Open it with zipfile, read the paragraphs, runs and tables of a docx and the shared strings and cells of an xlsx.
7 min read
YAML
Using Anchors to Reduce Repetition in YAML Config Files
Put shared settings in one anchored block and merge it into each service. The web service replaced the whole environment block, since the merge is shallow.
6 min read
JSON
Updating Parts of a MySQL JSON Document in Place Safely
Use JSON_SET, JSON_REPLACE, JSON_REMOVE and array functions to change one part of a MySQL JSON column, with partial updates, binlog size and the counter trap.
8 min read
Excel
Calculating Percent of Total in a Pivot and in SQL
Show values as percent of total in an Excel pivot, then write the same share in SQL with SUM OVER and avoid integer division and rounding traps.
6 min read
CSV
Letting DuckDB Sniff the Delimiter and Types of a CSV
How the DuckDB CSV sniffer picks delimiter, header and types, how to read its verdict with sniff_csv, and what breaks when a bad value sits past the sample.
9 min read
XMLGuide
Common XML Formats You Will Meet in Real Work Reference
A reference to the XML formats you meet in practice: how to recognize each by root and namespace, what to extract, and the trap each one carries.
19 min read
CSV
DuckDB read_csv Options You Will Actually Need in Practice
The read_csv options that solve real problems in DuckDB 2.0, each on a file that needs it, including the nullstr trap that replaces the empty default.
7 min read
Excel
Unpivoting Columns Back Into Rows in SQL for Excel Data
Turn a wide Excel sheet with a column per month into a long table: LATERAL VALUES in PostgreSQL, UNPIVOT or CROSS APPLY in SQL Server, UNION ALL in MySQL.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.