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
Topic
Data Wrangling
Take messy CSV, JSON and spreadsheets and land them in tables without losing a row or a character on the way.
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
CSV
A Government Dataset With Footnotes in the Data Cells
Statistical CSVs put flags in value cells: 1,204,300 (p), 5.3 e, and dots for missing. Split number from flag, keep both and check against the stated total.
7 min read
CSV
A Log Export With Unescaped Quotes and How to Read It
A log export has quotes inside quoted fields without doubling them. See what Python and DuckDB return, which setting reads them right and how to prove it.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.