Python and CSV – Working With Excel Spreadsheets In Python

We are all familiar with Excel spreadsheets. They are used everywhere to make tables and keep track of stuff in general. Excel offers nice functionalities as well. When we are dealing with a huge number of entries, manipulating these values manually becomes tedious. Consider a situation where there are 1000 rows and 25 columns in a spreadsheet. You want to check the values in the 8th column of each row. Depending on those values, you want to manipulate the values in the 17th column of each row. Or perhaps we want to get all the rows with the same name together. If it were a few rows and columns, we could have done it manually. But doing this for 1000 rows is quite boring. So how do we do it?  

CSV files

A CSV (Comma Separated Value) file stores tabular data in text form separated by commas. It’s a plain text file containing numbers, strings, special characters etc and they are all separated by commas. In this situation, the comma is called a delimiter. You can use other delimiters as well (like spaces, tabs etc), but commas are more frequently used. The beauty of this is that it’s very easy to manipulate. Another good thing is that Excel can easily read CSV files. They are stored using .csv extension. It looks just like a regular spreadsheet when you open it in Excel.

Using Python

Consider the earlier case with 1000 rows. If you have a .xlsx file, you can use the “Save As” option and save it as a .csv file. Once you have that, you can manipulate it using Python. Python has a module named “csv”. You can just import it and it provides necessary functions to read and write csv files.

The code below reads data from the spreadsheet and prints it.

import csv
file_reader = csv.reader(open('input.csv', 'rb'), delimiter=',')
for row in file_reader:
    print row, ", "

The code below writes data onto a spreadsheet.

import csv
file_writer = csv.writer(open('output.csv', 'wb'), delimiter=',')
file_writer.writerow(["Name", "Profession", "Age"])
file_writer.writerow(["Adam", "Engineer", "28"])

You can put the writerow function call in a loop if you have large amount of data to be written. Note that the input argument to ‘writerow’ has to be a Python list.

Once you read the data from a spreadsheet, you can manipulate it very easily. Python provides a big list of features to manipulate data. You can access the smallest details of the input data, manipulate it and put it into an output file.


9 thoughts on “Python and CSV – Working With Excel Spreadsheets In Python

  1. We can manipulate values or anything else in Excel spreadsheets using VBA. So why shoud we use Python instead VBA? I have MS Office with Excel, and I can write some simple macros in VBA, so I see no need to learn Python for making scripts/macros. What is the advantage of using Python instead of VBA?

    1. There are a lot of ways to do this. I just demonstrated one of the ways in this post! Python is one of the most popular programming languages. A lot of complex software systems are written using Python. So if you find yourself in a position where you have to manipulate a spreadsheet from within Python, you can use this method.

  2. Thank You for the answer. If I understood well, You wrote about the case when I have to use Python together with Excel files. But I thought about situation when there is no such need, when I work only with Excel spreadsheets and VBA, they work fine, and their results are sufficient for me. So in that case where is no need to use additional tools like Python, right?

    1. That’s right! This post is more from a programmer’s point of view. If you just want to do some simple modifications to your spreadsheets, I guess VBA should suffice. Python has extensive features which will allow you to manipulate data in very complex ways. So if you are building a system where you need to analyze the data, then Python would be the way to go.

  3. import csv
    file_writer = csv.writer(open(‘output.csv’, ‘wb’), delimiter=’,’)
    file_writer.writerow([“Name”, “Profession”, “Age”])
    file_writer.writerow([“Adam”, “Engineer”, “28”])

    pls could you tell me how to close csv.writer it is not executing

  4. import csv
    file_writer = csv.writer(open(‘output.csv’, ‘wb’), delimiter=’,’)
    file_writer.writerow([“Name”, “Profession”, “Age”])
    file_writer.writerow([“Adam”, “Engineer”, “28”])

    pls could you tell me how to close this fileas it is nit executing

  5. Hi Prateek,

    Can you please help.
    Requirement is like, I have to load hive table data into an excel which is macro enabled.

    step 1: I have taken hive data in csv file.
    step 2: I have created a macro template in .xlsm file
    step 3: Need to feed the csv file data (step 1) to some new excel file and need to use the macro template as well

    Please help me to achieve step3. Also please note that I have restrictions to use most of the python libraries.{java , Unix and base python can be used to achieve the same}

    Thanks and Regards,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s