Offensive Security Researcher & Quantum Cryptography Analyst

Writing to CSV from String or List in Python: Complete CSV File Handling and Data Export Guide

Abdul Wahab Junaid
Writing to CSV from String or List in python

Writing to CSV from String or List in python

Exporting data to CSV feels trivial until you hit a value with a comma in it, or a Windows user complains about mysterious blank lines between every row of a file I generated on Linux. I’ve written CSV export code more times than almost any other kind of file-handling task, whether the source data started life as a list of lists, a list of dicts, or even a raw comma-separated string that needed reformatting. This guide covers all of those starting points and the details that separate CSV code that “mostly works” from CSV code that’s actually correct.

Why Not Just Use write() with Commas Manually?

I did this once, early on, and regretted it almost immediately.

# DON'T do this - breaks the moment a field contains a comma or quote
data = [['Name', 'City'], ['Alice', 'New York'], ['Bob, Jr.', 'Austin']]

with open('bad_output.csv', 'w') as file:
    for row in data:
        file.write(','.join(row) + '\n')

That second row — "Bob, Jr.","Austin" — needs its comma escaped with quotes, and manual string joining has no idea how to do that. Python’s csv module handles this correctly and automatically, which is exactly why I never hand-roll CSV writing anymore.

Writing from a List of Lists

import csv

data = [
    ['Name', 'Age', 'City'],
    ['Alice', 30, 'New York'],
    ['Bob, Jr.', 25, 'Austin'],
    ['Charlie "The Rock" Smith', 35, 'Chicago']
]

