How to Write and Read CSV Files with Python on Ubuntu 20.04

Introduction

The comma-separated values (CSV) is the most widely and convenient tabular data exchange format. Most CSV files originate from spreadsheet applications and relational database management systems (For example, MySQL). In a CSV file, commas separate field/column values, and linebreaks separate records/rows. The CSV format allows developers to store and transfer tabular data in plain-text files without using special software.

The Python programming language supports different libraries for reading and writing CSV files. Mostly, you require to parse CSV files with Python to automate tasks or analyze data. For instance, you can use Python to loop through large CSV files, filter data records, and insert the results in a relational database like MySQL or PostgreSQL.

This guide describes how to write and read CSV files with Python on Ubuntu 20.04 server.

Prerequisites

To follow along with this guide:

• Deploy an Ubuntu 20.04 server.
• Create a non-root sudo user.

1. Create a Sample CSV File

Before you start reading CSV files with Python, you need a project directory and a sample CSV file. Follow the steps below to carry out the above tasks:

1. Create a new project directory.

    $ mkdir project
   

2. Switch to the new project directory.

    $ cd project
   

3. Open a new products.csv file in a text editor. In a production environment, the CSV file might come from spreadsheet applications or databases like PostgreSQL or MySQL.

    $ nano products.csv
   

4. Enter the following CSV data records into the products.csv file. In the following file, separate field/column values with commas (,) and put each record on a separate line.

    product_id,product_name,retail_price
    1,LED TORCH,20.25
    2,4G WIRELESS ROUTER,89.90
    3,LEATHER BELT,19.85
    4,LEMON SQUEEZER,3.50
    5,COFFEE MAKER,239.65
   

5. Save and close the products.csv file.

2. Read and Write CSV Files With Python

To manipulate CSV files with Python, you require the csv module. The module contains the following functions for reading and writing CSV data:

• reader(): This function loops a given CSV file and returns data rows as a list of strings.
• writer(): This function converts raw input data from Python into a CSV delimited string for storage.

2.1. Read Data from a CSV File with Python

To read data from the sample products.csv file that you created earlier, follow the steps below:

1. Open a new read_csv.py file in a text editor.

    $ nano read_csv.py
   

2. Enter the following information into the read_csv.py file.

    import csv
   
    fileName = 'products.csv'
   
    with open(fileName, 'r') as csvFile:
   
        dataRows = csv.reader(csvFile)
   
        for dataRow in dataRows:
   
            print(dataRow)
   

3. Save and close the read_csv.py file.

4. Check out the read_csv.py source code explanation below.

   • The import csv declaration at the top loads the Python csv module.

   • The fileName = 'products.csv' statement declares the CSV path.

   • The with open(fileName, 'r') as csvFile: line opens the products.csv file. The r parameter declares the read mode.

   • The dataRows = csv.reader(csvFile) statement reads the products.csv file and returns a list of records.

   • The for dataRow in dataRows: statement loops through the data records in the dataRows object.

   • The print(dataRow) statement outputs each row's data.

5. Execute the read_csv.py file using the following command.

    $ python3 read_csv.py

   The read_csv.py script displays the following output.

    ['product_id', 'product_name', 'retail_price']
    ['1', 'LED TORCH', '20.25']
    ['2', '4G WIRELESS ROUTER', '89.90']
    ['3', 'LEATHER BELT', '19.85']
    ['4', 'LEMON SQUEEZER', '3.50']
    ['5', 'COFFEE MAKER', '239.65']

In this guide, you only have a few sample records in the CSV file. In a production environment, you might have thousands of records. To implement a paging mechanism that allows you to retrieve only a few records at a time, follow the steps below:

