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
Tag
how-to
590 posts
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
XML
Parsing a Bank Statement in XML and Reconciling the Balance
Read an ISO 20022 style account statement, apply the credit and debit sign, and check that opening balance plus entries equals the closing balance.
7 min read
JSON
Required Fields and Optional Fields in JSON Schema
How required, null, empty strings and defaults differ in JSON Schema, with test results for dependentRequired and if-then rules for conditional fields.
6 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
XML
Analyzing Publication Metadata From XML Archives in Python
Read thousands of journal article XML files straight from a tar.gz, count by year, journal and author count, rank keywords, and avoid unpacking the archive.
6 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
CSV
Converting CSV to XML With a Short and Safe Python Script
Convert CSV to XML with the standard library: invalid tag names and control characters need your own checks; a streaming writer does 193,708 rows in 22 MB.
10 min read
JSON
Writing Your First JSON Schema From a Real Document
Build a JSON Schema from a user profile document one keyword at a time, run it in Python after each step, and catch typos that a validator ignores silently.
7 min read
CSV
Converting CSV to JSON Lines for Streaming Pipelines
Convert CSV to JSON Lines, one object per line: a 13 MB streaming script, DuckDB in 0.3 s, files that split cleanly and a cut-off file that shows itself.
9 min read