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
Library
Everything you need to level up your coding skills.
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
JSONGuide
Converting JSON to CSV XML YAML and Excel Without Surprises
A working guide to converting JSON to CSV, XML, YAML, Excel, SQL and Markdown and back: what each format cannot hold, a 23 field round trip test, and checks.
18 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
Excel
Percentile Calculations Compared Across Excel and SQL
Compare Excel PERCENTILE.INC, PERCENTILE.EXC and MEDIAN with PostgreSQL percentile_cont and percentile_disc, with worked numbers.
6 min read
Excel
Splitting Text by Delimiter in Excel and SQL Queries
Split delimited text into columns or rows in Excel with TEXTSPLIT and Text to Columns and in PostgreSQL with split_part, string_to_array and unnest.
6 min read
Excel
Concatenate Text With a Separator Both Ways in Excel
Join text with a separator in Excel using TEXTJOIN and the ampersand and in SQL with concat_ws and string_agg, with blanks, NULLs and order handled.
7 min read
Excel
Lookup With Approximate Match Versus SQL Range Join
Compare Excel approximate match lookups with SQL range joins for tiers, rates and bands: how each handles gaps, boundaries and overlaps, and which to use.
7 min read
Excel
Count Distinct Customers per Month Both Ways in Excel
Count distinct customers per month in Excel with UNIQUE and FILTER, SUMPRODUCT or a PivotTable, and in SQL with COUNT(DISTINCT), with blanks handled.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.