Python Doc & Data Automation

Fix Blank Rows in CSV Files on Windows

The file is written on Windows, opened in Excel, and every second row is empty:

id,name,amount

1,Dana,1204.50

2,Acme,98.00

Read it back and the blanks are real rows, not a rendering artefact:

>>> len(pd.read_csv("out/report.csv"))
4          # two records, two phantom rows

Nothing wrote empty records. The line endings were doubled, and each \r\r\n sequence reads as a line break followed by an empty line.

Root Cause

Python's text mode performs newline translation. On Windows, writing "\n" to a file opened in text mode emits \r\n. The csv module — and pandas, which uses it — already writes \r\n as the record separator by default, because that is what the CSV specification calls for. Combine the two and every record ends \r\r\n: the module's own \r\n, with its \n translated to \r\n on the way out.

The fix is not to change what the writer emits, but to stop the file object translating it. That is what newline="" does: it disables translation and lets the csv writer control the terminator exactly.

Where the extra carriage return comes from A csv writer emits a record ending with carriage return line feed. On the upper path the file was opened in default text mode, so the line feed is translated to carriage return line feed and the bytes on disk end with carriage return carriage return line feed, which readers see as an extra blank line. On the lower path the file was opened with newline set to the empty string, translation is disabled, and the bytes on disk end with a single carriage return line feed. csv writer emits row + CR LF open(path, "w") translates LF to CR LF CR CR LF on disk reads as a blank row open(path, "w", newline="") no translation CR LF on disk one row per record The writer is correct in both cases — only the file object's translation differs

Minimal Diagnostic

Look at the bytes. Nothing else settles the question, because most editors hide line endings.

# Standard library only
from pathlib import Path

CSV = Path("out/report.csv")

def line_ending_report(csv_path: Path, sample_bytes: int = 4096) -> dict:
    """Count the line-ending sequences actually present in the file."""
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")
    data = csv_path.read_bytes()[:sample_bytes]
    return {
        "CRCRLF": data.count(b"\r\r\n"),        # the doubled ending — the bug
        "CRLF": data.count(b"\r\n") - data.count(b"\r\r\n"),
        "LF_only": data.count(b"\n") - data.count(b"\r\n"),
        "first_line": data.split(b"\n", 1)[0][:80],
    }

if __name__ == "__main__":
    print(line_ending_report(CSV))

{'CRCRLF': 2, 'CRLF': 1, 'LF_only': 0, 'first_line': b'id,name,amount\r'}

Any non-zero CRCRLF count is the confirmed cause. A file that shows only CRLF and still displays blank rows in Excel has a different problem — genuinely empty records, usually from a filtered frame that kept its blank rows.

The Fix

For the csv module, open the file with newline="". This is not Windows-specific advice — it is what the module's documentation requires on every platform, and it makes output byte-identical across operating systems.

# Standard library only
import csv
from pathlib import Path

def write_rows(dest: Path, header: list[str], rows: list[list]) -> Path:
    """Write a CSV whose line endings are exactly what the csv module chose."""
    dest.parent.mkdir(parents=True, exist_ok=True)
    # newline="" disables the file object's own newline translation
    with dest.open("w", encoding="utf-8", newline="") as fh:
        writer = csv.writer(fh)
        writer.writerow(header)
        writer.writerows(rows)
    return dest

if __name__ == "__main__":
    path = write_rows(Path("out/report.csv"), ["id", "name", "amount"],
                      [[1, "Dana", 1204.50], [2, "Acme", 98.00]])
    print(path.read_bytes()[:40])
    # b'id,name,amount\r\n1,Dana,1204.5\r\n2,Acme,98.0\r\n'

For pandas, pass the path directly rather than a hand-opened file object — to_csv opens it correctly itself:

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

frame = pd.DataFrame({"id": [1, 2], "name": ["Dana", "Acme"], "amount": [1204.5, 98.0]})

# Correct: pandas manages the file object
frame.to_csv(Path("out/report.csv"), index=False)

# Also correct when you must control the handle (e.g. appending)
with Path("out/report.csv").open("a", encoding="utf-8", newline="") as fh:
    frame.to_csv(fh, header=False, index=False)

Handing to_csv a file object opened without newline="" reintroduces the bug — a common shape in append loops and in the chunked writes described in handling large CSV files with chunking.

Variant Fix 1: Choose the Line Terminator Explicitly

When the consumer is a Unix system or a tool that dislikes \r, set the terminator rather than relying on the platform default:

# pip install "pandas>=2.2,<3"
import pandas as pd

frame.to_csv("out/unix.csv", index=False, lineterminator="\n")     # LF only
frame.to_csv("out/excel.csv", index=False, lineterminator="\r\n")  # explicit CRLF

Excel reads both. Most Unix tooling prefers \n. Being explicit means the same code produces the same bytes on a developer laptop and on a Linux build agent, which matters when output is compared against a checked-in fixture.

Variant Fix 2: Blank Rows That Are Real

If the byte report shows clean CRLF and blank rows persist, the frame genuinely contains empty records — usually rows that were filtered to all-NaN rather than dropped:

# pip install "pandas>=2.2,<3"
import pandas as pd

frame = pd.read_csv("data/source.csv")
print("all-empty rows:", frame.isna().all(axis=1).sum())

cleaned = frame.dropna(how="all")           # drop rows where every field is missing
cleaned = cleaned.loc[:, ~cleaned.columns.str.startswith("Unnamed:")]
cleaned.to_csv("out/report.csv", index=False)

