Iterate over excel-sheets using xlrd

[tobbs]

Hi, i can't iterate over excel-sheets the way i want.

First the code without iteration, wiitch works fine;

1 2 3 4 5 6 7 8 9

xl_fil_resultat = xlrd.open_workbook("Data/resultat.xlsx") sheet_resultat = xl_fil_resultat.sheet_by_index(0) resultat.append(sheet_resultat.row_values(0)) # First row is text that cant use datemode        for i in range(1, sheet_resultat.nrows):     resultat.append(sheet_resultat.row_values(i))     date_cell = sheet_resultat.cell(i,1).value     resultat[i][1]= xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode)     print(resultat[i])

here is the result:

['E0', (2019, 2, 9, 0, 0, 0), 'Watford', 'Everton', 1.0, 0.0, 'H', 0.0, 0.0, 'D', 'L Probert']
['E0', (2019, 2, 10, 0, 0, 0), 'Man City', 'Chelsea', 6.0, 0.0, 'H', 4.0, 0.0, 'H', 'M Dean']
['E0', (2019, 2, 10, 0, 0, 0), 'Tottenham', 'Leicester', 3.0, 1.0, 'H', 1.0, 0.0, 'H', 'M Oliver']
['E0', (2019, 2, 11, 0, 0, 0), 'Wolves', 'Newcastle', 1.0, 1.0, 'D', 0.0, 0.0, 'D', 'G Scott']


When i try to iterate over sheets with a for loop the reading is mixed up.

1 2 3 4 5 6 7 8 9

for n in range(5):     sheet_resultat = xl_fil_resultat.sheet_by_index(n)     resultat.append(sheet_resultat.row_values(0)) # First row is text that cant use datemode        for i in range(1, sheet_resultat.nrows):         resultat.append(sheet_resultat.row_values(i))         date_cell = sheet_resultat.cell(i,1).value         resultat[i][1]= xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode)         print(resultat[i])

The result with iteration over sheets:

['E0', (2019, 2, 9, 0, 0, 0), 'Liverpool', 'Newcastle', 4.0, 0.0, 'H', 1.0, 0.0, 'H', 'G Scott']
['E0', (2019, 2, 9, 0, 0, 0), 'Man United', 'Huddersfield', 3.0, 1.0, 'H', 1.0, 0.0, 'H', 'J Moss']
['E0', (2019, 2, 10, 0, 0, 0), 'Tottenham', 'Bournemouth', 5.0, 0.0, 'H', 3.0, 0.0, 'H', 'C Kavanagh']
['E0', (2019, 2, 10, 0, 0, 0), 'Watford', 'Chelsea', 1.0, 2.0, 'A', 1.0, 1.0, 'D', 'M Atkinson']

First of all, it doesn't iterate over different sheets it just gets from index 0. Second, it mixes the items in the list so suddenly Tottenham is playing home against Bournemouth... (I cut out the last 4 matches of a longer list, therefore the teams are different because of the mixes)

If i manually changes to sheet 1,2,3,4,5.. in the code it works just fine. I'm really confused here, hoping for some good advices :)

[buran]

Let's assume you have import the first sheet. It works as expected.
When you get to the second, look what's happens on line 8:

1	resultat[i][1]= xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode)

i=1, but resultat[1] is a row (list) from the first sheet, then you change the second element (resultat[1][1])
in order line 8 to work you need to keep track how many rows you have in the result from previous sheets.

---

Actually you have better solution, change line 8 like below:

1 2 3 4 5 6 7 8 9

for n in range(5):     sheet_resultat = xl_fil_resultat.sheet_by_index(n)     resultat.append(sheet_resultat.row_values(0)) # First row is text that cant use datemode         for i in range(1, sheet_resultat.nrows):         resultat.append(sheet_resultat.row_values(i))         date_cell = sheet_resultat.cell(i,1).value         resultat[-1][1]= xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode) # this will change the last element in resultat         print(resultat[-1])

Note that iterating over sheets works, you were simply printing same rows from the beginning of resultat list due to same confusion with i

---

Yet another option is to process row data before appending them to resultat

1 2 3 4 5 6 7 8 9 10

for n in range(5):     sheet_resultat = xl_fil_resultat.sheet_by_index(n)     resultat.append(sheet_resultat.row_values(0)) # First row is text that cant use datemode          for i in range(1, sheet_resultat.nrows):         row_data = sheet_resultat.row_values(i)         date_cell = sheet_resultat.cell(i,1).value         row_data[1]= xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode) # this will change the second element in row_data         resultat.append(row_data)         print(resultat[-1])

[tobbs]

Oh yeah, off course! Thank you :)

Another thing, any ideas how to get rid of the time in the date format? "2019, 2, 10, 0, 0, 0" and just display "2019, 2, 10"

[buran]

slicing

1	xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode)[:3]

[tobbs]

Please help a noob...

I have a new problem with a similar excel fil as above. This time however, i use append.sheet.row_values(i)[0] instead of just append.sheet.row_values(i) as above. Suddenly i get tuple instead of a list and then i can't change the values.

My new code:

1 2 3

for i in range(1, sheet.nrows):     spelomgång.append((sheet.row_values(i)[0], sheet.row_values(i)[1], sheet.row_values(i)[2]))      spelomgång[-1][1] =  xldate_to_datetime(spelomgång[-1][1])

The line "spelomgång[-1][1] = xldate_to_datetime(spelomgång[-1][1]" causes the error: TypeError: 'tuple' object does not support item assignment


I have a feeling i should use something live append.sheet.cell(i , 1).value instead of append.sheet.row_values(i)[0] but i just can't get it to work.

[buran]

you get tuple, because you append tuple
change

1	spelomgång.append((sheet.row_values(i)[0], sheet.row_values(i)[1], sheet.row_values(i)[2]))

to

1	spelomgång.append([sheet.row_values(i)[0], sheet.row_values(i)[1], sheet.row_values(i)[2]])

[tobbs]

Ah yes i see now. Big thank you!