Retrieve the headers in excel using Python

Question

Please click here to view the Excel file

I want to retrieve headers of this excel file (Only A,B,C)and store it in a list using Python. I opened my file but I am unable to retrieve it.

import xlrd
file_location= "C:/Users/Desktop/Book1.xlsx"
workbook= xlrd.open_workbook(file_location)
sheet=workbook.sheet_by_index(0)

Can anyone please help me with that? I am new to Python.
Thank you for your help.

are you open to use pandas library and maybe read the whole excel file easily ? — Rayhane Mama
– Rayhane Mama, Commented Jul 5, 2017 at 14:06
I can use anything but I don't want the rest of the data. Only headers — Analyst
– Analyst, Commented Jul 5, 2017 at 14:08
which row is the headers, I see two rows which could be them. — gold_cy
– gold_cy, Commented Jul 5, 2017 at 14:09

Cary Shindell · Accepted Answer · 2017-07-05 15:44:43Z

1

You could also try using the numpy method loadtxt: https://docs.scipy.org/doc/numpy/reference/generated/numpy.loadtxt.html Which gives you arrays for each column (you can also skip specified columns so that you only get the A,B,C as you desire). You can write a for loop to get the first entry of each column and put them in a list.

data = loadtxt("Book1.xlsx")
headers = [] 
for c in range(1,data.shape[0]): 
    if data[c, 0] != "": 
        headers.append(data[c, 0])

edited Jul 5, 2017 at 15:44

answered Jul 5, 2017 at 14:20

Cary Shindell

1,3861 gold badge8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Analyst Over a year ago

Hi, but I might add a couple of columns in future so I can't hard code the number. Can you syntactically tell me how do I do that?

Cary Shindell Over a year ago

You can do: data = loadtxt("Book1.xlsx") headers = [data[c,0] for c in range(data.shape[0] if data[c,0] != ""]

Cary Shindell Over a year ago

You can also skip the first column by changing the range arguments to range(1,data.shape[0])

Analyst Over a year ago

I am getting following error:>>>>>>>>>>>>>>>>>>>>>> headers = [data[c,0] for c in range(1,data.shape[0]) if data[c,0] != ""] AttributeError: '_io.TextIOWrapper' object has no attribute 'shape'

Cary Shindell Over a year ago

Actually, I'm not sure the generator expression is smooth, so try this: headers = [] for c in range(1,data.shape[0]): if data[c, 0] != "": headers.append(data[c, 0])

|

hd1 · Accepted Answer · 2017-07-05 14:45:03Z

1

iterheaders = iter(sheet.row(1))
headers = [str(cell.value) for cell in iterheaders[1:] if cell is not None and cell.value != '']

Hope that helps...

edited Jul 5, 2017 at 14:45

answered Jul 5, 2017 at 14:19

hd1

34.9k5 gold badges83 silver badges95 bronze badges

5 Comments

Analyst Over a year ago

Hi, Thanks! This helped partially but the output is ['Date', '', 'A', '', '', 'B', '', '', 'C', ''] I want it look as [A, B, C]. Thanks.

Andreas Lymbouras Over a year ago

Then try to do this instead: headers = [str(cell.value) for cell in iterheaders if cell is not None and cell != '']

Analyst Over a year ago

Sorry that didn't work either! Still the same output

hd1 Over a year ago

Try now @Programmer?

hd1 Over a year ago

so, you accepted the answer without understanding it? Great... The update skips the first column and adds the check that @AndreasLympouras suggested in his comment

Collectives™ on Stack Overflow

Retrieve the headers in excel using Python

2 Answers 2

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related