CSV (Comma Separated Values) Handling in Python

What is CSV File

• A Comma Separated Values (CSV) file is a plain text file that contains the comma-separated data.
• These files are often used for exchanging data between different applications.
• CSV files are usually created by programs that handle huge amounts of data. They are used to export data from spreadsheets (ex:- excel file) and databases (Ex:- Oracle, MySQL). It can be used to import data into a spreadsheet or a database.

CSV File Structure

## sample.csv file structure

Name, DOB, City
Ram, 12-Jul-2001, Delhi
Mohan, 23-Jan-2005, Delhi
Suraj, 17-Dec-2002, Kolkata

Python CSV module

• CSV Module is available in Python Standard Library.
• The CSV module contains classes that are used to read and write tabular form of data into CSV format.
• To work with CSV Files, programmer have to import CSV Module.

import csv

Methods of CSV Module :

• writer( ) 
• reader( )

Both the methods return an Object of writer or reader class. Writer Object again have two methods  – writerow( ) , writerows( ).

writer( ) Methods

This function returns a writer object which is used for converting the data given by the user into delimited strings on the file object.

W_obj = csv.writer(csvfile, quotechar='"', delimiter=',',
lineterminator='\r\n', quoting=csv.QUOTE_MINIMAL )

writer( ) Object Methods –

• w_obj . writerow( <Sequence> ) : Write a Single Line
• w_obj . writerows ( <Nested Sequence> ) : Write Multiple Lines
  Example:-

  ## writerow()
  import csv
  row=['Nikhil', 'CEO', '2', '9.0']
  f=open("myfile.csv", 'w')
  w_obj = csv.writer(f)
  w_obj.writerow(row)
  f.close()
  
  
  ## writerows()
  import csv
  rows = [['Nikhil','CEO','2','9.0'],
          ['Sanchit','CEO','2','9.1']]
  f=open("myfile.csv",'w')
  w_obj = csv.writer(f)
  w_obj.writerows(rows)
  f.close()
  
  

reader( ) Methods

This function returns a reader object which will be used to iterate over lines of a given CSV file.
r_obj = csv.reader( csvfile_obj )

To access each row, we have to iterate over this Object.
for i in r_obj:
    print(i)

Example:-

import csv
f=open("myfile.csv",'r')
r_obj = csv.reader(f)
for data in r_obj:
    print(data)
f.close()

If we consider the sample.csv file given above in the CSV file structure the output of the above code will be:

## OUTPUT

['Name', 'DOB', 'City']
['Ram', '12-Jul-2001', 'Delhi']
['Mohan', '23-Jan-2005', 'Delhi']
['Suraj', '17-Dec-2002', 'Kolkata']

Problem with writer( ) and Solution

• Extra Newline after each line in CSV File.
• To remove this we have to Open the CSV File with newline Parameter as Empty '' string:
  f=open("myfile.csv", 'w', newline = '')
• New Code:

import csv
row=['Nikhil', 'CEO', '2', '9.0']
f=open("myfile.csv", 'w', newline = '')
w_obj = csv.writer(f)
w_obj.writerow(row)
f.close()

Why Problem with the writer( )

Universal Newline
'\r'	Carriage Return	Mac OS
'\n'	Line Feed	Unix
'\r\n'	Carriage Return + New Line	MS-DOS, Windows

• since the CSV module does its own (universal) newline handling. writer() has default line terminator parameter is '\r\n'. When we do not Open the file with newline=''(Empty) python replaces '\r' to '\n'.
• So the newline end will become '\n\n'. That’s why there is an extra newline that will come in CSV.

Ref:-

• https://docs.python.org/3/library/functions.html#open
• https://docs.python.org/dev/glossary.html#term-universal-newlines
• https://docs.python.org/dev/library/csv.html#id3