CSV
A Supplier Price List With Merged Header Rows Fixed
A price list CSV has title lines, a header on row 4, category names written once per group and a footer. Skip, forward fill, filter and check the item count.
7 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.
CSV
An Analytics Export With Percent Signs and Dashes Cleaned
An analytics CSV writes numbers as text: 12,430, 42.5%, $1,204.50, (4.1%) and dashes. Convert them with one macro and prove nothing is left unparsed.
6 min read
CSV
A CRM Export With Multi Line Notes and How to Parse It
A CRM export has notes with line breaks inside quoted fields, so line counts and grep mislead. Parse by records, then flatten the breaks or keep them.
6 min read
CSV
An Ecommerce Export With Commas Inside Product Names
A product export writes names with commas and no quotes, so rows have 6 fields, not 4. Rejoin the middle fields from the good ends and verify every row.
6 min read
CSV
A Bank CSV With Dates in Two Formats and How to Fix It
A statement mixes 2026-03-04 and 05/03/2026 in one column. Classify each row, prove the slash order from the data, convert, and check the dates are in order.
6 min read
PostgreSQL
Storing Uploaded Excel Files and Their Parsed Rows
Store uploaded Excel files and their parsed rows in PostgreSQL: keep the original, stage, track errors, dedupe by hash and link rows to uploads.
7 min read
PostgreSQL
Reading XML With xmltable in a Real Import in PostgreSQL
Import XML into PostgreSQL with xmltable: XPath row and column paths, namespaces, attributes, nested elements, large files and validation of a feed.
6 min read
Excel
Error Handling With IFERROR and Safer Alternatives Today
Handle Excel formula errors without hiding bugs: IFERROR versus IFNA, testing inputs first, ISERROR functions, XLOOKUP not-found values and error codes.
6 min read
Excel
SUMPRODUCT as a Swiss Army Formula for Conditional Sums
Use SUMPRODUCT for weighted averages, OR conditions, multi-column counts and arithmetic before summing, with its speed limits and what to use instead.
6 min read
XML
Dealing With XML Feeds That Break Their Own Schema
A feed with a raw ampersand, a missing id, a bad date, a duplicate id and an extra element: repair what is safe, quarantine the rest, report each reject.
7 min read
JSON
Handling a Weather API With Nested Arrays of Measurements
Weather APIs return columns as parallel arrays. Turn them into rows with pandas and DuckDB, handle nulls and unequal lengths, and keep units and zones.
6 min read
JSON
Cleaning a Twitter Style Archive File That Is Not Quite JSON
Clean a social media archive that is JavaScript, not JSON: strip the assignment, join parts, fix string numbers and dates, unescape HTML and write Parquet.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.