Python Forum
Iterate over excel-sheets using xlrd
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Iterate over excel-sheets using xlrd
#1
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 :)
Reply
#2
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])
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
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"
Reply
#4
slicing
xlrd.xldate_as_tuple(date_cell, xl_fil_resultat.datemode)[:3]
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
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.
Reply
#6
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]])
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
Ah yes i see now. Big thank you!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Selecting correct xlrd version Kithsiri 4 446 Feb-27-2024, 07:09 AM
Last Post: Kithsiri
Lightbulb Help using Google Sheets matheuspimenta 0 688 Dec-15-2022, 05:36 PM
Last Post: matheuspimenta
  How to loop through all excel files and sheets in folder jadelola 1 4,333 Dec-01-2022, 06:12 PM
Last Post: deanhystad
  Compare two Excel sheets with Python and list diffenrences dmkfon 1 14,491 Oct-09-2021, 03:30 PM
Last Post: Larz60+
  identical cells in 2 different excel sheets python pandas esso 0 1,598 Jul-19-2020, 07:50 PM
Last Post: esso
  Does "import xlrd" work in Thonny? cnutakor 3 2,947 Apr-30-2020, 12:41 AM
Last Post: Larz60+
  Issues installing xlrd KipCarter 2 2,781 Jan-24-2020, 12:20 PM
Last Post: KipCarter
  Comparing data from two Excel sheets Stuart_Houghton 0 1,650 Jan-19-2020, 02:49 PM
Last Post: Stuart_Houghton
  Concat multiple Excel sheets with exclusion alessandrotk 1 2,812 Jan-10-2020, 04:43 AM
Last Post: sandeep_ganga
  XLRD Package not be able to call dhiliptcs 2 2,267 Sep-24-2019, 05:21 PM
Last Post: dhiliptcs

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020