Editor's pick
CSVGuide
Opening CSV Files in Excel and Sheets Without Damage
How to open, edit and save CSV files in Excel, Google Sheets and LibreOffice without losing zeros, digits, dates or accents: imports, settings, traps.
17 min read
Library
Everything you need to level up your coding skills.
Excel
Percentile Calculations Compared Across Excel and SQL
Compare Excel PERCENTILE.INC, PERCENTILE.EXC and MEDIAN with PostgreSQL percentile_cont and percentile_disc, with worked numbers.
6 min read
JSON
Normalizing a JSON Export Into Relational Tables Safely
Turn a blog export with authors, tags and comments into five PostgreSQL tables, with a tag junction table, merged author copies and checks against the source.
8 min read
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
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
YAML
Testing YAML Driven Jobs With pytest and Hypothesis
Test a YAML pipeline runner with pytest: order, unknown steps, cycles, duplicates, a Hypothesis property over random graphs and a check that the tests bite.
7 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
Get new articles by email
We send one email when a new article is published. No spam.