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?
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