How to use Python to read one column from Excel file?

Question

I want to read the data in one column in excel, here is my code:

import xlrd

file_location = "location/file_name.xlsx"

workbook = xlrd.open_workbook(file_location)

sheet = workbook.sheet_by_name('sheet')

x = []

for cell in sheet.col[9]:

    if isinstance(cell, float):

        x.append(cell)

print(x)

It is wrong because there is no method in sheet called col[col.num], but I just want to extract the data from column 8 (column H), what can I do?

Answer 1

You can get the values of the 8th column like this:

for rownum in range(sheet.nrows):
    x.append(sheet.cell(rownum, 7))

Answer 2

If you're not locked with xlrd I would probably have used pandas instead which is pretty good when working with data from anywhere:

import pandas as pd

df = pd.ExcelFile('location/test.xlsx').parse('Sheet1') #you could add index_col=0 if there's an index
x=[]
x.append(df['name_of_col'])

You could then just write the new extracted columns to a new excel file with pandas df.to_excel()

Answer 3

By far the easiest way to get all the values in a column using xlrd is the col_values() worksheet method:

x = []
for value in sheet.col_values(8):
    if isinstance(value, float):
        x.append(value)

(Note that if you want column H, you should use 7, because the indices start at 0.)

Incidentally, you can use col() to get the cell objects in a column:

x = []
for cell in sheet.col(8):
    if isinstance(cell.value, float):
        x.append(cell.value)

The best place to find this stuff is the official tutorial (which serves as a decent reference for xlrd, xlwt, and xlutils). You could of course also check out the documentation and the source code.