CODEWITHSUNDEEP
pythonexcel
Sirui Li
Sirui Li

Reputation: 223

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

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?

Upvotes: 16

Views: 114357

Answers (5)

Rahul
Rahul

Reputation: 1336

XLRD is good, but for this case you might find Pandas good because it has routines to select columns by using an operator '[ ]'

Complete Working code for your context would be

import pandas as pd
file_location = "file_name.xlsx"
sheet = pd.read_excel(file_location)
print(sheet['Sl'])

Output 1 - For column 'Sl'

0    1
1    2
2    3
Name: Sl, dtype: int64

Output 2 - For column 'Name'

print(sheet['Name'])
0      John
1      Mark
2    Albert
Name: Name, dtype: object

Reference: file_name.xlsx data

Sl  Name
1   John
2   Mark
3   Albert

Upvotes: 0

Irfanullah
Irfanullah

Reputation: 105

I would recommend to do it as:

import openpyxl
fname = 'file.xlsx'
wb = openpyxl.load_workbook(fname)
sheet = wb.get_sheet_by_name('sheet-name')
for rowOfCellObjects in sheet['C5':'C7']:
  for cellObj in rowOfCellObjects:
    print(cellObj.coordinate, cellObj.value)

Result:
C5 70.82
C6 84.82
C7 96.82

Note: fname refers to excel file, get_sheet_by_name('sheet-name') refers to desired sheet and in sheet['C5':'C7'] ranges are mentioned for columns.

Check out the link for more detail. Code segment taken from here too.

Upvotes: 0

John Y
John Y

Reputation: 14559

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.

Upvotes: 1

Deusdeorum
Deusdeorum

Reputation: 1426

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()

Upvotes: 14

Roee Aharoni
Roee Aharoni

Reputation: 141

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

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

Upvotes: 5

Related Questions