Excel
Joining Two Excel Sheets With SQL in DuckDB or SQLite
Join two Excel sheets with SQL in DuckDB or SQLite: choose the join type, find rows that match nothing, avoid duplicated keys and check row counts.
7 min read
Tag
how-to
590 posts
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
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
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
CSV
Sorting a CSV File by Column in the Terminal Correctly
Sort a CSV by column from the shell without losing the header or reading text as numbers. Compare sort, qsv, Miller, csvsort and DuckDB on 2,000,000 rows.
9 min read