XML
Fixing XML With Unescaped Ampersands From a Legacy Export
Repair a 29 MB XML export with 215,385 raw ampersands using a lookahead regex that leaves existing entities alone, with the cases that need judgment.
6 min read
Library
Everything you need to level up your coding skills.
Excel
Validating Row Counts After an Excel Import Step by Step
Prove an Excel import lost nothing by counting rows at the source, the stage and the target, adding control totals, and reconciling rejects in one query.
8 min read
CSV
The UTF8 BOM in CSV Files and When It Helps or Hurts
Three invisible bytes at the start of a CSV can fix Excel or break a header match. See which readers strip a BOM and how to add or remove one.
7 min read
JSON
Python JSONDecodeError Expecting Value Line 1 Column 1
Why json.loads or response.json() raises Expecting value at line 1 column 1 in Python, how to see what the text really is, and a checklist for each cause.
7 min read
Excel
Staging Excel Data in a Table Before Cleaning It Up
Design a staging table for Excel loads with text columns and provenance, load it fast, profile it with SQL and keep or drop it on a retention rule.
8 min read
CSV
Why a CSV Shows Strange Characters Instead of Accents
Seeing odd letter pairs where accents belong means UTF-8 was read as a legacy encoding. See the byte level cause and a table of symptoms.
7 min read
XML
Python ParseError Unbound Prefix in XML and How to Fix It
Fix ElementTree ParseError unbound prefix and lxml Namespace prefix is not defined: fragments cut from a document, auto-declared prefixes, recover.
7 min read
YAML
YAML Error Found Character That Cannot Start Any Token
Which characters trigger this scanner error, a tab, at sign, backtick or percent sign, what each looks like in output and the fix that clears it.
6 min read
CSV
How to Detect the Encoding of a CSV File Before You Load It
Four encoding detectors gave four different answers on the same six files. See which ones to trust, how to cross check them, and when only the sender knows.
7 min read
Excel
Loading Excel Files With DuckDB and the Excel Extension
Query an XLSX sheet with SQL in DuckDB through its excel extension, read every column as text, create a table and copy it to PostgreSQL or Parquet.
8 min read
XML
Mismatched Tag Errors in Large XML Files and Finding Them
Locate an Opening and ending tag mismatch in a 28.6 MB XML file four ways: the parser, per-tag counts, record-by-record parsing and bisection, with timings.
6 min read
JSON
Fixing JSON Copied From a Web Page With Smart Quotes
JSON pasted from a web page, a chat or a Word file fails on curly quotes and invisible spaces. How to spot each character and clean the text safely.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.