Excel
Checking an Incoming Workbook Against a Template by Code
Check an incoming Excel workbook against a template in Python: headers, column types, required cells, allowed values, duplicates and a clear report.
9 min read
Library
Everything you need to level up your coding skills.
Excel
Spotting Data Entry Errors With Conditional Formatting
Use Excel conditional formatting to flag duplicates, blanks, out of range values, bad dates and unknown codes as people type, with formulas that stay fast.
7 min read
Excel
Using Excel Tables for Structured Input in Data Entry
Use Excel tables for data entry: automatic expansion, calculated columns, structured references, validation that follows new rows, and reading tables by name.
7 min read
Excel
Protecting Formula Cells From Edits in Shared Workbooks
Protect Excel formula cells from accidental edits: lock cells, protect the sheet, allow sorting and filters, hide formulas, and set it up from code.
7 min read
Excel
Data Validation Lists and Their Limits in Excel Sheets
How Excel data validation lists behave and where they stop: the 255 character typed list, range sources, long lists, dependent dropdowns and pasting.
7 min read
ExcelGuide
Controlling What Goes Into a Spreadsheet Before It Spreads
Control what goes into a shared spreadsheet: templates, data validation, tables, protection, error flags and import checks that stop bad data at the source.
17 min read
XML
Reading Publisher Metadata in JATS XML for One Article
Pull the title with inline markup, authors with ORCID and affiliations, group authors, editors and the several dates out of one JATS article front matter.
7 min read
XML
Flattening a Product Catalog Feed With Repeating Fields
A shopping feed with a namespace, prices written as 12.50 EUR, optional sale prices and repeated shipping and image fields, flattened into three clean tables.
7 min read
XML
Joining XML Derived Tables to Your Own Data Safely
A join between parsed XML orders and a customer CSV silently dropped 1,280 lines and 3.2 million in revenue. Find the unmatched keys and normalize them.
6 min read
XML
Storing Raw XML in a Data Lake and Parsing It Later
Land source XML unchanged, compressed, partitioned by date and described by a manifest with a checksum, so parsing can be rerun when the rules change.
6 min read
JSON
Parsing a Package Registry Metadata File Where Keys Are Data
Read a package metadata document where version numbers are keys: json_each, DuckDB MAP inference, semver sorting, dist-tags versus max, and the time map.
7 min read
CSV
A Survey Export With Hundreds of Columns Made Usable
A survey CSV has one column per question, 300 wide. Unpivot it to long form with DuckDB, drop blanks, name the questions and check the answer count.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.