How would I take an excel file and convert its col

Question

I had the following question. Namely, suppose I have an excel file with some names in the first row. Then the next however many rows are single letter entries (so "A", "B", "C" and so on).

My goal is to extract the column and make it into a list, so that, say, for column 1 the list would start in row 2, the next entry would be from row 3, and so on, until the end is reached.

How would I do that in Python?

Answer 1

I used a module called xlrd.

Some information on that http://www.blog.pythonlibrary.org/2014/04/30/reading-excel-spreadsheets-with-python-and-xlrd/

And here is the package: https://pypi.python.org/pypi/xlrd

To exclude the first row, and make different lists for different columns you could do something like...

from xlrd import open_workbook

book = open_workbook("mydata.xlsx")
sheet = book.sheet_by_index(0) #If your data is on sheet 1

column1 = []
column2 = []
#...

for row in range(1, yourlastrow): #start from 1, to leave out row 0
    column1.append(sheet.cell(row, 0)) #extract from first col
    column2.append(sheet.cell(row, 1))
    #...

Put the index-1 of the last row that contains data in the 'yourlastrow' placeholder.

Answer 2

I figured out the answer. Assuming the Excel file is Test.xlsx, we can translate the j-th column (excluding the first row) into list_j as follows:

book = xlrd.open_workbook("Test.xlsx")
sheet = book.sheet_by_index(0)

list_j = []

for k in range(1,sheet.nrows):
    list_j.append(str(sheet.row_values(k)[j-1]))