Excel
Building a Budget Workbook From Query Output Reliably
Build a budget workbook from SQL output: actuals from the ledger, budget versions in a table, variances, planner input cells and a frozen release.
7 min read
Tag
how-to
590 posts
CSV
Escaping Formula Characters in a CSV Export Safely
A small function that neutralizes formula triggers in CSV text cells, why negative numbers must be exempt, and a test table to keep the fix from regressing.
7 min read
Excel
Reconciling Bank Statements Against a Database Table
Reconcile a bank statement against a ledger table: load it, match in passes, list unmatched items on both sides and explain the closing difference.
8 min read
JSONGuide
Comparing and Patching JSON Documents Without Surprises
A tested guide to diffing, patching and merging JSON: normalizing, array identity, JSON Patch, Merge Patch, atomic apply, deep merge, history and tests.
17 min read
CSV
CSV Injection and How Formulas in a Cell Become an Attack
How a name starting with an equals sign turns your CSV export into an attack on whoever opens it, what payloads do, and how to test your own exports.
7 min read
XML
Splitting a Big XML File by Repeating Element Safely
Split a 36.6 MB XML file into eight valid files of 25,000 orders with iterparse in 1.5 seconds at 21 MB, and see why split -l produces broken XML.
6 min read
ExcelGuide
Excel Reports That Finance Teams Rely On Month After Month
What makes an Excel report dependable for finance: reconciliations, rounding, controls, evidence, invoices, budgets, timesheets and currencies.
16 min read
Excel
Giving Users a Template Instead of Rules for Uploads
Replace a page of upload rules with a ready template: built-in formats, dropdowns, an example row, a stable link and a note on what the file is for.
7 min read
CSV
Opening a CSV With More Than a Million Rows in Excel
Excel stops at 1,048,576 rows and drops the rest if you save. Four ways to work with a bigger CSV: aggregate it, split it, use the Data Model or sample.
6 min read
JSONGuide
Handling JSON Files Too Large to Parse With Ordinary Tools
A tested guide to JSON files too big to load: memory and string limits, array to JSON Lines, DuckDB, streaming, splitting, compression and databases.
18 min read
XML
Merging Several XML Files Into One Valid Document Safely
Why cat breaks XML, and three ways that work: XInclude with xmllint, XSLT with document(), and lxml, with the xml:base attribute side effect of XInclude.
7 min read
CSV
Keeping Long Numbers Intact When Excel Opens a CSV
Excel keeps 15 significant digits, so a 19 digit ID loses its last four. Why it happens, how to import the column as text, and how to check a file for damage.
6 min read