ExcelGuide
A Guide to Excel Files That Are Too Big or Too Slow
Diagnose an Excel workbook that is huge, slow to calculate or too large to read in code, then fix it by size, formulas, the Data Model or a different format.
17 min read
Tag
how-to
590 posts
XMLGuide
Handy Command Line Tools for XML Work in Daily Practice
The command-line tools that cover daily XML work: xmllint, xmlstarlet, xsltproc, xq and yq, with tested commands, timings, exit codes and the traps of each.
19 min read
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
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
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
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
YAML
Reviewing YAML Config Changes in a Pull Request Safely
A two-line YAML diff can move a key to another level or turn a string into a float. Review the loaded data, not the text, with a short script and a linter.
7 min read
JSON
Validating JSON Before It Gets Into a MySQL Column
Enforce structure on a MySQL JSON column with JSON_TYPE checks and JSON_SCHEMA_VALID in CHECK constraints, with the draft 4 limits and the insert cost.
8 min read
CSV
Using pandas read_csv for Files That Do Not Behave
Make pandas read_csv cope with title lines, comments, European numbers, odd NA markers, short or long rows and the wrong encoding, one option at a time.
9 min read