How can I extract excel data by column name?

Question

My excel data looks like this:

    A    B    C
1   123  534  576
2   456  745  345
3   234  765  285

In another excel spreadsheet, my data may look like this:

    B    C    A
1   123  534  576
2   456  745  345
3   234  765  285

How can I extract column C's contents from both spreadsheets?

My code is as follows:

#Open the workbook
ow = xlrd.open_workbook('export.xlsx').sheet_by_index(0)

#Store column 3's data inside an array
ips = ow.col_values(2, 1)

I would like something more like: ips = ow.col_values(C, 1)

How can I achieve the above?

Possible duplicate of [Extract columns from Excel using Python](http://stackoverflow.com/questions/14931906/extract-columns-from-excel-using-python) — Keerthana Prabhakaran, Apr 05 '17 at 03:33
Also, take a look at @Harilal Remesan answer [here](http://stackoverflow.com/questions/34754077/openpyxl-read-only-one-column-from-excel-file-in-python) That might help! — Keerthana Prabhakaran, Apr 05 '17 at 03:35
Did you try assigning 2 to ```C```? ```xlrd``` doesn't use *alpha* column references. — wwii, Apr 05 '17 at 03:49
@wwii The column names are in different columns for each excel spreadsheet I want to extract from. — juiceb0xk, Apr 05 '17 at 05:00
You can use dictionary [Python documentation - dictionary](https://docs.python.org/2/tutorial/datastructures.html#dictionaries) to map numbers with letters. To use it in your code `ips = ow.col_values(dictionary['A'], 1)` — Jacek Zygiel, Apr 05 '17 at 07:58

score 0 · Accepted Answer · answered Apr 05 '17 at 05:31

Since I have two different spreadsheets, with the data that I'm wanting are in two separate rows, I have to search the first row by name until I find it, then extract that column.

Here's how I did it:

ow = xlrd.open_workbook('export.xlsx').sheet_by_index(0)

for x in range (0, 20):
    try:
        if ow.cell_value(0, x) == "IP Address":
            print "found it!"
            ips = ow.col_values(x, 1)
            break
    except IndexError:
        continue

How can I extract excel data by column name?

1 Answers1