Reading Excel Sheets with PyExcelerator Works Unless I have a blank cell

[rasizzle]

Hey all,

Here is some code for reading Excel spreadsheets (2003 and before) with PyExcelerator. My main problem is that if I leave a cell blank, it gives me an error. I want to be able to have blank cells on my spreadsheet without any problems. Does anyone have any ideas?

This is a spreadsheet with data on only the first 4 columns.

import pyExcelerator

book = pyExcelerator.parse_xls("ReadFrom1.xls")
parsed_dictionary = book[0][1]
readlist = []

number_of_rows = len(parsed_dictionary)/4 #Divided by the number of columns to get number of rows

for i in range(0,number_of_rows):
    readlist.append([parsed_dictionary[i,0],parsed_dictionary[i,1], parsed_dictionary[i,2],parsed_dictionary[i,3]]) #I have to add parsed_dictionary[i,(last number + 1)] for each additional column

for i in readlist:
    print i

If my excel spreadsheet looks like

[1,2,3,4]
[a,b,c,d]

My output is:

[1.0, 2.0, 3.0, 4.0]

But when I take out the letter the number "2" in my excel sheet, my output becomes:

Traceback (most recent call last):
  File "C:/Python26/April_2010/Read With PyExcelerator.py", line 10, in <module>
    readlist.append([parsed_dictionary[i,0],parsed_dictionary[i,1], parsed_dictionary[i,2],parsed_dictionary[i,3]]) #I have to add parsed_dictionary[i,(last number + 1)] for each additional column
KeyError: (0, 1)

Does anybody know how I can solve or work around this?

Also, as you can probably tell from my code, I have to manually add additional columns into the program and also account for that in the parsed_dictionary for loop every time I update my spreadsheet with additional columns.

Does anyone have any suggestions to handle this dynamically?

Thanks and I hope that some of you find this code useful.

[Gribouillis]

If you only want to read blank cells, you could use a defaultdict

from collections import defaultdict
parsed_dictionary = defaultdict(lambda: None, book[0][1])

This would insert a value None if you try to read a non existent key (0,1) for example, instead of raising KeyError.

[rasizzle]

Perfect! Thanks!

And I guess one way around this problem is to preset the program to read much more columns than you need. That way when you add a column, it will already be accounted for.

[rasizzle]

Here is an example of how to make it more dynamic:

book = pyExcelerator.parse_xls("Scoring Rules.xls")
parsed_dictionary = defaultdict(lambda: None, book[0][1])

number_of_columns = 28 #Manually input this number. I put in way more than I needed so I can add columns without worrying about the program until I reach 28. Excel has a 256 limit. 

number_of_rows = len(parsed_dictionary)/number_of_columns #Divided by the number of columns to get number of rows

for i in range(0,number_of_rows):
    scoring_list.append([])
    for h in range(0, number_of_columns):
        scoring_list[i].append(parsed_dictionary[i,h])

I think there's a little bug somewhere in there but that should generally suffice.

[Gribouillis]

Here is an example of how to make it more dynamic:

book = pyExcelerator.parse_xls("Scoring Rules.xls")
parsed_dictionary = defaultdict(lambda: None, book[0][1])

number_of_columns = 28 #Manually input this number. I put in way more than I needed so I can add columns without worrying about the program until I reach 28. Excel has a 256 limit. 

number_of_rows = len(parsed_dictionary)/number_of_columns #Divided by the number of columns to get number of rows

for i in range(0,number_of_rows):
    scoring_list.append([])
    for h in range(0, number_of_columns):
        scoring_list[i].append(parsed_dictionary[i,h])

I think there's a little bug somewhere in there but that should generally suffice.

The bug is in number_of_rows = len(parsed_dictionary)/number_of_columns because the initial len() of your dictionary is the number of cells it read in the spreadsheet.