CSV FILE HANDLING

CSV File:

CSV stands for “comma separated values”.

A comma-separated file is a delimited text file that uses a comma by default to separate values.

Purpose:
CSV is a simple text file in a human readable format which is extensively used to:
 Store tabular data as text in a spreadsheet or database
 Sharing data through import and export of file

File Elements:
 Records - Each line in a file is known as data/record. Each line is separated by a newline (‘\n’).
 Fields - Each record consists of one or more fields. Each field is separated by a delimiter (by
default comma).
 Every records have same number of fields.
 The first line of the file generally stores Headers or Column names, rest of the lines contains
data values for each record.

Application:
CSV organizes large unstructured data into a properly structured and systematic form.
Since CSV files formats are of plain text format, it makes it very easy for website developers to
create applications that implement CSV.

Advantages:
1. Small in size
2. Fast to handle
3. Human readable and easy to edit manually
4. Easy to generate and import onto a spreadsheet or database
5. Simple to implement and parse
6. Processed by almost all existing applications

Disadvantages:
1. Not fully standardized
2. Problem arises if the values contain quotation (Single or Double quotes), comma or newline
characters within data itself
3. Not applicable for those systems which supports each record with different number of fields.

Example:

CSV file format is supported by:

Almost all spreadsheet and database applications like Apple Numbers, LibreOffice Calc,
Apache OpenOffice Calc., Microsoft Excel etc
Libraries available for many programming languages like python
Many utility programs on Unix-style systems
Magento, an e-commerce platform
Emacs editor
 CSV File Handling in Python

1. Import CSV Module – Python built-in CSV module should be imported at first to deal file
CSV file using the following statement:
import csv

