Read Excel File in Python

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

This is one approach:

    from xlrd import open_workbook

    class Arm(object):
        def __init__(self, id, dsp_name, dsp_code, hub_code, pin_code, pptl):
            self.id = id
            self.dsp_name = dsp_name
            self.dsp_code = dsp_code
            self.hub_code = hub_code
            self.pin_code = pin_code
            self.pptl = pptl

        def __str__(self):
            return("Arm object:\n"
                   "  Arm_id = {0}\n"
                   "  DSPName = {1}\n"
                   "  DSPCode = {2}\n"
                   "  HubCode = {3}\n"
                   "  PinCode = {4} \n"
                   "  PPTL = {5}"
                   .format(self.id, self.dsp_name, self.dsp_code,
                           self.hub_code, self.pin_code, self.pptl))

    wb = open_workbook('sample.xls')
    for sheet in wb.sheets():
        number_of_rows = sheet.nrows
        number_of_columns = sheet.ncols

        items = []

        rows = []
        for row in range(1, number_of_rows):
            values = []
            for col in range(number_of_columns):
                value  = (sheet.cell(row,col).value)
                try:
                    value = str(int(value))
                except ValueError:
                    pass
                finally:
                    values.append(value)
            item = Arm(*values)
            items.append(item)

    for item in items:
        print item
        print("Accessing one single value (eg. DSPName): {0}".format(item.dsp_name))
        print

You don't have to use a custom class, you can simply take a dict(). If you use a class however, you can access all values via dot-notation, as you see above.

Here is the output of the script above:

    Arm object:
      Arm_id = 1
      DSPName = JaVAS
      DSPCode = 1
      HubCode = AGR
      PinCode = 282001 
      PPTL = 1
    Accessing one single value (eg. DSPName): JaVAS

    Arm object:
      Arm_id = 2
      DSPName = JaVAS
      DSPCode = 1
      HubCode = AGR
      PinCode = 282002 
      PPTL = 3
    Accessing one single value (eg. DSPName): JaVAS

    Arm object:
      Arm_id = 3
      DSPName = JaVAS
      DSPCode = 1
      HubCode = AGR
      PinCode = 282003 
      PPTL = 5
    Accessing one single value (eg. DSPName): JaVAS

From: stackoverflow.com/q/22169325

Back to homepage or read more recommendations: