Excel
Keeping Excel as the Front End to a Database Safely
Keep Excel for reading and analysing database data through queries and refresh, and decide how writes should reach the database without losing rules.
7 min read
Library
Everything you need to level up your coding skills.
Excel
Turning Repeating Columns Into a Proper Child Table
Replace columns like Phone 1, Phone 2, Phone 3 from an Excel sheet with a child table: unpivot in SQL or pandas, drop blanks, add keys and check the counts.
7 min read
Excel
Designing Database Tables From a Messy Excel Sheet
Turn a wide Excel sheet into database tables: find the entities, choose keys, remove repeated groups and derived columns, and check the design with queries.
8 min read
Excel
Moving From Excel to PostgreSQL Step by Step Without Loss
Move an Excel workbook into PostgreSQL: staging table, clean, normalized tables, constraints, roles, a verified load, and Excel as the read connection.
7 min read
Excel
Moving From Excel to SQLite for a Small Team Step by Step
Move an Excel list into SQLite: clean it, design tables, load with Python, add constraints, query from Excel and know the limits for a small team.
7 min read
Excel
Ten Signs Your Excel File Needs to Become a Database
Ten concrete signs that an Excel file has become a database, from several editors to lost updates and broken lookups, with a quick test for each.
7 min read
ExcelGuide
When a Spreadsheet Has Become a Database and What to Do
How to tell that an Excel file now behaves as a database, choose SQLite, Access or PostgreSQL, design tables, migrate safely and keep Excel as a reader.
17 min read
Excel
Testing That a Generated Excel Report Is Actually Correct
Test a generated Excel report with pytest: structure, control totals, formulas, formats and edge cases, plus a fixture of bad input and a golden file.
8 min read
YAML
Round Tripping YAML Without Losing Comments or Order
Edit a YAML file in code and keep its comments, anchors and quotes. PyYAML drops them, ruamel.yaml and yq keep them, and each changes some whitespace.
6 min read
CSV
Excel CSV Versus RFC CSV Differences Worth Knowing About
Where Excel's CSV departs from RFC 4180: delimiter, encoding, BOM, the sep line, displayed values, formulas and the ID trap, plus a converter script.
7 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
Get new articles by email
We send one email when a new article is published. No spam.