CSV
Doing a Three Way Merge of CSV Edits by Key and by Cell
Merge two people's edits of a CSV against a common base: git merge-file conflicts when both touch a row, merging by key and cell resolves different cells.
6 min read
Tag
how-to
590 posts
XML
Reading Microsoft Project and Visio XML Exports in Python
Read a Microsoft Project XML plan into tasks with durations, start dates, percent complete and predecessors, and learn how Visio files are zipped XML.
6 min read
Excel
Splitting Text by Delimiter in Excel and SQL Queries
Split delimited text into columns or rows in Excel with TEXTSPLIT and Text to Columns and in PostgreSQL with split_part, string_to_array and unnest.
6 min read
CSV
Keeping a CSV Sorted So Diffs Stay Small in Version Control
Keep a CSV sorted by its key so a one-row change is a one-line diff: sort the body not the header, use byte order and numeric keys, and sort in the writer.
6 min read
JSON
Turning a Deeply Nested API Response Into Three Tables
Split a nested order response into customers, orders and order_items in PostgreSQL with keys and constraints, and see what the nested address belongs to.
7 min read
Excel
Concatenate Text With a Separator Both Ways in Excel
Join text with a separator in Excel using TEXTJOIN and the ampersand and in SQL with concat_ws and string_agg, with blanks, NULLs and order handled.
7 min read
XML
SOAP Responses and Pulling the Payload Out With XPath
Take a SOAP envelope apart: Header, Body, the namespaced response and a Fault, then read the price from the payload in Python without a client library.
6 min read
CSV
Using git diff Sensibly on a CSV File With Word Diff
Make git diff readable for a CSV: word diff on commas, a textconv that aligns columns, a sorted file with one record per line and a trailing newline.
6 min read
CSV
Calculating Month Over Month Growth From a CSV in SQL
Compute month over month growth with lag(): revenue of 46.50, 202.00 and 382.00 grew 334.4% and then 89.1%. Partial months, gaps and a zero base need care.
6 min read
XML
Keeping Both XML and JSON Output From One Source Model
Build one internal model and two small renderers, negotiate the format with the Accept header, and test that both carry the same data on awkward cases.
6 min read
JSON
Flattening JSON in SQL With Lateral Joins in PostgreSQL
Flatten JSON arrays into rows in PostgreSQL with CROSS JOIN LATERAL, jsonb_to_recordset and WITH ORDINALITY, keep empty arrays, and compare MySQL and SQLite.
8 min read
CSV
Filling Gaps in a CSV Time Series With SQL and DuckDB
Find and fill missing days in a CSV time series: build a calendar with range(), left join the data and choose zero, carry-forward or interpolation.
7 min read