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;

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.

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:

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:

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

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

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:

    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

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

to

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!