CSV
Header Variants and Normalizing Column Names in CSV Files
Clean up messy CSV headers into stable column names: BOMs, spaces, case, accents, duplicates, blanks and leading digits, with a function you can test.
7 min read
Tag
how-to
590 posts
CSV
Parsing Multi Line Headers in Report Style CSV Files
Turn a CSV with a title row and a two level header into a flat table: skip the title, forward fill merged top cells and join the levels into column names.
6 min read
CSV
Pipe Delimited Files From Legacy Systems Without Surprises
Read pipe delimited files from banks and mainframes: a trailing pipe that adds a phantom column, unquoted values containing pipes, trailers and code pages.
7 min read
CSV
Fixed Width Files and Converting Them to CSV Safely
Convert a fixed width file to CSV by slicing each line at known positions, check every line length, and handle implied decimals, padding and wide characters.
7 min read
CSV
CSVW and Describing CSV With Metadata That Tools Can Read
Describe a CSV's delimiter, header, column names, types and keys in a CSVW metadata file, then use it to read the file correctly. Includes a working example.
7 min read
PostgreSQL
SQL JSON Support Added in Recent Versions of PostgreSQL
SQL/JSON in PostgreSQL 16 to 18: constructors, IS JSON, JSON_EXISTS, JSON_QUERY, JSON_VALUE and JSON_TABLE, with examples.
6 min read
Excel
Handling Multi Currency Columns in Workbooks and Tables
Handle amounts in several currencies: keep the original amount and code, convert once with a dated rate, never sum mixed currencies and show the rate.
7 min read
Excel
Timesheet Data From Excel Into a Database Without Loss
Load Excel timesheets into a database cleanly: one row per person, day and project, hours as decimals, validation of codes and limits, and approvals kept.
8 min read
Excel
Rounding Rules That Survive Audit in Spreadsheets and SQL
Write a rounding policy an auditor can follow: where to round, which tie rule, per line or per total, and how to test it in Excel, Python and SQL.
7 min read
Excel
Protecting a Generated Workbook From Accidental Edits
Lock the cells, sheets and structure of a workbook your script generates so readers cannot break formulas, with openpyxl and XlsxWriter, and know the limits.
7 min read
Excel
Watching a Folder for New Excel Files and Importing Them
Watch a folder for new Excel files and import each once: polling or watchdog, waiting for the copy to finish, moving files, idempotent loads and failures.
7 min read
Excel
Batch Converting XLS Files to XLSX With LibreOffice
Convert a folder of old XLS files to XLSX with LibreOffice headless, Python or Excel automation, and check what formatting and formulas survive.
6 min read