Reading Excel File using Python, how do I get the values of a specific column with indicated column name?

Question

I've an Excel File:

Arm_id      DSPName        DSPCode          HubCode          PinCode    PPTL 1            JaVAS            01              AGR             282001    1,2 2            JaVAS            01              AGR             282002    3,4 3            JaVAS            01              AGR             282003    5,6 

I want to save a string in the form Arm_id,DSPCode,Pincode. This format is configurable, i.e. it might change to DSPCode,Arm_id,Pincode. I save it in a list like:

FORMAT = ['Arm_id', 'DSPName', 'Pincode'] 

How do I read the content of a specific column with provided name, given that the FORMAT is configurable?

This is what I tried. Currently I'm able to read all the content in the file

from xlrd import open_workbook wb = open_workbook('sample.xls') for s in wb.sheets():     #print 'Sheet:',s.name     values = []     for row in range(s.nrows):         col_value = []         for col in range(s.ncols):             value  = (s.cell(row,col).value)             try : value = str(int(value))             except : pass             col_value.append(value)         values.append(col_value) print values 

My output is :

[     [u'Arm_id', u'DSPName', u'DSPCode', u'HubCode', u'PinCode', u'PPTL'],     ['1', u'JaVAS', '1', u'AGR', '282001', u'1,2'],      ['2', u'JaVAS', '1', u'AGR', '282002', u'3,4'],      ['3', u'JaVAS', '1', u'AGR', '282003', u'5,6'] ] 

Then I loop around values[0] trying to find out the FORMAT content in values[0] and then getting the index of Arm_id, DSPname and Pincode in the values[0] and then from next loop I know the index of all the FORMAT factors , thereby getting to know which value do I need to get .

But this is such a poor solution.

How do I get the values of a specific column with name in excel file?

Answer 1

A somewhat late answer, but with pandas, it is possible to get directly a column of an excel file:

import pandas  df = pandas.read_excel('sample.xls') #print the column names print df.columns #get the values for a given column values = df['Arm_id'].values #get a data frame with selected columns FORMAT = ['Arm_id', 'DSPName', 'Pincode'] df_selected = df[FORMAT] 

Make sure you have installed xlrd and pandas:

pip install pandas xlrd