Reading and Writing CSV Files in Python

Introduction

A CSV (Comma Separated Values) files is a type of plain text file that uses specific structuring to arrange tabular data. Generally comma is used to separate each specific data value. CSV format is the most common import and export format for spreadsheets and databases. csv module in Python implements classes for reading and writing tabular data in CSV format.

In the below sample data, elements of a CSV file are separated by commas. Comma(,) is called delimiter. Any single character can be used as delimiter.

// CSV sample data
SN, Country,  Capital
1,  India,    New Delhi
2,  Japan,    Tokyo

Reading CSV File

CSV file is opened as a text file using built-in open() function. Opened file object is passed to csv.reader() function of csv module. Syntax of reader() is

csv.reader(csvfile, dialect='excel', **fmtparams)

It return a reader object which will iterate over lines in the given csvfile. Each row read from the csv file is returned as a list of strings. Optional dialect parameter is used to define a set of parameters specific to a particular CSV dialect. Other optional fmtparams keyword arguments can be given to override individual formatting parameters in the current dialect. Some of the optional CSV reader parameters are

• delimiter : Specifies the character used to separate each field. Default is the comma.
• quotechar : Specifies the character used to surround fields that contain the delimiter character. Default is a double quote.
• escapechar : Specifies the character used to escape the delimiter character, in case quotes aren’t used. Default is no escape character.

To get list of dialects, use list_dilects().

import csv

# List the dialects
print(csv.list_dialects())

# Output
# ['excel', 'excel-tab', 'unix']

Below example shows reading of CSV file, having space as delimiter and ‘|’ as quote character. If csvfile is a file object, it should be opened with newline=”.

import csv

'''
# Content of data.txt

|SN| |Country| |Capital|
|1| |India| |New Delhi|
|2| |Japan| |Tokyo|
'''
with open('data.txt', newline='') as csvfile:
  reader = csv.reader(csvfile, delimiter=' ', quotechar='|')

  for row in reader:
    print(', '.join(row))

# Output
# SN, Country, Capital
# 1, India, New Delhi
# 2, Japan, Tokyo

Sniffer class is used to deduce the format of a CSV file. sniff() analyze CSV file and return a Dialect subclass reflecting the parameters found. has_header() function analyze the CSV file and return True if the first row appears to be a series of column headers.

import csv

with open('StockPrice.csv', newline='') as csvfile:

    reader = csv.reader(csvfile)

    dialect = csv.Sniffer().sniff(csvfile.read(1024))
    csvfile.seek(0)

    hasHeader = csv.Sniffer().has_header(csvfile.read(1024))

    print("Has Header : " + str(hasHeader))
    print("Delimiter : " + str(dialect.delimiter))
    print("Escape Char : " + str(dialect.escapechar))
    print("Quote Char : " + str(dialect.quotechar))

# Output
# Has Header : True
# Delimiter : ,
# Escape Char : None
# Quote Char : "

Writing CSV File

To write to a CSV file, we can use the csv.writer() function. It returns a writer object that converts the user’s data into a delimited string. Syntax of writer()

csv.writer(csvfile, dialect='excel', **fmtparams)

Return a writer object responsible for converting the user’s data into delimited strings on the given file like object. If csvfile is a file object, it should be opened with newline=” 1. Optional dialect parameter define a set of parameters specific to a particular CSV dialect. The value None is written as the empty string.

Below example shows the use of writer() function.

# Writing Comma Separate Values files with Python
import csv

# Write data to a CSV file
with open("SampleData.csv", mode="w") as csvfile:
    # create a csv writer
    csvWriter = csv.writer(csvfile)

    # write the header
    csvWriter.writerow(["Name", "Department", "Location"])

    # write a few rows
    csvWriter.writerow(["Ram", "Finance", "Hyderabad"])
    csvWriter.writerow(["Om", "Payroll", "New Delhi"])
    csvWriter.writerow(["Vishnu", "Human Resources", "Patna"])