CSV
Loading a CSV Into a Staging Table Then Cleaning It in SQL
Load a messy CSV as text first, then convert it with SQL. Build a three format date parser, a reject table with reasons, and the typed insert.
8 min read
Library
Everything you need to level up your coding skills.
Excel
Why an Exported Excel Date Shows as a Number and the Fix
An exported date shows as 45292 because Excel stores dates as numbers and the cell has no date format. See the cause, the code fix and a file repair.
7 min read
CSV
Importing Only Some CSV Columns Into a PostgreSQL Table
COPY cannot skip file columns. Compare two ways to load three of eight columns of a 2,000,000 row CSV, with timings, and see why cut breaks on quotes.
6 min read
YAML
Fixing Special Characters That Break YAML Values Safely
Colons, hashes, braces, backslashes and percent signs in YAML values: which break, which load silently wrong, tested outputs and the quoting rule.
6 min read
JSON
JSON Versus JSONB in PostgreSQL and Which to Store
PostgreSQL has two JSON types. What each keeps, drops and costs, measured on 200,000 rows, and the narrow cases where plain json is the right choice.
7 min read
XML
Loading a Product Feed in XML Into PostgreSQL With xmltable
Load a Google Merchant style product feed into PostgreSQL with xmltable and a namespace, split price from currency, and handle missing GTINs and empty prices.
7 min read
Excel
Making Excel Exports Open Cleanly Every Time on Any PC
Stop repair prompts, garbled accents and wrong columns when exports open in Excel: legal characters, sheet names, time zones, NaN, BOM and separators.
7 min read
CSV
PostgreSQL Invalid Byte Sequence for UTF8 in a CSV Import
COPY stops with an invalid byte sequence error when a CSV is Latin 1. Fix it with the ENCODING option or client_encoding, and avoid the SQL_ASCII trap.
7 min read
CSV
PostgreSQL COPY Error Extra Data After Last Expected Column
The COPY error means a row has more fields than the table has columns. See the four usual causes, how to find the line, and the fix for each one.
7 min read
Excel
Emailing a Scheduled Excel Report From a Database Job
Build a job that queries a database, writes an Excel file and emails it on a schedule with smtplib and cron, with safe credentials and failure alerts.
8 min read
JSONGuide
Working With JSONB in PostgreSQL From First Query to Index
A working guide to JSONB in PostgreSQL 18: reading, filtering, updating, unnesting and aggregating documents, then GIN and expression indexes, with timings.
21 min read
XML
Should You Store XML in a Database Column or Shred It
Store XML whole, shred it into tables, or both? What each engine keeps and loses, what validation you get, and what it costs on 200,000 documents.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.