CSVGuide
Fixing Garbled Text in CSV Files and Keeping It Fixed
Why accents, euro signs and emoji break in CSV files, how to diagnose each symptom from the bytes, and one routine that converts, loads and checks a file.
21 min read
Library
Everything you need to level up your coding skills.
Excel
Importing Excel Dates Correctly Into a Date Column
An Excel date column holds serial numbers, real dates or text. Classify each value in the stage, convert each kind by an explicit rule and reject the rest.
7 min read
CSV
A Cheat Sheet of CSV Edge Cases to Test Every Parser With
Thirty six tiny CSV files that separate good readers from bad ones, with the right answer for each and the readers that missed it in my tests.
8 min read
YAML
YAML Error Mapping Values Are Not Allowed Here Fixed
What mapping values are not allowed here means, three causes found in real files, an unquoted colon, a stray space and a double value, and the fix for each.
6 min read
XML
Content Is Not Allowed in Prolog and the Hidden BOM
What Content is not allowed in prolog means in Java: a BOM decoded to text, a double BOM, JSON or text instead of XML, and how to diagnose it.
6 min read
Excel
Handling Excel Files With Title Rows Above the Header
Find the real header row in an Excel sheet with a title, a date line or a logo above it, read from there with pandas, and cut notes and totals off the bottom.
7 min read
CSV
TSV Versus CSV and When Tabs Are the Saner Choice for Data
TSV avoids most quoting because tabs rarely appear in text, but there are three TSV dialects and they disagree. See where each breaks and which to pick.
7 min read
JSON
Finding the Exact Line of a JSON Syntax Error in Any Parser
Every JSON parser reports a position in a different way. How to turn a character offset into a line, a column and a caret in Python, Node and jq.
9 min read
CSV
How to Escape Quotes and Newlines Inside CSV Fields
Write CSV that survives every reader. Wrap fields with commas, quotes or line breaks, double inner quotes, and round trip test it against four readers.
9 min read
Excel
Loading Multiple Excel Sheets Into Separate Tables
Read every sheet of an Excel workbook with pandas, map each to its own staging table with safe names, skip hidden or unexpected sheets and verify the counts.
7 min read
XML
Undefined Entity Errors in XML and the Ampersand Problem
Fix undefined entity and Entity not defined errors in XML from HTML entities like nbsp and eacute: convert to numeric references, a DOCTYPE, or lxml recover.
6 min read
CSV
Why a CSV File Has No Types and What That Costs You
A CSV stores text, so every reader infers types. Measure what inference costs in time and memory on 2 million rows, and what it breaks: ZIP codes and dates.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.