Why does Windows use CRLF at all?

It inherited it from teletype hardware, where carriage return moved the print head back to the left and line feed advanced the paper. DOS kept both characters, Unix kept only the line feed, and classic Mac OS kept only the carriage return. Every CSV that crosses those worlds inherits the argument.

No. RFC 4180 actually specifies CRLF as the line break for CSV. In practice almost every modern parser accepts LF, CRLF and lone CR, so the safest thing you can do is be consistent within one file rather than picking a side.

Why would a blank row appear between every single row?

Something wrote CRLF and something else added its own line feed, giving CR LF LF. A parser that treats every LF as a row break sees an empty row after each real one. It is a double-conversion bug, usually from a script that opened the file in text mode on the wrong platform.

My whole file loads as a single row. Same cause?

Usually yes. The file uses lone CR line endings, which some parsers do not treat as a break at all, so every row is glued into one very long line. It can also mean an unterminated quote swallowed the rest of the file.

Do trailing blank rows matter?

They matter to anything that counts rows or computes an average. A single trailing newline is normal and produces no row. Several trailing newlines produce empty rows that many tools will happily include in a COUNT.

Guide September 3, 2026 by Arif Aslam 6 min read

Your CSV has extra blank rows. It is almost always line endings.

Three complaints that sound different and share one root cause: a blank row between every row of data, a file that arrives as one endless line, and a handful of empty rows hanging off the bottom. All three are about the bytes a program writes at the end of a line, and none of them are visible in a spreadsheet.

The characters in question are carriage return, hex 0D, written \r, and line feed, hex 0A, written \n. Windows ends a line with both, in that order. Linux and modern macOS use the line feed alone. Classic Mac OS, which still shows up in files exported by very old accounting software, used the carriage return alone.

On this page: a blank row between every row · the whole file on one line · blank rows at the end · blank rows that are not blank · check what is really in the file

Symptom: a blank row between every row of data

Cause. The file contains a carriage return followed by two line feeds, or a carriage return and line feed where the reader counts both characters as separate row breaks. The classic way to produce this is to open a file in text mode on Windows in a language that already translates \n to \r\n on write, then write \r\n yourself. Python's csv module is famous for this, which is why its documentation tells you to open the file with newline="".

You can confirm it without a hex editor. On macOS or Linux:

head -c 200 yourfile.csv | od -c | head

Healthy Windows output looks like ... \r \n o r d e r .... A doubled file shows \r \n \n at every break, and that extra \n is your blank row.

Fix. Normalize the line endings once and rewrite the file. Do not try to delete the blank rows by hand in a spreadsheet; you will fix the display and leave the bytes wrong, so the next export has the same problem. If you own the script that wrote the file, fix the write mode there. If the file came from someone else, re-write it through a tool that emits one consistent line ending.

Fix it: re-write the file with clean, consistent line endings →

Symptom: the entire file loads as one row

Cause. Two very different problems produce this, and they need different fixes.

The first is lone carriage returns. A file written by classic Mac software, or by a mainframe export that used \r only, has no line feeds at all. Parsers that split on \n find exactly one line, and it is the whole file.

The second is an unterminated quote. If a value opens with a double quote and never closes it, everything after that point, including every newline, is inside a single field as far as the parser is concerned. That gives you one giant row that starts partway through the file.

Telling them apart is easy: if the collapse starts at the very first row, it is line endings. If the file looks fine for 4,000 rows and then everything after row 4,001 is in one cell, it is a quote that never closed at row 4,001.

Fix. For lone carriage returns, re-write the file with a normalizing tool. For an unterminated quote, find the offending row. Our loader is deliberately loud about this class of failure. It has a check that fires when a CSV appears to have parsed successfully but produced either one column whose name contains the delimiter, or one or two rows out of a file with many physical lines. Both of those are the signature of a parse that quietly lost, and the file gets re-read with more forgiving options rather than handed to you as garbage.

Fix it: find the row where quoting breaks →

Symptom: empty rows at the bottom of the file

Cause. A file that ends with a single newline is correct and produces no extra row. A file that ends with several newlines, or with a row of nothing but delimiters like ,,,,, produces rows that exist but contain nothing.

The delimiter-only variety usually comes from Excel. If someone once typed in row 5,000 of a sheet and later deleted the content, Excel may still consider that row part of the used range and write it out as an all-empty row. That row then survives every downstream hop.

Fix. Filter them out explicitly rather than hoping the next tool ignores them. In SQL that is a single predicate against whichever column must always have a value:

SELECT *
FROM data
WHERE order_id IS NOT NULL
  AND TRIM(order_id) <> '';

The TRIM matters. An "empty" cell that actually holds a space is not NULL, and it will pass an IS NOT NULL check while still being empty to a human.

Fix it: filter out the empty rows and export a clean file →

Symptom: rows that look blank but are not

Cause. A perfectly valid CSV row can span several physical lines, because a newline inside a quoted field is legal. When you open such a file in a text editor, the wrapped lines look like broken, half-empty rows. They are not. The parser is right and the editor is showing you bytes.

ticket,summary,status
4471,"Login fails after SSO
redirect loop on Safari",open
4472,Password reset email delayed,closed

Four physical lines, two data rows. Deleting the "blank" second line would destroy ticket 4471.

Fix. Nothing to fix in the file. Fix the tool you are reading it with. If a downstream system genuinely cannot handle embedded newlines, replace them with a literal marker before you send the file, rather than stripping the quoting:

SELECT ticket,
       REPLACE(REPLACE(summary, CHR(13), ' '), CHR(10), ' ') AS summary,
       status
FROM data;

Fix it: open the file in a viewer that parses rather than displays →

Check what is really in the file

The whole class of problems in this guide comes down to one question: how many rows does a parser think this file has, versus how many lines does a text tool think it has? Get both numbers and the diagnosis falls out.

Lines roughly double the rows: doubled line endings, the blank-row-between-every-row case.
Many lines, one or two rows: lone carriage returns, or an unterminated quote.
Rows slightly exceed the data you expect: trailing empties.
Lines exceed rows by a small, irregular amount: embedded newlines inside quoted fields, and nothing is wrong.

Load the file in the validator and it reports the parsed row count, the detected delimiter, and every row whose field count disagrees with the header. That last list is usually where the real culprit is sitting.

Fix it: CSV Validator, ragged rows and line endings in one report →