CSV
Converting CSV to XML With a Short and Safe Python Script
Convert CSV to XML with the standard library: invalid tag names and control characters need your own checks; a streaming writer does 193,708 rows in 22 MB.
10 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
Converting CSV to JSON Lines for Streaming Pipelines
Convert CSV to JSON Lines, one object per line: a 13 MB streaming script, DuckDB in 0.3 s, files that split cleanly and a cut-off file that shows itself.
9 min read
ExcelGuide
Excel Formulas Data People Use Every Week Explained
The Excel formulas data people use every week: lookups, conditional sums, text and date cleanup, arrays, errors and LET, with their SQL equivalents.
19 min read
CSV
Converting CSV to JSON With Python Without Losing Types
Convert CSV to JSON with the csv module, then decide on types: zip stays text, money stays exact. pandas gave 2134.0 for 02134; converters took 0.4 to 36 s.
9 min read
Excel
Differences Between Sheets and Excel Formulas to Know
How Google Sheets and Excel formulas differ: array handling, lookup and text functions, dates, locale, errors and what breaks when a file moves between them.
7 min read
CSVGuide
Converting CSV to JSON XML YAML and Excel in Practice
Convert one awkward CSV to JSON, JSON Lines, XML, YAML, Excel, Parquet, Markdown and SQL, read each back and see the cost on 193,708 rows.
22 min read
Excel
Sheets IMPORTDATA and Its Limits for Live Data Feeds
What Sheets IMPORTDATA can and cannot do as a live feed from a CSV URL: format, refresh, auth, size, parsing quirks and a way to measure it for your data.
7 min read
CSV
Automating a Nightly CSV Export With cron and psql
A cron export that cannot publish a half-written file: lock, temporary name, row-count check, atomic rename. Real cron failed first, on the environment.
10 min read
Excel
Publishing Database Results to a Sheet Automatically
Publish database results to a Google Sheet on a schedule: replace a range safely, mark it generated, stamp the update time and alert on failure.
8 min read
CSV
Exporting CSV From the Database Straight to a Gzip File
Pipe a PostgreSQL export into gzip or zstd: 130 MB became 42 MB in 4.6 s, with six settings compared and the pipeline that exits 0 after a failed export.
8 min read
CSV
Writing Dates and Booleans to CSV That Others Can Read
One timestamp exported as 09:30+00, 16:30+07 and 04:30-05, and dd.mm.yyyy that pandas read as 3 January. Fix dates and booleans in the query, not the session.
9 min read
Excel
Query Function in Sheets Versus SQL and What Maps to What
Compare the Google Sheets QUERY function with SQL: select, where, group by, order by, label and limit side by side, what QUERY lacks, and when to use which.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.