Reading an excel file using Python

What is Excel Document?

Excel document is worksheet that contains its own collection of cells and help you to store and organise your data. In excel document we save the file with .xlsx extension. The first row of the spreadsheet is usually used for the header and the first column is used for make the numbering of the data.

The Excel sheet file format contains N number of Sheets with each sheet will contains a tabular form of data (rows and cols).

There are plenty of libraries available for Python to load and access the Excel file. One of the popular libraries is “xlrd”.

Looking for someone to help with your Python Programming Assignments? You can use Python Homework help service at AssignmnetXp

Code

				
					# Import the xlrd module      
import xlrd     
      
# Define the location of the file     
loc = ("path of file")     
      
# To open the Workbook     
workbook = xlrd.open_workbook(loc)     
worksheet = workbook.sheet_by_index(0)     
      
# For row 0 and column 0     
data=worksheet.cell_value(0, 0)

How to capture file location

First, you’ll need to capture the full path where the Excel file is stored on your computer.

For example, let’s suppose that an Excel file is stored under the following path:

C:\Users\Jose\Desktop\Medical List.xlsx

Don’t forget to include the file name (in our example, it’s ‘Medical list‘ as highlighted in blue). You’ll also need to include the Excel file extension (in our case, it’s ‘.xlsx‘ as highlighted in maroon).

				
					import pandas as pd

df = pd.read_excel (r'C:\Users\Jose\Desktop\Medical list.xlsx') 

#place "r" before the path string to address special character, such as '\'. Don't forget to put the file name at the end of the path + '.xlsx'

print (df)

Setup

The “xlrd” library should be installed before we use the API. Run the following command under command prompt/terminal.

				
					$pip install xlrd

Instructions

Step 1: Importing Library

We should be importing the library into our sample code using the following statement

				
					import xldr

Step 2: Workbook

The file can be imported into the code as a workbook using the method open_workbook. The file name should be passed as the parameter to the method as shown below.

				
					workbook = xlrd.open_workbook("Medical list xlsx")

[Assuming that the sample.xlsx file already created with the data shown as per Figure ]

Step 3: Worksheet

The hierarchy of the workbook will be of below

Workbook
- Worksheet 1
  - N number of Rows
    - N number of Cols
  - Worksheet 2
  - N number of Rows
    - N number of Cols
    - Worksheet 3
    - Worksheet 4
    - ….

The worksheet should be extracted from the workbook using the method sheet_by_index, where the sheet number should be passed as the parameter. There should be atleast one sheet within the workbook.

				
					worksheet=workbook.sheet_by_index(0)

Step 4: Access Data

The data from a specific row and col from the sheet can be accessed by the method cell_value, where the row and col should be passed as the parameter.

				
					data=worksheet.cell_value(row,column)

Note: You can read data from excel file using many programming languages like Java, C#, R Programming, Matlab. If you need any kind of programming help then contact us and our experts are available 24*7

Error Scenario

In case the specified row and col don’t contain any valid data or the row is not created at all, then the following exception will occur.

				
					IndexError: list index out of range

Step 5 : Iterating through the Table

The table can be iterated through a for loop. The number of valid rows and cols can be extracted using the properties worksheet.nrows and worksheet.ncols. An example code snippet is shown below.

				
					for row in range(0, worksheet.nrows):
    for col in range(0, worksheet.ncols):
        value = worksheet.cell_value(row, col