Csv file written with Python has blank lines between each row

python

csv-writing

python-quirks

file-handling

byNikita Barsukov·Sep 5, 2024

To eliminate blank lines in a CSV file when using Python on Windows, set the newline parameter to an empty string '' when opening the file. This stops the CSV module from adding extra newline characters:

import csv

data = [['Header1', 'Header2'], ['Row1Col1', 'Row1Col2'], ['Row2Col1', 'Row2Col2']]

with open('output.csv', 'w', newline='') as file:
    # Writes data to a file, no newline nonsense here
    csv.writer(file).writerows(data)

Always use open with newline='' to prevent sneakily snuck-in line breaks in your CSV output.

Addressing version-specific CSV writing quirks

Let's dive deeper and tackle some CSV writing gotchas specific to different Python versions and use-cases.

Python 2: Beware of unexpected blank lines

If you're coding in Python 2, remember to open your files in binary mode using wb instead of w. This ward-off unnecessary newline translation causing those annoying blank lines.

# "No extra newlines" password for Python 2:
with open('output.csv', 'wb') as file:
    # Can't have those extra newlines sneaking in, can we?
    csv.writer(file).writerows(data)

Unicode handling in Python 2

Is your CSV data rich in Unicode strings? Fear not my friend, unicodecsv module comes to your rescue when you're using Python 2.

import unicodecsv as csv

with open('output.csv', 'wb') as file:
    # Now that's what I call talking in tongues!
    csv.writer(file).writerows(data)

Custom line terminator control

Default settings are comfortable, but sometimes you may want to take control of the line terminator. Go ahead, be the boss of your line endings!

# Hit the newline control, captain!
with open('output.csv', 'w', newline='') as file:
    # \n, \r\n... so many choices!
    csv.writer(file, lineterminator='\n').writerows(data)

StringIO - The in-memory CSV lifesaver

Building up a CSV in memory before hitting the disk? StringIO is your new best friend:

import csv
from io import StringIO

output = StringIO()
#writing to memory. Save the trees!
csv.writer(output).writerows(data)
contents = output.getvalue()
output.close()

# Finally, writing the result to a file when you're ready
with open('output.csv', 'w', newline='') as file:
    # Phew! Finally!
    file.write(contents)

Python 3.10+: The `Path` to easy file writing

If you're on Python 3.10 or newer, enjoy easy and succinct file handling with the Path object from pathlib:

from pathlib import Path

output_path = Path('output.csv')
# write_text, because who needs complexity, right?
output_path.write_text(contents, newline='')

Catering to various use cases

Now let's add more tools to your CSV-writing toolkit for a range of scenarios:

For Excel files, just switch the `newline`

When your CSV files are going to be read by Excel, stick to the newline='' rule because Excel likes newlines with \r\n.

with open('for_excel.csv', 'w', newline='') as file:
    # Writes Excel-friendly data, no extra newlines
    csv.writer(file).writerows(data)

Quoted Fields and CSV

We've all seen CSV fields perfectly encapsulating line breaks within quotes. To parse these correctly, it's vital to specify newline=''.

with open('quotes.csv', 'w', newline='') as file:
    # Writes data with line breaks inside quotes
    writer = csv.writer(file)
    writer.writerow(['a', '1', 'First\nLine'])
    writer.writerow(['b', '2', 'Second\nLine'])

Large data and memory efficiency

When crunching large data sets, it's more memory-efficient to write results incrementally rather than building up the entire result in memory:

with open('large_file.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    # Write rows one at a time, because Rome wasn't built in a day!
    for row in generate_large_data():
        writer.writerow(row)

Use pathlib.Path for modern file handling

With the modern Path module, handling file operations becomes a breeze, due to its precise and readable syntax:

from pathlib import Path

output_file = Path('output.csv')

with output_file.open('w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(data)