Guides
CSV Guides
Practical answers to the problems that come up when CSV files get large or messy.
Every guide here starts from a symptom you can see on screen — a warning, a column of run-together text, a number that has turned into 4.5E+15 — and explains what the program is actually doing before it gets to the fix. Where a job is quicker with a tool than by hand, the guide says so and links to it.
4 guides
When the File Won’t Open Properly
Excel shows an error, loads only part of the file, or the computer struggles to open it at all.
- Why a CSV File Won’t Open in Excel, and How to Fix ItError messages, half-loaded files and scrambled rows: what each symptom means, tested in Excel 2021.
- How to Open a CSV File That Is Too Large for ExcelSix ways to work with a CSV that has millions of rows, from free tools to one line of code.
- Excel’s Row Limit: What Happens after Row 1,048,576The exact limits of Excel and Google Sheets, why data goes missing, and how to avoid losing it.
- How to Open a Large CSV File on a MacExcel for Mac, Numbers, Terminal and DuckDB: what each can handle, and how to split what they cannot.
5 guides
When the File Is Over a Limit
Google Sheets, an upload form or an email refuses it, and it has to get smaller or be split.
- Is There a CSV File Size Limit?The format has none. Excel, Google Sheets, email and import tools do — the real numbers, with sources.
- What to Do When a CSV Is Too Large for Google SheetsSheets counts cells, not rows, and the limit is doubling to 20 million. How to tell if a file fits.
- How to Split a CSV to Fit an Import LimitImport tools cap rows or file size. How to work out the right split, with current published limits.
- How to Reduce the Size of a CSV FileEvery common trick measured on a 99.4 MB file: which shrink it, which barely help, which make it bigger.
- How to Count the Rows in a CSV FileWhy line counts and row counts differ, and how to get the real number on a file of any size.
3 guides
When Excel Changes Your Values
The file opens fine, but the values in it are no longer the ones you exported.
- Stop Excel Removing Leading Zeros from CSV FilesWhy ZIP codes, IDs and phone numbers lose their zeros in Excel, and four ways to keep them.
- Stop Excel Turning Long Numbers into 4.5E+15Why card numbers, IMEIs and long IDs get rounded to 15 digits, and how to keep every digit.
- How to Convert a CSV to Excel without Breaking Your DataOpening a CSV and saving as .xlsx silently changes values. Here is the import route that does not.
3 guides
Encoding and Delimiter Problems
Every row lands in one column, or accented characters arrive as nonsense.
- Why Your CSV Opens in One Column in ExcelThe delimiter and regional-settings mismatch behind it, and four ways to get proper columns back.
- UTF-8, UTF-16 and Windows-1252: Which One Is Your CSV?How to tell which encoding a file uses, what the BOM does, and why Excel needs one.
- CSV UTF-8 vs CSV in Excel: Which One to Save AsWhat each of Excel’s four CSV formats writes, byte by byte, and which one to choose.
5 guides
Combining, Splitting and Cleaning Files
Several exports have to become one, or one has to become several, without duplicates or shifted columns.
- How to Merge CSV Files That Have Different ColumnsCombining exports whose headers changed over time, without shifting values into the wrong column.
- How to Merge CSV Files in Command Prompt, PowerShell or BashWhat copy, Import-Csv, cat and awk really do to merged files — tested — and the versions that work.
- How to Combine CSV Files into One Excel Workbook, One Sheet EachBy hand, with Power Query or with a tested script — without losing leading zeros or overwriting sheets.
- How to Split a Large CSV File with PowerShell, Bash or PythonTested scripts that keep the header in every part, and the line-counting trap that cuts rows in half.
- How to Remove Duplicate Emails from a CSV ListExact duplicates, case differences and stray spaces — and why deduping before an import matters.
FAQ
Questions About CSV Files
What exactly is a CSV file?
A CSV is a plain text file in which each line is a record and a separator character — usually a comma — marks the boundary between fields. There is no formatting, no formulas, no column types, and nowhere in the file to record which character encoding or delimiter it uses. That last gap is the root of nearly every problem covered in these guides.
What is the difference between a CSV file and an .xlsx file?
A CSV holds text only; an .xlsx workbook holds text plus types, formats, formulas, several sheets and charts. That difference is why opening a CSV in Excel is lossy in one direction: Excel has to guess a type for every value, and it guesses wrongly on ZIP codes, long IDs and date-like codes. Converting a CSV to Excel covers the import route that avoids the guessing.
Is my CSV corrupted, or is the program just reading it wrongly?
Almost always the second. Garbled accents, everything landing in column A, and long numbers shown as 4.5E+15 are produced by the program reading the file, not written into the file. The bytes on disk are usually intact, which is why one export can look perfect in a text editor and wrong in Excel. The common exception is genuinely missing quoting in the original export, which no reader can undo.
What is RFC 4180, and does my CSV have to follow it?
RFC 4180 is the closest thing CSV has to a specification, and it is what the tools here follow. It says a field containing a comma, a double quote or a line break is wrapped in double quotes, and that the last record may or may not end with a line break. Nothing enforces it and plenty of real exports break it — but files that follow it survive being read by other programs.
Why does the same CSV look different in Excel, Google Sheets and a text editor?
Because each one guesses differently. A text editor shows the raw characters and guesses only the encoding. Excel splits on your Windows list separator and converts anything that looks like a number or a date. Google Sheets asks you for a separator and offers to convert types. The file does not change between them; three programs simply apply three sets of assumptions to the same bytes.
Which guide should I read first if my CSV will not open properly?
Start from the symptom. Everything in one column is a delimiter problem: why your CSV opens in one column. Strange accented characters are an encoding problem: UTF-8, UTF-16 and Windows-1252. A warning that the file was not loaded completely is a size problem: opening a CSV too large for Excel.