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
Topic
Data Wrangling
Take messy CSV, JSON and spreadsheets and land them in tables without losing a row or a character on the way.
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
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
CSV
Detecting Encoded Payloads Hidden in CSV Fields Fast
A short Python scanner that flags base64 blobs, percent-encoded text, script tags, formula starts and oversized fields in a CSV before you import it.
6 min read
CSV
Sending a CSV by Email Safely and the Better Options
Email is a poor way to move a CSV of real data. Ranked alternatives, from not sending it to expiring links, plus how to encrypt a file you must attach.
6 min read
CSV
Handling CSV Files Full of Customer Data Under GDPR
The GDPR duties that land on a CSV of customer data, from minimization and retention to access requests and deletion, turned into practical engineering steps.
7 min read
CSV
Hashing or Masking Columns in a CSV Safely Without Leaks
A plain SHA-256 of an email is reversible with a guess list. Use a keyed HMAC for tokens and partial masking for display, with a script that does both.
7 min read
Excel
Exporting Invoices to Excel With Totals That Add Up
Export invoices and lines to Excel so totals add up: one row per line, tax by a stated rule, document totals checked, no gaps in numbering and exact decimals.
7 min read
CSV
Stripping Personal Data From a CSV Before Sharing It
Remove, generalize and pseudonymize the personal columns of a CSV with DuckDB before it leaves your team, then check that no one can still be singled out.
7 min read
Excel
Building a Budget Workbook From Query Output Reliably
Build a budget workbook from SQL output: actuals from the ledger, budget versions in a table, variances, planner input cells and a frozen release.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.