Introduction
In this tutorial, we will learn how to read the data from an Excel file and use the data in Python. At the end of this tutorial, you will learn how to open an Microsoft Excel file, read data from a particular column into Python.
Example
Let us assume, that the Excel file looks something like this as shown in the figure. You want read the names/ages of all the students. Let us develop the code to read the data from Column A and Column B in to 2 variables Name and Age.
Steps
Step 1 : The first step is to import the xlrd library. We use xlrd package to read the data from Excel spreadsheets. If xlrd library is not installed, you can download from here.
|
1
|
import xlrd
|
Step 2 : In this step, we will open an Excel file “Data.xlsx” in the current directory.
|
1
2
3
4
|
ExcelFileName= 'Data.xlsx'
workbook = xlrd.open_workbook(ExcelFileName)
worksheet = workbook.sheet_by_name("Sheet1") # We need to read the data
#from the Excel sheet named "Sheet1"
|
Step 3 : In the current example, we know that the data is saved in Column A and Column B. However, if we have to read multiple rows and columns and donot exactly how many rows and columns, we need to first obtain the number of rows and columns
|
1
2
|
num_rows = worksheet.nrows #Number of Rows
num_cols = worksheet.ncols #Number of Columns
|
Step 4 : The last step is to parse through each row and each column and read the data in the current cell.
|
1
2
3
4
5
6
7
8
9
10
|
result_data =[]
for curr_row in range(0, num_rows, 1):
row_data = []
for curr_col in range(0, num_cols, 1):
data = worksheet.cell_value(curr_row, curr_col) # Read the data in the current cell
#print(data)
row_data.append(data)
result_data.append(row_data)
|
Download
The complete Python source can be downloaded from here. Please note, that the code is tested using Python 2.7 . If you are using Python 3.3, you might have to do some minor changes in the code.
I hope, you find this article very useful. If you have any doubts or have any comments concerning this tutorial, Please leave a comment or contact me. I would be glad to help.
SapnaEdu is a portal to learn some really cool Mathematical Shortcuts. I hope, it will help students preparing for various competitive examinations. Some of these shortcuts are explained in Vedic Mathematics and rest of them are from my own experience of teaching students. Get to know about SapnaEdu 