CSV
Reading a CSV File in Node With Streams and Backpressure
Read a large CSV in Node with fs.createReadStream and csv-parse, filter with pipeline, stop early, and handle errors, BOMs and Windows-1252 files.
9 min read
Library
Everything you need to level up your coding skills.
JSON
Storing Settings as JSON in SQLite and Updating One Key
A SQLite settings table with a JSON column: a CHECK that it is an object, json_set for one key, json_patch upserts, defaults on read, counters and a trigger.
8 min read
XML
Importing XML Into Excel With an XML Map Step by Step
How Excel maps XML elements to cells with an XML map, what the XML Source pane does, how append and overwrite work, and why repeating data is the hard part.
6 min read
Excel
Querying an Excel File With SQL in DuckDB Step by Step
Run SQL on an Excel workbook with DuckDB: the excel extension, a pandas DataFrame route, sheet and range options, types and checks that keep numbers right.
7 min read
Editor's pick
CSVGuide
Parsing CSV in JavaScript and Node Without Surprises
A tested guide to CSV in JavaScript: csv-parse, Papa Parse and a hand-written parser timed on 2,000,000 rows, plus streams, types, uploads and downloads.
20 min read
YAML
Fixtures in YAML for Database Tests With pytest and Rollback
Keep test data in YAML files, load it inside a transaction per test and roll back after, with pytest and PostgreSQL. Three tests ran in 0.13 seconds.
7 min read
CSV
Splitting a CSV by Column Value Into Separate Files
Split a CSV into one file per column value with awk, qsv, Miller or DuckDB, keep the header in every file, check the counts and avoid unsafe file names.
9 min read
XMLGuide
Moving XML Into and Out of Spreadsheets Without Losing Data
How to get XML into a spreadsheet and back: XML maps, opening files, flattening, the 2003 format, scripts, types and the checks that catch silent loss.
17 min read
ExcelGuide
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
JSON
Using the JSONB Format Added in SQLite 3.45 Safely
SQLite 3.45 added JSONB, a binary JSON blob. Convert a column, measure size and speed, learn the function pairs and the pitfalls with older drivers.
8 min read
CSV
Removing Duplicate Rows From a CSV With Shell Tools
Remove duplicate CSV rows with sort -u, awk, qsv and Miller. See what each does to the header and the order, which row it keeps, and why awk drops a good row.
9 min read
Excel
How Many Rows Can Pandas Read From Excel Before It Struggles
Estimate how many rows pandas can read from an Excel file for your memory, why read_excel loads everything, and how usecols and dtype shrink the load.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.