CSV
Opening a CSV With More Than a Million Rows in Excel
Excel stops at 1,048,576 rows and drops the rest if you save. Four ways to work with a bigger CSV: aggregate it, split it, use the Data Model or sample.
6 min read
Library
Everything you need to level up your coding skills.
CSV
Keeping Long Numbers Intact When Excel Opens a CSV
Excel keeps 15 significant digits, so a 19 digit ID loses its last four. Why it happens, how to import the column as text, and how to check a file for damage.
6 min read
CSV
Excel CSV UTF8 Versus CSV Comma Delimited Explained
Excel offers five CSV-like save formats. Here is what each writes, why plain CSV Comma Delimited loses characters, and which one to pick for each reader.
6 min read
CSV
Importing a CSV Into Google Sheets With the Right Types
Import a CSV into Google Sheets without losing zeros or flipping dates: the import dialog, the locale, IMPORTDATA and an Apps Script that keeps text.
6 min read
CSV
Saving an Excel Sheet as CSV Without the Extra Commas
Excel adds trailing commas and blank rows to a CSV when empty cells outside your data hold formatting. Find the used range, clear it and check the file.
7 min read
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
JSON
Converting JSON to CSV With Python and pandas Safely
Convert JSON to CSV with pandas and the csv module, and avoid the traps: Python repr in cells, True and False, ragged keys, Excel encoding and formula cells.
8 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
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
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
Get new articles by email
We send one email when a new article is published. No spam.