Excel
Data Types per Column in Templates That Feed a Database
Map Excel template columns to database types: text for identifiers, real dates, fixed-decimal numbers, flags and lists, with validation and checks.
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
Why Excel Shows a CSV in One Column and What Causes It
Excel puts a whole CSV row in one cell for five different reasons. Learn how to tell which one you have by reading the first line of the file.
7 min read
CSV
How to Import a CSV Into Excel With Power Query Reliably
Import a CSV into Excel with Power Query so encoding, delimiter and column types are set once, and a refresh loads next week's file the same way.
7 min read
Excel
Naming Sheets and Columns for Machines in Excel Templates
Name sheets and columns in Excel templates so code and databases can use them: lowercase snake case, no punctuation, stable keys, label mapping.
7 min read
CSV
Opening a Semicolon Separated CSV in Excel Without Mess
A semicolon CSV lands in one column when Excel expects commas. Five ways to open it correctly, and how to convert the file when decimal commas are involved.
7 min read
Excel
One Header Row and No Merged Cells in Import Templates
Why import templates need one header row and no merged cells, how to detect both in code, and how to repair files that arrive with titles and merged headings.
7 min read
CSV
Stopping Excel From Turning Gene Names and IDs Into Dates
Excel reads SEPT2 as 2-Sep and MARCH1 as 1-Mar, and saving makes the change permanent. Here is how to import, detect and recover gene names and similar IDs.
8 min read
ExcelGuide
Building Excel Templates That Import Cleanly Every Time
Design Excel templates that load into a database without cleanup: one header row, machine names, typed columns, a notes sheet, versions and rejection rules.
17 min read
CSV
Why Excel Drops Leading Zeros When You Open a CSV File
Why Excel turns 00123 into 123 when you double-click a CSV, what is lost on save, and how to keep zeros with the import wizard, Power Query or text columns.
6 min read
Editor's pick
CSVGuide
Opening CSV Files in Excel and Sheets Without Damage
How to open, edit and save CSV files in Excel, Google Sheets and LibreOffice without losing zeros, digits, dates or accents: imports, settings, traps.
17 min read
CSV
Doing a Three Way Merge of CSV Edits by Key and by Cell
Merge two people's edits of a CSV against a common base: git merge-file conflicts when both touch a row, merging by key and cell resolves different cells.
6 min read
CSV
Keeping a CSV Sorted So Diffs Stay Small in Version Control
Keep a CSV sorted by its key so a one-row change is a one-line diff: sort the body not the header, use byte order and numeric keys, and sort in the writer.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.