Hi, i changed the model from reading csv-file to reading xlsx-file with xlrd and encounterd some weird problems.
here's the code:
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']
Everything looks fine here. The problem is that i have several sheets and when i try to iterate over them using a for loop everything gets messy.
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])
['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. Second, it suddenly 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... it works just fine. I'm really confused here, hoping for some good advices :)