Excel
Rejecting Files That Do Not Match the Template Cleanly
Reject Excel files that do not match the template in a way senders can act on: staged checks, stable codes, specific messages, quarantine and a retry path.
7 min read
Tag
how-to
590 posts
XML
Counting Nodes in XML From the Shell Without Miscounting
grep counts lines, not elements. Count XML elements correctly with xmllint, xmlstarlet and a streaming parser, and see why grep gave 20000 for 49819 lines.
6 min read
JSONGuide
Moving Between JSON and MongoDB Documents Without Loss
A working guide to moving data between JSON and MongoDB: types, import flags, Extended JSON exports, round trips, flattening, nested queries, validation.
17 min read
Excel
Versioning Template Layouts So Old Files Still Import
Version Excel template layouts so the loader recognizes old files: a version cell, compatible changes, migration rules, a support window and a changelog.
8 min read
CSV
Importing a CSV Into Google Sheets With the Right Types
Import a CSV into Google Sheets without losing zeros or flipping dates: the import dialog, the locale, IMPORTDATA and an Apps Script that keeps text.
6 min read
Excel
Reserving Notes in a Separate Sheet of Import Templates
Give notes, comments and instructions their own place in an import template so the data sheet stays a clean table, and make the loader ignore strays.
7 min read
XML
Using yq to Read XML From the Command Line and Pipelines
Two different tools are called yq. Both read XML and print JSON or YAML, with different prefixes for attributes. See each one's output and which you have.
6 min read
CSV
Saving an Excel Sheet as CSV Without the Extra Commas
Excel adds trailing commas and blank rows to a CSV when empty cells outside your data hold formatting. Find the used range, clear it and check the file.
7 min read
JSONGuide
Pulling JSON From APIs Into a Database Reliably in Python
A working guide to loading JSON APIs into a database: raw pages, pagination, rate limits, upserts, cursors, shape checks and retries, with a tested loader.
17 min read
Excel
Data Types per Column in Templates That Feed a Database
Map Excel template columns to database types: text for identifiers, real dates, fixed-decimal numbers, flags and lists, with validation and checks.
7 min read
XML
Editing XML Files in Place From a Script Without Damage
Change a value, add an attribute and delete an element in XML from a script with xmlstarlet or lxml, and compare with sed on diffs, comments and escaping.
6 min read
CSV
How to Import a CSV Into Excel With Power Query Reliably
Import a CSV into Excel with Power Query so encoding, delimiter and column types are set once, and a refresh loads next week's file the same way.
7 min read