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.
————————————————————————————————-
Perpetual Enigma 
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?
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.
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?
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.
Hello Prateek,
I can generate a csv file using a Python script. However – suppose the file is open in Excel, for an end user – the python script cannot write/append to this csv file. Do you know of a solution for this ?
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 method.as it is not executing
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
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,
Ann