with open('output.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerows(data)

Resulting file content:

Name,Age,City
Alice,30,New York
"Bob, Jr.",25,Austin
"Charlie ""The Rock"" Smith",35,Chicago

Notice how csv.writer automatically wrapped "Bob, Jr." in quotes because it contains a comma, and escaped the internal quotes in Charlie "The Rock" Smith by doubling them ("") — this is the standard CSV escaping convention, and I never have to think about implementing it myself.

The newline=” Parameter: Non-Negotiable on Every Platform

I mentioned this mistake in a previous guide, but it’s important enough to repeat specifically here since it’s the single most common CSV bug I see. Without newline='', on Windows, Python’s default text-mode newline translation converts every \n the csv module writes into \r\n, but the csv module also writes its own \r\n line terminators internally — the combination produces doubled line endings, showing up as blank lines between every row when opened in many text editors.

# WRONG - can produce extra blank lines, especially on Windows
with open('output.csv', 'w', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerows(data)

# CORRECT - newline='' lets the csv module manage line endings itself
with open('output.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerows(data)

I now type newline='' reflexively any time I open a file for CSV writing, without exception.

Writing a Single Row at a Time with writerow()

import csv

with open('output.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'Score'])
    writer.writerow(['Alice', 95])
    writer.writerow(['Bob', 87])

I use writerow() for a single row, and writerows() (note the extra “s”) for a list of rows at once — mixing these up is an easy typo that raises confusing errors, since passing a single flat list to writerows() treats each individual element as its own “row.”

Writing from a List of Dictionaries with DictWriter

This is the pattern I use most often in real projects, because data frequently arrives as a list of dictionaries — from a database query, an API response, or JSON — rather than plain lists.

import csv

employees = [
    {'name': 'Alice', 'department': 'Engineering', 'salary': 95000},
    {'name': 'Bob', 'department': 'Sales', 'salary': 72000},
    {'name': 'Charlie', 'department': 'Marketing', 'salary': 68000}
]

with open('employees.csv', 'w', newline='', encoding='utf-8') as file:
    fieldnames = ['name', 'department', 'salary']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(employees)

DictWriter requires fieldnames explicitly because dictionaries in Python don’t have a guaranteed schema the way a database table does — I have to tell it exactly which keys to pull out, and in what column order.

Handling Missing or Extra Keys with DictWriter

Real-world data is rarely perfectly uniform. DictWriter gives me explicit control over what happens when a dict is missing a key, or has extra keys beyond the ones I specified.

import csv

data = [
    {'name': 'Alice', 'age': 30},
    {'name': 'Bob', 'age': 25, 'city': 'Austin'},  # extra key not in fieldnames
    {'name': 'Charlie'}  # missing 'age' key
]

with open('output.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.DictWriter(
        file,
        fieldnames=['name', 'age'],
        restval='N/A',        # value used for missing keys
        extrasaction='ignore'  # silently drop keys not in fieldnames, instead of raising an error
    )
    writer.writeheader()
    writer.writerows(data)

Without extrasaction='ignore', DictWriter raises a ValueError the moment it encounters a dict with an unexpected key — I actually appreciate this strict default behavior, since it catches schema mismatches early rather than silently writing malformed output, but I override it deliberately when I know extra keys are expected and safe to drop.

Writing CSV from a Raw String

Sometimes the data I’m working with starts as a single delimited string — pasted from somewhere, or extracted from a log line — and I need to parse and re-write it properly.

import csv
import io

raw_string = "Alice,30,New York\nBob,25,Austin\nCharlie,35,Chicago"

# Parse the string as if it were a CSV file, using io.StringIO to treat it like one
reader = csv.reader(io.StringIO(raw_string))
rows = list(reader)
print(rows)

with open('from_string.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'Age', 'City'])
    writer.writerows(rows)

io.StringIO is the key tool here — it wraps a plain string in a file-like interface, letting me hand it directly to csv.reader(), which normally expects a real file object.

Writing CSV to a String Instead of a File

The reverse is useful too — generating CSV content as an in-memory string, for cases like attaching CSV data to an email or returning it directly from a web API response, without ever touching the disk.

import csv
import io

data = [['Name', 'Score'], ['Alice', 95], ['Bob', 87]]

output = io.StringIO()
writer = csv.writer(output)
writer.writerows(data)

csv_string = output.getvalue()
print(csv_string)

Custom Delimiters and Dialects

Not every “CSV-like” file actually uses commas. Tab-separated values, semicolon-separated (common in European locales where comma is the decimal separator), and pipe-separated formats are all common variations.

import csv

data = [['Name', 'Age'], ['Alice', 30], ['Bob', 25]]

with open('tab_separated.tsv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file, delimiter='\t')
    writer.writerows(data)

with open('semicolon.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file, delimiter=';')
    writer.writerows(data)

For settings I reuse often, I register a named dialect rather than repeating the same parameters everywhere:

import csv

csv.register_dialect('pipe_dialect', delimiter='|', quoting=csv.QUOTE_MINIMAL)

with open('pipe_separated.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file, dialect='pipe_dialect')
    writer.writerows(data)

Quoting Behavior

The csv module gives me four quoting strategies, controlling exactly when field values get wrapped in quotes:

import csv

data = [['Name', 'Notes'], ['Alice', 'Likes, commas'], ['Bob', '5']]

# QUOTE_MINIMAL (default): quote only fields containing special characters
with open('minimal.csv', 'w', newline='') as f:
    csv.writer(f, quoting=csv.QUOTE_MINIMAL).writerows(data)

# QUOTE_ALL: quote every single field, regardless of content
with open('all.csv', 'w', newline='') as f:
    csv.writer(f, quoting=csv.QUOTE_ALL).writerows(data)

# QUOTE_NONNUMERIC: quote all non-numeric fields
with open('nonnumeric.csv', 'w', newline='') as f:
    csv.writer(f, quoting=csv.QUOTE_NONNUMERIC).writerows(data)

I use QUOTE_ALL specifically when the receiving system is known to be picky about type inference on unquoted fields (some spreadsheet tools misinterpret unquoted numeric-looking strings), and stick with the sensible QUOTE_MINIMAL default otherwise.

Internal Working: How csv.writer Formats a Row

When writerow() is called, the writer object iterates over each field in the row, converts non-string values to their string representation, and then checks whether that string needs quoting based on the active dialect’s quoting rule and whether the field contains the delimiter, the quote character, or a newline. If quoting is needed, it wraps the field in the quote character and doubles any internal occurrence of that quote character to escape it, following the same convention used by the vast majority of CSV-producing and CSV-consuming tools, which is why files written by Python’s csv module open correctly in Excel, Google Sheets, and other CSV parsers without special configuration.

Performance Considerations for Large Exports

For very large datasets, I use writerows() with a generator rather than materializing the entire dataset as a list first, keeping memory usage bounded:

import csv

def generate_rows(n):
    for i in range(n):
        yield [i, f"item_{i}", i * 1.5]

with open('large_output.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerow(['id', 'name', 'value'])
    writer.writerows(generate_rows(1_000_000))

Because writerows() accepts any iterable, not just a list, passing a generator here means rows are produced and written one at a time rather than all held in memory simultaneously — essential when exporting millions of records.

For genuinely large-scale tabular exports, I also compare against pandas.DataFrame.to_csv(), which is implemented with significant internal optimization and often outperforms manual csv.writer loops for large, already-tabular data:

import pandas as pd

df = pd.DataFrame({'id': range(1_000_000), 'value': range(1_000_000)})
df.to_csv('large_output.csv', index=False)

Common Mistakes I’ve Made

Real-World Use Cases

  1. Exporting database query results to a CSV file for a business user to open in Excel.
  2. Generating downloadable reports from a web application’s backend.
  3. Converting API JSON responses into CSV for spreadsheet-based analysis.
  4. Batch data migration — reading from one system’s export format and writing to another’s expected import format.

FAQs

Why does my CSV file have a blank line after every row? Almost certainly a missing newline='' argument when opening the file, especially on Windows.

What’s the difference between csv.writer and csv.DictWriter? csv.writer works with plain lists/tuples representing each row. csv.DictWriter works with dictionaries, mapping specified fieldnames keys to columns, and requires an explicit header write via writeheader().

How do I write CSV data without ever touching disk? Use io.StringIO() as the file-like target passed to csv.writer(), then call .getvalue() to retrieve the resulting string.

Does csv.writer handle Unicode/special characters correctly? Yes, as long as you open the file with an explicit, appropriate encoding (encoding='utf-8' in almost all modern cases).

How do I write tab-separated or semicolon-separated files instead of comma-separated? Pass a delimiter argument to csv.writer(), e.g. delimiter='\t' for tabs or delimiter=';' for semicolons.

Summary

Writing CSV files in Python from lists, dicts, or raw strings all funnel through the same reliable csv module, which correctly handles quoting, escaping, and delimiters so I never have to reimplement that logic myself. The details that actually matter in practice — newline='', choosing writer versus DictWriter based on the source data’s shape, and using generators for large exports — are small but consistently save real debugging time down the line. Once these became habits rather than things I had to remember, CSV export stopped being a source of “why does this file look wrong in Excel” bugs entirely.

References

Exit mobile version