Sunday, March 31, 2024

Working With CSV Recordsdata Utilizing Python, with Examples — SitePoint

Must read

On this article, we’ll discover ways to use Python to learn from and write information to CSV information, and the right way to convert CSV information to JSON format and vice versa. We’ll discover the right way to use the csv module and in addition have a look at examples that assist perceive the way it works.

A CSV (comma-separated values) file is a textual content file format that permits information to be saved in a tabular construction. It is a standard format used for exporting and importing information from databases and spreadsheets.

Because the title suggests, every bit of knowledge in a CSV file is separated by a comma (,). Generally the time period “CSV” can be utilized to explain codecs with different forms of separators, reminiscent of colons (:), semicolons (;) and tabs (t). For the needs of this text, we’ll simply be coping with CSV information that use commas as delimiters (generally known as RFC 4180).

When opened, the content material of a CSV file appears like this:

Worker Id,First Identify,Gender,Begin Date,Final Login Time,Wage,Bonus %,Senior Administration,Crew
1,Douglas,Male,8/6/1993,12:42 PM,,6.945,TRUE,Advertising
2,Thomas,Male,3/31/1996,6:53 AM,61933,4.17,,
3,Maria,Feminine,4/23/1993,11:17 AM,,11.858,FALSE,Finance
4,Jerry,Male,3/4/2005,1:00 PM,138705,9.34,,Finance

As seen above, the comma delimiter, ,, is used to separate every particular piece of knowledge within the file.

The primary row of knowledge might optionally function the header, figuring out every column of knowledge beneath it. CSV information are generally saved with a .csv file extension.

The csv Module

Since spreadsheets and databases like MS SQL could be imported and exported as CSV information, it’s essential to know the right way to deal with information served in CSV format programmatically. Most programming languages like Python assist dealing with information in CSV and in addition remodeling them to different codecs like JSON.

Python offers the csv module for studying, writing and performing different types of file dealing with in CSV codecs. The in-built library offers capabilities and lessons that make working with CSV information seamless.

How one can Learn CSV Recordsdata Utilizing Python

The csv module has the csv.reader() perform for studying CSV information. It’s used along with objects (together with file objects) reminiscent of these produced with Python’s in-built open() perform.

Given a file object from a name to open(), csv.reader() will return a reader object. The reader object can be utilized to iterate over every line of CSV information, the place rows are returned as a listing of strings.

Let’s take an instance:

import csv

with open('staff.csv', newline='') as file_obj:
    reader_obj = csv.reader(file_obj)
    for row in reader_obj:

Right here’s the output of the code above:

['Employee Id', 'First Name', 'Gender', 'Start Date', 'Last Login Time', 'Salary', 'Bonus %', 'Senior Management', 'Team']
['1', 'Douglas', 'Male', '8/6/1993', '12:42 PM', '', '6.945', 'TRUE', 'Marketing']
['2', 'Thomas', 'Male', '3/31/1996', '6:53 AM', '61933', '4.17', '', '']
['3', 'Maria', 'Female', '4/23/1993', '11:17 AM', '', '11.858', 'FALSE', 'Finance']
['4', 'Jerry', 'Male', '3/4/2005', '1:00 PM', '138705', '9.34', '', 'Finance']
['5', 'Larry', 'Male', '1/24/1998', '4:47 PM', '101004', '1.389', 'TRUE', 'Client Services']

From the primary code snippet, the staff.csv file is opened, after which the csv.reader() perform parses it and returns a reader object. A easy for loop is used to iterate over the reader object, which returns a listing of knowledge from the every row from the staff.csv file, ranging from the highest.

How one can Write to CSV Recordsdata Utilizing Python

Apart from studying information from CSV information, we will additionally write information to those information in Python. The perform allows us to jot down information to CSV format. After opening the file in write mode, the perform returns a author object, which converts provided information into delimited strings on the supplied file object. The author object has the writerow() technique for writing a row — an iterable of strings or numbers of comma-separated values per time — whereas the writerows() technique is used for a number of rows directly. The writerow() and writerows() strategies are they solely two choices for writing information to a CSV file.

All of the record objects used within the code snippet above might be grouped right into a 2D record and handed in as an argument to the writerows() technique of the author object to attain the identical end result.

After the with assertion is executed, a CSV file (merchandise.csv) is created within the present working listing containing these comma-separated values.

