Excel
Array Formulas and Why They Break Imports to Databases
How Excel array formulas and spilled ranges are stored in xlsx files, why loaders see blanks, extra cells or errors, and how to import them correctly.
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.
CSV
Converting CSV to Markdown Tables for Documentation
pandas, Miller and csvlook turn a CSV into a Markdown table, and each breaks on a pipe or a newline. pandas wrote 12.50 as 12.5 and csvlook 02134 as 2,134.
9 min read
Excel
Date Formulas for Month Ends and Business Days in Excel
Excel date formulas for month starts and ends, adding months, business days with holidays, fiscal years and age, with the edge cases that break them.
7 min read
CSV
Converting CSV to SQL INSERT Statements the Safe Way
Generate INSERT statements from a CSV without breaking on O'Neil or running a DROP TABLE hidden in a value. Batches of 1,000 loaded 193,708 rows in 1.6 s.
13 min read
Excel
LET and LAMBDA for Readable Formulas in Excel Workbooks
Use LET to name values and LAMBDA to define reusable functions so long Excel formulas become readable, faster and easier to review, with portability notes.
6 min read
CSV
Converting JSON to CSV When the Data Is Nested With Arrays
Flatten nested JSON to CSV with pandas, Python or DuckDB: dotted columns, a row per array item, the order that vanishes and arrays written as Python text.
10 min read
CSV
Converting Excel to CSV Without Losing Leading Zeros
pandas turned a text cell 02134 into 2134 and in2csv did too; qsv kept it. A number formatted 00000 loses its zeros everywhere unless you rebuild them.
10 min read
Excel
TEXTSPLIT TEXTJOIN and Text Cleanup Formulas That Work
Use TEXTSPLIT, TEXTJOIN, TEXTBEFORE and TEXTAFTER with TRIM, CLEAN and SUBSTITUTE to clean text in Excel, with fallbacks for older versions.
6 min read
CSV
Converting CSV to an Excel Workbook With Formatting
Write a CSV to .xlsx with xlsxwriter: text zips, number formats, real dates and a frozen header. write() made =1+1 a formula; rows past 1,048,575 vanished.
8 min read
Excel
Dynamic Array Functions FILTER SORT and UNIQUE Explained
Learn the Excel dynamic array functions FILTER, SORT, SORTBY and UNIQUE: syntax, spill behavior, combining them, errors and what scripts see.
6 min read
CSV
Converting CSV to YAML for Config Seeds Without Surprises
Hand-written YAML turned NO into false, 02134 into 1116 and 12:30 into 750; yaml.safe_dump quotes them. Dumping 193,708 records took 53 s, 15 s with libyaml.
9 min read
Excel
XLOOKUP Examples That Replace VLOOKUP Pain for Good
Six XLOOKUP examples that fix VLOOKUP problems: lookups to the left, missing keys, two-way lookups, multiple columns, wildcards and nearest matches.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.