1. Review the syntax below that allows you to convert the CSV reader object (dataRows = csv.reader(csvFile)) into a Python list.

    ...
    dataRows = list(csv.reader(csvFile))`
    ...
   

2. Understand how to use the square brackets and Python's slicing syntax to access the data records using a list's start and stop indices.

    ...
    dataRows[start_index:stop_index]:
    ...
   

3. To read the first four records from the products.csv file, delete the existing read_csv.py file and create a new file.

    $ rm read_csv.py && nano read_csv.py
   

4. Enter the following information into the read_csv.py file.

    import csv
   
    fileName = 'products.csv'
   
    with open(fileName, 'r') as csvFile:
   
        dataRows = list(csv.reader(csvFile))
   
        for dataRow in dataRows[0:4]:
   
            print(dataRow)
   

5. Save and close the read_csv.py file.

6. Run the read_csv.py file.

    $ python3 read_csv.py

   Output.

    ['product_id', 'product_name', 'retail_price']
    ['1', 'LED TORCH', '20.25']
    ['2', '4G WIRELESS ROUTER', '89.90']
    ['3', 'LEATHER BELT', '19.85']
   

7. Recreate the read_csv.py again.

    $ rm read_csv.py && nano read_csv.py
   

8. Change the read_csv.py file to retrieve the next records in the list as follows.

    import csv
   
    fileName = 'products.csv'
   
    with open(fileName, 'r') as csvFile:
   
        dataRows = list(csv.reader(csvFile))
   
        for dataRow in dataRows[4:6]:
   
            print(dataRow)
   

9. Run the read_csv.py file.

    $ python3 read_csv.py

   Output.

    ['4', 'LEMON SQUEEZER', '3.50']
    ['5', 'COFFEE MAKER', '239.65']

2.2. Write Data into a CSV File with Python

The csv.writer() function discussed earlier allows you to write data into CSV files. Follow the steps below to use the module.

1. Open a new write_csv.py file in a text editor.

    $ nano write_csv.py
   

2. Enter the following information into the write_csv.py file.

    import csv
   
    row6 = ['6', 'DOUBLE-SIDED TAPE', '4.5']
   
    row7 = ['7', '16GB FLASH DRIVE', '6.95']
   
    row8 = ['8', 'WIRELESS KEYBOARD', '25.85']
   
    fileName = 'products.csv'
   
    with open(fileName, 'a') as csvFile:
   
        writer = csv.writer(csvFile)
   
        writer.writerow(row6)
   
        writer.writerow(row7)
   
        writer.writerow(row8)
   
        print("Success")
   

3. Save and close the write_csv.py file.

4. Review the write_csv.py file source code.

   • The import csv declaration at the top loads the Python csv module.

   • The row6 = [...], row7 = [...], and row8 = [...] statements initialize Python lists objects containing comma-separated values for each additional row that you want to add to the products.csv file.

   • The fileName = 'products.csv' statement defines the path.

   • The with open(fileName, 'a') as csvFile: function opens the products.csv file. The `a' parameter instructs the function to open the file in append mode.

   • The writer = csv.writer(csvFile) statement returns an object for writing data into the products.csv file.

   • The writer.writerow(...) functions write row6, row7, and row8 into the products.csv file.

   • The print("Success") line displays a Success message when the code executes.

5. Run the write_csv.py file.

    $ python3 write_csv.py

   Output.

    Success
   

6. Recreate the read_csv.py file.

    $ rm read_csv.py && nano read_csv.py
   

7. Enter the following information into the read_csv.py file to read all the records from the products.csv file.

    import csv
   
    fileName = 'products.csv'
   
    with open(fileName, 'r') as csvFile:
   
        dataRows = csv.reader(csvFile)
   
        for dataRow in dataRows:
   
            print(dataRow)
   

8. Save and close the read_csv.py file.

9. Execute the read_csv.py to check whether the write_csv.py script has successfully updated the products.csv file.

    $ python3 read_csv.py

   Output.

    ['product_id', 'product_name', 'retail_price']
    ['1', 'LED TORCH', '20.25']
    ['2', '4G WIRELESS ROUTER', '89.90']
    ['3', 'LEATHER BELT', '19.85']
    ['4', 'LEMON SQUEEZER', '3.50']
    ['5', 'COFFEE MAKER', '239.65']
    ['6', 'DOUBLE-SIDED TAPE', '4.5']
    ['7', '16GB FLASH DRIVE', '6.95']
    ['8', 'WIRELESS KEYBOARD', '25.85']

Conclusion

This guide shows you how to work with CSV files using the Python csv module. The sample Python code in the guide use the csv.reader() and csv.writer() functions to parse and append data into CSV files. Then, the Python list syntax describes how to page CSV rows when working with large files.