Right here’s an instance:

import csv

with open('merchandise.csv', 'w', newline='') as file_obj:
    writer_obj =
    writer_obj.writerow(['Product Name', 'Price', 'Quantity', 'SKU Number' ])
    writer_obj.writerow(['Rice', 80, 35, 'RI59023'])
    writer_obj.writerow(['Curry', 2, 200, 'CY13890'])
    writer_obj.writerow(['Milk', 9.5, 315, 'MK10204'])

Right here’s the output of the code above:

Product Identify,Value,Amount,SKU Quantity

How one can Convert CSV to JSON Utilizing Python

Whereas performing file I/O operations, we’d need to convert a CSV file to JSON format — which is standard for receiving and transmitting information between a shopper and a server. The csv module offers the csv.DictReader class to assist us to attain this.

The csv.DictReader class strategies assist to transform a given CSV file to a Python dictionary earlier than making use of the json module’s json.dump() perform to transform the ensuing Python dictionary to a JSON file. The csv.DictReader() class takes an elective fieldnames argument. The place the sector names are omitted, values from the primary row will likely be mapped to the remainder of the information as area names.

Let’s check out an instance:

import csv
import json

my_dict = {}

with open('staff.csv', newline='') as file_obj:
    reader_object = csv.DictReader(file_obj)
    for row in reader_object:
        key = row['Employee Id']
        my_dict[key] = row

with open('worker.json', 'w', encoding='utf-8') as file_obj:
    json.dump(my_dict, file_obj, indent=4)   

Right here’s the output of the code above:

"1": {
    "Worker Id": "1",
    "First Identify": "Douglas",
    "Gender": "Male",
    "Begin Date": "8/6/1993",
    "Final Login Time": "12:42 PM",
    "Wage": "",
    "Bonus %": "6.945",
    "Senior Administration": "TRUE",
    "Crew": "Advertising"
"2": {
    "Worker Id": "2",
    "First Identify": "Thomas",
    "Gender": "Male",
    "Begin Date": "3/31/1996",
    "Final Login Time": "6:53 AM",
    "Wage": "61933",
    "Bonus %": "4.17",
    "Senior Administration": "",
    "Crew": ""

To transform a CSV file to a JSON equal, we utilized the next steps:

  • opened the staff.csv file in learn mode
  • created a Python dictionary from the returned file object utilizing the csv.DictReader class
  • opened a JSON file in write mode, reminiscent of staff.json (if no such file had existed, one would have been created)
  • used the dump() perform of the json module to transform the Python dictionary (my_dict) to a JSON file

How one can Convert JSON to CSV Utilizing Python

On this part, we’ll have a look at the right way to convert information from a JSON file to CSV format. To attain this, we’ll use each the in-built csv and json Python modules. The json module’s json.load() perform will assist convert a JSON file to a Python dictionary, whereas the csv module’s csv.DictWiter class strategies will assist convert the Python dictionary to a CSV file.

Right here’s an instance:

import csv
import json

py_dict = {}

with open('staff.json', 'r', encoding='utf-8') as file_obj:
    py_dict = json.load(file_obj)

with open('employees_records.csv', 'w', newline='') as file_obj:
    csv_writer = csv.DictWriter(file_obj, fieldnames=py_dict['1'].keys())
    for key in py_dict.keys():

To transform a JSON file to a CSV equal, we utilized the next steps:

  • opened the staff.json file in learn mode
  • used the json.load() perform to create a Python dictionary py_dict
  • opened a CSV file employees_records.csv in write mode (if no such file had existed, one would have been created)
  • created a author object with the csv.DictWriter class with crucial arguments
  • used the author object strategies to map dictionaries into the suitable variety of rows


CSV information are highly regarded and infrequently utilized in exporting and importing spreadsheets and databases. This file format is used fairly often by these working with information. Nonetheless, whereas programming with Python there is likely to be must rapidly use CSV information, so it’s essential to discover ways to carry out file I/O operations with CSV.

Python’s csv module may be very helpful for working with CSV information, because it offers the required capabilities and lessons for these type of duties.

It’s essential to additionally notice that we might must convert information from one format to a different (CSV to JSON) as seen in our examples above.

Supply hyperlink

More articles


Please enter your comment!
Please enter your name here

Latest article