Python "IndexError: list index out of range" | Goal: Read 1 excel file & append data to a 2nd excel file

Question

Goal of the script is to take the data from an excel file that is generated daily from a cron job & append that data to the bottom of a 'master' excel file.

Here is where I am right now:

# Find the most recent excel file created by the cron job & open it.

localtime = time.localtime(time.time())
yy = localtime.tm_year
mm = localtime.tm_mon
dd = localtime.tm_mday

file_name = 'daily_' + str(yy) + '_' + str(mm) + '_' + str(dd-1) + '.xls'
file_loc = '/Users/' + file_name
new_sheet = open_workbook(file_loc, on_demand=True).sheet_by_index(0)

# Grab all the data from the recent cron job file starting at row 2 to avoid
# including headers.

for row in range(1, new_sheet.nrows):
    values = []
    for col in range(new_sheet.ncols):
        values.append(new_sheet.cell(row,col).value)
"""
The above loop structures the list 'values[]' in a way that the print
statement of value looks like the below output; which matches the formatting of 
master_file.xlsx:

        2341511  Sports 12112 Dog   324.92
        71611    Other  18192 Cat   128.17
        ...
"""

# Find the excel master file & open it.

sheet = open_workbook('master_file.xlsx').sheet_by_index(0)

# Find the first row that is empty.
new_row = sheet.nrows + 1

# Append the values[] list to the master file starting at row(new_row) and save
# the db file.

for row_num, row in enumerate(values, new_row):
    for col_num, col in enumerate(row):
        sheet.row(row_num).write(col_num, col)

sheet.save('master_file.xlsx')

My traceback error is:

File "daily.py", line 45, in <module>
    sheet.row(row_num).write(col_num, col)
  File "/Users/code/env/lib/python2.7/site-packages/xlrd/sheet.py", line 462, in row
    for colx in xrange(len(self._cell_values[rowx]))
IndexError: list index out of range

Any help would be greatly appreciated!

Community · Accepted Answer · 2017-05-23 11:59:20Z

1

Intuition from the code is that you're not actually adding a row to the worksheet prior to iterating.

xlrd doesn't look like it actually has methods to modify an existing worksheet. Check out this SO post which might help...

How to add new column and row to .xls file using xlrd

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Dec 12, 2012 at 22:21

jsh

2,00419 silver badges28 bronze badges

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

3 Comments

Dude Abides Over a year ago

I was assuming I didn't have to add a row to the worksheet because all of the writing is being handled with xlwt & the write function. Maybe I was wrong about how xlrd and xlwt play together.

Dude Abides Over a year ago

Thanks for this link. I totally needed xlUtils. Yay!

jsh Over a year ago

Good luck! feel free to mark as answered and/or upvote if this helped ;)

sampson-chen · Accepted Answer · 2012-12-12 22:00:04Z

0

Did you mean to have col(new_row) instead of col, as in:

for row_num, row in enumerate(values, col(new_row)):
    for col_num, col(new_row) in enumerate(row):
        sheet.row(row_num).write(col_num, col(new_row))

?

answered Dec 12, 2012 at 22:00

sampson-chen

47.6k13 gold badges87 silver badges81 bronze badges

3 Comments

Dude Abides Over a year ago

Sorry ya I posted the wrong for loop. Edited to the correct one. Both give the Indexerror however.

sampson-chen Over a year ago

@DudeAbides then it might be an off-by-1 error, did you try offsetting the indices by 1?

Dude Abides Over a year ago

I tried offsetting the indices on new_row by 1 in both directions with no luck. I will keep tinkering with it I suppose.

Collectives™ on Stack Overflow

Python "IndexError: list index out of range" | Goal: Read 1 excel file & append data to a 2nd excel file

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related