TypeError: 'generator' object is not subsc

Question

I am getting the typeerror: 'generator' object is not subscriptable when trying to make lists of information in several csv files, sort them so that I get the information I need and put that information into a new .xlsx file. I am wondering if anyone here can help me with where I am going wrong. Is it the csv files that cannot be accessed or is there something wrong with my code? (I did not add the entire code, only the code where I get the error)

Code:

import csv
import operator
from openpyxl import Workbook, load_workbook
import os
import logging

def opencsv(csvfile):
    csvdata = []
    with open(csvfile, encoding='utf-8-sig') as csv_input:
        try:
            reader = csv.reader(csv_input, delimiter=';')
            for row in reader:
                key_1 = row[0]
                key_2 = row[1]
                1_2 = key_1.split(';')
                2_1 = key_2.split(';')


                csvdata.append(list+link)
                sortedlist = sorted(csvdata, key=operator.itemgetter(0), 
                reverse=False)
        return sortedlist
    finally:
        csv_input.close()

def copycsv(excel_file, csvfile):
    rel_path_xlsx = r'C:\Myfolder\xlsx'
rel_path_csv = r'C:\Myfolder\CSV'
wb1 = load_workbook(rel_path_xlsx+"\\"+excel_file)
wb2 = Workbook()
ws1 = wb1.active
ws2 = wb2.active

sortedlist = opencsv(rel_path_csv+"\\"+csvfile)
listed = sorted(sortedlist, key=operator.itemgetter(0), reverse=False)
for info in listed:
    ws2.append(info)

col_v = ws2.columns[0] #line 39, error
col_n = ws2.columns[1] 

for idx, cell in enumerate(col_v, 1):
    ws1.cell(row=idx, column=4).value = cell.value

for idx, cell in enumerate(col_n, 1):
    ws1.cell(row=idx, column=5).value = cell.value

wb1.save(r"C:\Myfolder"+"\\"+"file_"+excel_file)


def copyxlsx(rel_path_xlsx, rel_path_csv):
    for filename in zip(sorted(os.listdir(rel_path_xlsx)), 
    sorted(os.listdir(rel_path_csv))):
    print(filename[0], filename[1])
    copycsv(filename[0], filename[1]) #line 55, error

Traceback (most recent call last):
line 55, in copyxlsx
   copycsv(filename[0], filename[1])
line 39, in copycsv
   col_v =ws2.columns[0]
TypeError: 'generator' object is not subscriptable

I am quite new to python, so any help will be highly appreciated! Working in Python3.4.1

Answer 1

worksheet.columns returns a generator (as the error suggests). You'll need to convert it to a subscriptable object (ie list or tuple) in order to get a column by index:

cols = tuple(ws2.columns)
col_v = cols[0]
col_n = cols[1] 

Or even better, assuming there are only 2 columns:

col_v, col_n = tuple(ws2.columns)

Or if there are more than 2 columns, you don't care about the rest and using Python 3:

col_v, col_n, *_ = tuple(ws2.columns)

Note this will create a useless list in memory. You could also do
col_v, col_n = tuple(ws2.columns)[:2] that works in both Python 2 and 3, and won't create a needless list in memory.

Answer 2

You're calling columns method on ws2, which returns a generator rather than a list. You cannot access generator's values using index, so you have two options:

1) read all values from a generator at once and create a list of them, then operate on the list:

column_list = list(ws2.columns)
col_v = column_list[0]
col_n = column_list[1]

2) read only the first two values using next function:

col_v = next(ws2.columns)
col_n = next(ws2.columns)

Method 2 is preferable if your generator returns a long sequence of items, so you don't want to create a memory-hogging list of all of them. In your case, it probably doesn't matter.