2. Opening a CSV file – Using built-in open() like any other text file the file is opened in the
mode specified (by default, reading mode). Filename must have extension as .csv and open()
returns a file object to access file. CSV file can be opened as follows:
3.
file_object = open(“<path//>Filename.csv”, file_mode)
Or,
with open(“<path//>Filename.csv”, file_mode) as file_object:
# Access file (Explicit calling close() not required)

4. Reading a CSV file – For reading a CSV file following functions are required:

Step 1: csv.reader() -- This function creates a special type of object i.e. reader object to access
the CSV file. The reader object is iterable and gives us access to each line of the CSV
file as a list of fields.

csv_reader_object = csv.reader( file_object )

Step 2: Reading each line or record from the file

 Method 1 : next() -- This built-in function can be directly called on reader object to read
the next line of the CSV file. next() returns the current row and advances the iterator to the
next row.
record = next(csv_reader_object)

 Method 2 : Use for loop to read each lines of the file as a list of the fields

for record in csv_reader_object:

# Access each record

5. Writing a CSV file – For writing into a CSV File following functions are required:

Step 1: csv.writer() -- This function returns a writer object that converts the user's data (can
include number or text or both) into a delimited string which can be used to write into CSV file.

csv_writer_object = csv.writer( file_object , delimiter = ‘,’)

where delimiter parameter is optional and have default value ‘,’ that can be changed.

Step 2: Writing records into the file

 Method 1 : writerow() – This function allows to write a list of fields i.e. only one record at
a time to the file.
csv_writer_object.writerow(['Field1', 'Field2', … ,'Fieldn'])
  Method 2 : writerows() – This function allows to write all the lines i.e. list of all records
(2D list) to the file at one go.

csv_writer_object.writerows([Record1], [Record2], ... , [Recordm])

where Recordi = ['Fieldi1', 'Fieldi2', … ,'Fieldin'] if a csv file contains m records and each
record contains n fields.

6. Closing a CSV file – If file is opened using open() directly then it should be closed after
operation on file is completed.
file_object.close()

Some Programs on CSV files

1. Writing data into CSV File through Python: Output:

import csv Enter Employee ID : 1001

Enter Name : Sonam Dutta
# INITIALIZATION Enter Department : Computer
field = ['EMP_ID', 'NAME', 'DEPT', 'SALARY'] Enter Salary : 45000
record = [] Do you want to insert another record
choice = 'Y' (Y/N): y
Enter Employee ID : 1002
# INPUT DETAILS FROM USER Enter Name : Sourav Basu
while choice[0].upper() == 'Y': Enter Department : Physics
id = input("Enter Employee ID : ") Enter Salary : 62000
name = input("Enter Name : ") Do you want to insert another record
dept = input("Enter Department : ") (Y/N): n
sal = input("Enter Salary : ") Records entered successfully!
record.append([id, name, dept, sal])
choice = input("Do you want to insert another record (Y/N): ")

# WRITE RECORDS INTO CSV FILE

file = open("Employee.csv",'w')
csv_wr = csv.writer(file)
csv_wr.writerow(field)
csv_wr.writerows(record)
print("Records entered successfully!")
file.close()
Output CSV File opened using Excel / Spreadsheet Application

Output CSV File opened using Notepad / Text Editor Application

2. Reading data from CSV File through Python:

Method I : Simple display as a list of records i.e. lists

import csv
Output:
# OPEN AND READ FILE
['EMP_ID', 'NAME', 'DEPT', 'SALARY']
file = open("Employee.csv",'r')
['1001', 'Sonam Dutta', 'Computer', '45000']
csv_rd = csv.reader(file)
['1002', 'Sourav Basu', 'Physics', '62000']
c=0
Records retrieved successfully!
for rec in csv_rd:
Total no. of lines in the file = 3
print(rec)
c += 1
print("Records retrieved successfully!")
print("Total no. of lines in the file = ", c)
file.close()
Method II : Display records without any delimiter or special characters

import csv

# OPEN AND READ FILE Output:

file = open("Employee.csv",'r')
EMP_ID NAME DEPT SALARY
csv_rd = csv.reader(file) 1001 Sonam Dutta Computer 45000
field = next(csv_rd) 1002 Sourav Basu Physics 62000
print('\t'.join(field)) 2 Records retrieved successfully!
c=0
for rec in csv_rd:
print('\t'.join(rec))
c += 1

print(c,"Records retrieved successfully!")

file.close()

3. Appending records in CSV File through Python:

The program for appending records in an existing file is similar to writing records in a new file i.e.
Program#1.

The only difference is here the file should be opened in appending mode as following:
file = open("Employee.csv",'a')

During execution of Python the following records have been entered:

Output:

Enter Employee ID : 1003 File Content:

Enter Name : Sheena R
Enter Department : Physics EMP_ID,NAME,DEPT,SALARY
Enter Salary : 78000 1001,Sonam Dutta,Computer,45000
Do you want to insert another record 1002,Sourav Basu,Physics,62000
(Y/N): y 1003,Sheena R,Physics,78000
Enter Employee ID : 1004 1004,Rohit Sinha,Chemistry,58000
Enter Name : Rohit Sinha
Enter Department : Chemistry
Enter Salary : 58000
Do you want to insert another record
(Y/N): n
Records added successfully!
4. Searching data from CSV File through Python:

# Display the details and total count of employees who belong to a given department.

import csv

#INPUT FROM USER TO SEARCH FOR

dept = input("Enter Department to search : ")

# OPEN AND READ FILE

file = open("Employee.csv",'r') Output:
csv_rd = csv.reader(file)
field = next(csv_rd) Enter Department to search : Physics
c=0 EMP_IDNAMEDEPTSALARY
print('\t'.join(field)) 1002Sourav BasuPhysics62000
1003Sheena RPhysics78000
# SEARCH AND DISPLAY No. of employees in department Physics is = 2
for rec in csv_rd:
if dept == rec[2]:
print('\t'.join(rec))
c += 1
print("No. of employees in department", dept, "is = ", c)
file.close()

5. Searching and updating an existing record from CSV File through Python:

# Input Employee ID from user to search for. If found then update salary.

import csv

# INPUT DATA FROM USER TO SEARCH

id = input("Enter Employee ID to search : ")

# OPEN FILE FOR READING

file = open("Employee.csv",'r')
csv_rd = csv.reader(file)
field = next(csv_rd) Output:
record = [field]
Enter Employee ID to search : 1003
# SEARCH AND UPDATE Present Salary = 78000
flag = False Enter new Salary : 80000
for rec in csv_rd: Record updated successfully!
if id == rec[0]:
print("Present Salary = ", rec[3])
rec[3] = input("Enter new Salary : ")
flag = True
record.append(rec)
file.close()
# WRITE IN FILE IF MODIFIED File Content:
if flag:
file = open("Employee.csv",'w') EMP_ID,NAME,DEPT,SALARY
csv_wr = csv.writer(file) 1001,Sonam Dutta,Computer,45000
csv_wr.writerows(record) 1002,Sourav Basu,Physics,62000
print("Record updated successfully!") 1003,Sheena R,Physics,80000
file.close() 1004,Rohit Sinha,Chemistry,58000
else:
print("Record not found!")

6. Searching and deleting an existing record from CSV File through Python:

import csv

# INPUT DATA FROM USER TO DELETE Output:

id = input("Enter Employee ID to delete : ")
Enter Employee ID to delete : 1003
# OPEN FILE FOR READING Record deleted successfully!
file = open("Employee.csv",'r')
csv_rd = csv.reader(file)
field = next(csv_rd)
record = [field]
# SEARCH AND SKIP
flag = False
for rec in csv_rd:
if id == rec[0]:
flag = True File Content:
continue
record.append(rec) EMP_ID,NAME,DEPT,SALARY
file.close() 1001,Sonam Dutta,Computer,45000
1002,Sourav Basu,Physics,62000
# WRITE REST OF THE RECORDS IN FILE 1004,Rohit Sinha,Chemistry,58000
if flag:
file = open("Employee.csv",'w')
csv_wr = csv.writer(file)
csv_wr.writerows(record)
print("Record deleted successfully!")
file.close()
else:
print("Record not found!")

__________________