JSONGuide
JSON Functions in SQLite and What You Can Do With Them
A tested guide to SQLite's JSON functions: reading, arrows, editing, json_each, aggregates, JSONB, expression indexes and the type traps, on 200,000 rows.
18 min read
Library
Everything you need to level up your coding skills.
CSV
Reading a CSV With Inconsistent Column Counts in Python
Find the rows in a CSV whose field count differs from the header, learn why they happen, and quarantine or repair them in Python without losing data.
8 min read
ExcelGuide
A Practical Guide to Automating Excel Work Without Clicking
How to move recurring Excel work into scripts: choosing VBA, Python, Office Scripts or Power Query, scheduling, merging, splitting, protecting and testing.
17 min read
CSV
Why pandas Turns ZIP Codes Into Numbers and How to Stop It
pandas read_csv infers a column of digits as integers, so 02134 becomes 2134. See the cause, the 190,451 broken codes in 2M rows, and the fixes that work.
7 min read
YAMLGuide
Converting YAML to and From JSON CSV XML and Excel
What survives when YAML becomes JSON, CSV, XML or Excel and back, with a tested loss matrix, the type traps in each direction and a conversion checklist.
19 min read
Excel
Fixing Duplicate Column Names When Reading Excel Data
Handle duplicate column headers when reading Excel in pandas, Power Query and SQL loads: automatic suffixes, safe renaming and what duplicates mean.
7 min read
XML
Sanitizing Data You Put Into an XML Document Safely
User text put into XML by string formatting can add elements and attributes. See the injection, the escaping that stops it, CDATA, bad characters and names.
7 min read
CSV
Parsing Dates While Reading a CSV With pandas Correctly
Use parse_dates and date_format in pandas read_csv, then handle mixed formats and day-first files, where format="mixed" guessed two conventions in one column.
8 min read
JSON
Fixing Invalid JSON Text Errors in MySQL Inserts and Imports
Decode MySQL error 3140 Invalid JSON text and its relatives, including the backslash trap in SQL string literals that silently changes your JSON.
9 min read
Excel
Excel Cell Contains More Than 32767 Characters Error
Excel allows 32767 characters per cell and draws only 1024. See what happens to longer text on import, export and in scripts, and where to store it.
6 min read
CSV
Reading Only Some Columns of a Large CSV With pandas
Use usecols, dtype, nrows and chunksize to load a large CSV into pandas with a fraction of the memory. Measured on 2,000,000 rows, with the float32 trap.
8 min read
XML
Limiting XML Size and Depth for Uploads With Measured Limits
Where lxml and ElementTree stop on deep nesting, long text nodes and wide documents, what a tree costs in memory, and the limits to set for uploads.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.