CSV
Header Variants and Normalizing Column Names in CSV Files
Clean up messy CSV headers into stable column names: BOMs, spaces, case, accents, duplicates, blanks and leading digits, with a function you can test.
7 min read
Tag
cleaning
23 posts
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
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
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
Removing Duplicates in Excel Versus SELECT DISTINCT
Reproduce Excel's Remove Duplicates in SQL: how DISTINCT and ROW_NUMBER choose the row to keep, how case and spaces matter, and how to delete duplicates.
7 min read
XML
Encoding Mismatch Errors in XML Documents From Mixed Sources
When an XML feed mixes encodings or was encoded twice: why detectors guess wrong on short text, the UTF-8 first rule and per-line fallback.
7 min read
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
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
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
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
XML
Invalid Character in XML and Stripping Control Characters
Fix PCDATA invalid Char value and reference to invalid character number: which characters XML 1.0 forbids, how to strip them from 28 MB in 0.03 s and verify.
6 min read