Empty trailing rows often come from a spreadsheet whose used range extends past the data. Reading with the header-detection approach in fix unnamed columns in pandas read_excel removes them at the source.

Variant Fix 3: Embedded Newlines Inside Fields

A field containing a newline — a multi-line address, a comment — is legal when quoted, and it will look like a blank row to anything that splits on line breaks without honouring quotes.

# Standard library only
import csv
from pathlib import Path

def rows_with_embedded_newlines(csv_path: Path) -> list[int]:
    """Record numbers whose fields contain a line break."""
    found = []
    with csv_path.open("r", encoding="utf-8", newline="") as fh:
        for number, row in enumerate(csv.reader(fh), start=1):
            if any("\n" in field or "\r" in field for field in row):
                found.append(number)
    return found

if __name__ == "__main__":
    print(rows_with_embedded_newlines(Path("data/source.csv")))

Keep them if the data needs them and the consumer parses CSV properly; replace them with a space when the file feeds a line-oriented tool:

# pip install "pandas>=2.2,<3"
text_columns = frame.select_dtypes("object").columns
frame[text_columns] = frame[text_columns].apply(
    lambda col: col.str.replace(r"[\r\n]+", " ", regex=True)
)
Two different causes of the same symptom Starting from blank rows appearing in a CSV, the byte level check splits the diagnosis. If the file contains carriage return carriage return line feed sequences, the cause is newline translation and the fix is to open with newline set to the empty string. If the endings are clean, the cause is either genuinely empty records, fixed by dropping all-NaN rows, or newlines embedded inside quoted fields, which are legal and only look wrong in line oriented tools. Blank rows seen count the bytes CR CR LF clean CR LF Newline translation open(path, "w", newline="") — or let pandas open the file Genuinely empty rows dropna(how="all") Newlines inside fields legal — strip only if needed The bytes that end each record Three configurations and the bytes they produce at the end of a record. Opening the file in default text mode on Windows produces carriage return, carriage return, line feed, which readers see as a blank line. Opening with newline set to the empty string produces carriage return, line feed, the correct sequence. Passing lineterminator as a bare line feed produces line feed only, which suits Unix consumers. End-of-record bytes, three ways open(path, "w") on Windows 0D 0D 0A — a blank row appears open(path, "w", newline="") 0D 0A — one row per record lineterminator="\n" 0A — Unix-friendly, Excel still reads it Only the first row is a defect; the other two are deliberate choices

Verification

Assert on the bytes and on the round-tripped row count, so the check works the same on every platform.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def assert_clean_csv(csv_path: Path, expected_rows: int) -> None:
    """Fail on doubled line endings, phantom rows or a row-count mismatch."""
    data = csv_path.read_bytes()
    assert b"\r\r\n" not in data, "doubled line endings — file opened without newline=''"

    frame = pd.read_csv(csv_path)
    assert len(frame) == expected_rows, (
        f"read back {len(frame):,} row(s), expected {expected_rows:,}"
    )
    empty = frame.isna().all(axis=1).sum()
    assert empty == 0, f"{empty} all-empty row(s) in the output"
    print(f"{csv_path.name}: {len(frame):,} clean row(s)")

if __name__ == "__main__":
    df = pd.DataFrame({"id": [1, 2], "name": ["Dana", "Acme"]})
    df.to_csv("out/report.csv", index=False)
    assert_clean_csv(Path("out/report.csv"), expected_rows=len(df))

Running this in CI on both Linux and Windows agents is what stops the bug returning: the doubled ending only appears on Windows, so a Linux-only test suite will never see it. Pair it with the index and encoding checks from exporting data to CSV formats for a complete export contract.

FAQ

Why does newline="" look wrong? Surely that means no newline. It means "do not translate newlines" — the empty string disables the translation layer rather than setting the terminator. The csv writer still emits its own \r\n.

Do I need it on Linux and macOS? The bug does not appear there, but the argument is still correct and makes output identical across platforms. Writing it unconditionally costs nothing and removes a platform-specific failure mode.

Does this affect reading as well? Yes. Open files for csv.reader with newline="" too, so that newlines embedded in quoted fields are handled by the reader rather than the file object.

Why does Excel sometimes show the blank rows and sometimes not? Different Excel versions and locales tolerate stray carriage returns differently, and a file that renders fine on one machine can show gaps on another. Fix the bytes rather than testing against one viewer.

Is lineterminator or line_terminator the argument name?lineterminator on pandas 2.x. The older line_terminator spelling was deprecated in 1.5 and removed in 2.0.

Why This Survives Code Review

The defect is unusually persistent for its size, and the reason is worth naming. The bug is invisible on the machines where most review happens: a developer on macOS or Linux writes the code, tests it, sees clean output and merges it. It only appears when the same code runs on a Windows host or a Windows build agent, by which point the change is several commits old and the connection between the two is not obvious.

It is also invisible in most diffs. open(path, "w") and open(path, "w", newline="") look equally reasonable, and the argument that prevents the bug reads like a no-op to anyone who has not hit it. Nothing in the code says "this is the line that stops every second row being blank".

Two habits remove it permanently. Put the byte-level assertion from the verification section into the test suite, and run that suite on a Windows agent as well as a Linux one — the assertion costs microseconds and fails loudly on exactly the platform where the defect appears. And prefer letting pandas own the file handle: frame.to_csv(path) opens the file correctly every time, so the only code that needs the argument is code that deliberately manages the handle for appending or streaming, which is a much smaller surface to review.

Part of Exporting Data to CSV Formats.