CSV
Parsing Multi Line Headers in Report Style CSV Files
Turn a CSV with a title row and a two level header into a flat table: skip the title, forward fill merged top cells and join the levels into column names.
6 min read
Library
Everything you need to level up your coding skills.
CSV
Pipe Delimited Files From Legacy Systems Without Surprises
Read pipe delimited files from banks and mainframes: a trailing pipe that adds a phantom column, unquoted values containing pipes, trailers and code pages.
7 min read
CSV
Fixed Width Files and Converting Them to CSV Safely
Convert a fixed width file to CSV by slicing each line at known positions, check every line length, and handle implied decimals, padding and wide characters.
7 min read
CSV
PostgreSQL CSV Versus Text Format in COPY and Which to Use
PostgreSQL COPY has a CSV format and a text format that differ in delimiter, NULL marker and escaping. See the same rows in both and what a mismatch loads.
6 min read
CSV
Excel CSV Versus RFC CSV Differences Worth Knowing About
Where Excel's CSV departs from RFC 4180: delimiter, encoding, BOM, the sep line, displayed values, formulas and the ID trap, plus a converter script.
7 min read
CSV
CSVW and Describing CSV With Metadata That Tools Can Read
Describe a CSV's delimiter, header, column names, types and keys in a CSVW metadata file, then use it to read the file correctly. Includes a working example.
7 min read
CSVGuide
CSV Dialects and Variants You Will Run Into in Practice
The CSV dialects that exist in the wild, from RFC 4180 and Excel to PostgreSQL text, MySQL, TSV and pipes, with the settings that tell them apart.
19 min read
PostgreSQL
SQL JSON Support Added in Recent Versions of PostgreSQL
SQL/JSON in PostgreSQL 16 to 18: constructors, IS JSON, JSON_EXISTS, JSON_QUERY, JSON_VALUE and JSON_TABLE, with examples.
6 min read
PostgreSQL
Partitioning Mistakes That Make PostgreSQL Queries Slower
Partitioning mistakes that make PostgreSQL queries slower: wrong key, too many partitions, id lookups, missing pruning, tiny partitions and fixes for each.
7 min read
Excel
Handling Multi Currency Columns in Workbooks and Tables
Handle amounts in several currencies: keep the original amount and code, convert once with a dated rate, never sum mixed currencies and show the rate.
7 min read
Excel
Timesheet Data From Excel Into a Database Without Loss
Load Excel timesheets into a database cleanly: one row per person, day and project, hours as decimals, validation of codes and limits, and approvals kept.
8 min read
Excel
Rounding Rules That Survive Audit in Spreadsheets and SQL
Write a rounding policy an auditor can follow: where to round, which tie rule, per line or per total, and how to test it in Excel, Python and SQL.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.