How to read Excel File in Python

0 votes

I've an Excel File

Arm_id      DSPName        DSPCode          HubCode          PinCode    PPTL
1            RUTHI            01              BLR             123445    1,2
2            RUTHI            01              BLR             123446    3,4
3            RUTHI            01              BLR             123447    5,6

I want to save a string in the form Arm_id,DSPCode,Pincode. This format is configurable, ie it might change to DSPCode,Arm_id,Pincode . I save it format 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?

Oct 22, 2018 in Python by findingbugs
• 4,730 points
56 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

With pandas it is possible to get directly a column of an excel file:

import pandas
import xlrd
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]
answered Oct 22, 2018 by Priyaj
• 56,120 points

Related Questions In Python

0 votes
1 answer

How to create and read from a temporary file in Python?

Hi, there is a very simple solution ...READ MORE

answered Jan 29 in Python by Nymeria
• 3,500 points
13 views
0 votes
1 answer

How to read numbers from file in Python?

Assuming you don't have extraneous whitespace: with open('file') ...READ MORE

answered Apr 16 in Python by SDeb
• 9,460 points
52 views
+3 votes
5 answers

How to read multiple data files in python

Firstly we will import pandas to read ...READ MORE

answered Apr 6, 2018 in Python by DeepCoder786
• 1,700 points
501 views
0 votes
1 answer

In Python, how do I read a file line-by-line into a list?

with open(fname) as f:     content = f.readlines() # you ...READ MORE

answered Oct 9, 2018 in Python by SDeb
• 9,460 points
62 views
0 votes
1 answer

how can i count the items in a list?

suppose you have a list a = [0,1,2,3,4,5,6,7,8,9,10] now ...READ MORE

answered May 2 in Python by Mohammad
• 1,400 points
19 views
+2 votes
1 answer

“OSError: Unable to open file (bad superblock version number)" in python h5py file read

File could indeed be opened on Linux ...READ MORE

answered Sep 26, 2018 in Python by Priyaj
• 56,120 points
541 views
+2 votes
3 answers

How can I play an audio file in the background using Python?

down voteacceptedFor windows: you could use  winsound.SND_ASYNC to play them ...READ MORE

answered Apr 3, 2018 in Python by charlie_brown
• 7,710 points
940 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.