How to Prevent Blank Lines in Python CSV Output to Excel?

Python CSV Output: Avoid Blank Lines Between Rows

When writing a CSV file in Python, encountering blank lines separating each row in Microsoft Excel can be frustrating. This issue arises due to the way csv.writer handles line endings.

In Python 3, opening the output file in text mode ('w') will cause Windows to translate each newline ('n') into a carriage return and newline ('rn'). To prevent this, the file should be opened in untranslated text mode with the parameter newline='' (empty string):

with open('thefile_subset11.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)

Alternatively, using the Path module's open() method also allows for specifying the newline parameter:

from pathlib import Path
with Path('thefile_subset11.csv').open('w', newline='') as outfile:
    writer = csv.writer(outfile)

In Python 2, the solution is to open the output file in binary mode ('wb') instead of text mode ('w'):

with open('thefile_subset11.csv', 'wb') as outfile:
    writer = csv.writer(outfile)

For in-memory operations using StringIO, the resulting string will contain the Windows-specific line terminator ('rn'). When writing this string to a file, remember to use newline='':

from io import StringIO
s = StringIO()
writer = csv.writer(s)
writer.writerow([1, 2, 3])
with open('thefile_subset11.csv', 'w', newline='') as f:
    f.write(s.getvalue())

The above is the detailed content of How to Prevent Blank Lines in Python CSV Output to Excel?. For more information, please follow other related articles on the PHP Chinese website!